Fed up with Dlookup's

[balvinder]

Hi,

I'am quite new in VBA programming & doesn't know anything about Recordset etc. But have searched on google that Recordset & seek could help in making code much faster than dlookup's. I'am really keen to learn how this Recordset & seek works..

I have split database (in common shared drive) & front end is placed in every user desktop/D drive.

I'am quite disturbed with the speed of dlookup's i have in my code. Would request help to make this code faster if there is any alternative to dlookup's.

Private Sub Telecaller_Name_Change()

If (DCount("[Follow_up_date]", "Telecalling_database", "DatePart('d',[Follow_up_date]) = DatePart('d',Date()) And Year([Follow_up_date]) = Year(Date()) And Month([Follow_up_date]) = Month(Date()) And DatePart('d',[Calling_end_date_time]) <> DatePart('d',Date()) AND [Paid_Unpaid_Status] <> 'Paid' AND [CALLER_NAME] =""" & Me.Telecaller_Name & """") > 0) Then
Me.Form_S_No = CurrentProject.AccessConnection.Execute("SELECT Instrument_Number FROM telecalling_database WHERE [Caller_Name] = """ & Me.Telecaller_Name.Value & """ AND [Paid_Unpaid_Status] <> 'Paid' AND DatePart('d',[Follow_up_date]) = DatePart('d',Date()) And Year([Follow_up_date]) = Year(Date()) And DatePart('d',[Calling_end_date_time]) <> DatePart('d',Date()) And Month([Calling_end_date_time]) = Month(Date()) And Year([Calling_end_date_time]) = Year(Date())")(0)
Else
If (DCount("[MFYP_FRYP]", "Telecalling_Database", "[MFYP_FRYP] = 'MFYP' AND [Paid_Unpaid_Status] <> 'Paid' AND BANK_NAME NOT LIKE '&#37;DEBIT'") > 0) Then
Me.Form_S_No = CurrentProject.AccessConnection.Execute("SELECT Instrument_Number FROM Telecalling_database WHERE [Caller_Name] = """ & Me.Telecaller_Name.Value & """ AND [Calling_Code] is null AND [MFYP_FRYP]='MFYP' AND [Paid_Unpaid_Status] <> 'Paid' AND [BANK_NAME] NOT LIKE '%DEBIT' ORDER BY [Bounce_date] ASC, [No_of_Premium_Required] DESC, [Modal_Premium] DESC;")(0)
Else
If (DCount("[MFYP_FRYP]", "Telecalling_Database", "[MFYP_FRYP] = 'FRYP' AND [Paid_Unpaid_Status] <> 'Paid' AND BANK_NAME NOT LIKE '%DEBIT'") > 0) Then
Me.Form_S_No = CurrentProject.AccessConnection.Execute("SELECT Instrument_Number FROM Telecalling_database WHERE [Caller_Name] = """ & Me.Telecaller_Name.Value & """ AND [Calling_Code] is null AND [MFYP_FRYP]='FRYP' AND [Paid_Unpaid_Status] <> 'Paid' AND [BANK_NAME] NOT LIKE '%DEBIT' ORDER BY [Bounce_date] ASC, [No_of_Premium_Required] DESC, [Modal_Premium] DESC;")(0)
Else
If (DCount("[MFYP_FRYP]", "Telecalling_Database", "[MFYP_FRYP] = 'RYP' AND [Paid_Unpaid_Status] <> 'Paid' AND BANK_NAME NOT LIKE '%DEBIT'") > 0) Then
Me.Form_S_No = CurrentProject.AccessConnection.Execute("SELECT Instrument_Number FROM Telecalling_database WHERE [Caller_Name] = """ & Me.Telecaller_Name.Value & """ AND [Calling_Code] is null AND [MFYP_FRYP]='RYP' AND [Paid_Unpaid_Status] <> 'Paid' AND [BANK_NAME] NOT LIKE '%DEBIT' ORDER BY [Bounce_date] ASC, [No_of_Premium_Required] DESC, [Modal_Premium] DESC;")(0)
Else
End If
End If
End If
End If


Policy_Number = DLookup("Policy_No", "Telecalling_database", "Instrument_number=" & Me.Form_S_No)
GO_Code = DLookup("GO_CODE_Ingenium", "Telecalling_database", "Instrument_number=" & Me.Form_S_No)
Modal_Premium = DLookup("Modal_Premium", "Telecalling_database", "Instrument_number=" & Me.Form_S_No)
Frequency = DLookup("Frequency", "Telecalling_database", "Instrument_number=" & Me.Form_S_No)
ACt_Holder_Name = DLookup("Account_Holder_Name", "Telecalling_database", "Instrument_number=" & Me.Form_S_No)
Account_Number = DLookup("Account_Number", "Telecalling_database", "Instrument_number=" & Me.Form_S_No)
Bank_Name_Ingenium = DLookup("Bank_Name_Ingenium", "Telecalling_database", "Instrument_number=" & Me.Form_S_No)
Client_Name = DLookup("Client_Name", "Telecalling_database", "Instrument_number=" & Me.Form_S_No)
Mobile_No = DLookup("Mobile_No", "Telecalling_database", "Instrument_number=" & Me.Form_S_No)
Home_No = DLookup("Home_No", "Telecalling_database", "Instrument_number=" & Me.Form_S_No)
Work_No = DLookup("Work_No", "Telecalling_database", "Instrument_number=" & Me.Form_S_No)
No_of_Prem_required = DLookup("NO_OF_PREMIUM_REQUIRED", "ECS-renewals", "Policy_Number=" & Me.Policy_Number)
Issue_date = DLookup("Issue_Date", "Telecalling_database", "Instrument_number=" & Me.Form_S_No)
Calling_Code = DLookup("Calling_code", "Telecalling_database", "Instrument_number=" & Me.Form_S_No)
Customer_comments = DLookup("Customer_comments", "Telecalling_database", "Instrument_number=" & Me.Form_S_No)
Policy_Type = DLookup("Policy_Type", "Telecalling_Database", "Instrument_Number=" & Me.Form_S_No)


If (Policy_Type.Value = "Trad") Then
Policy_paid_to_date = DLookup("POLICY_PAID_TO_DATE", "ECS-renewals", "Policy_Number=" & Me.Policy_Number)
Else
If (Policy_Type.Value = "ULIP") And (IsNull(DLookup("Contractual_Paid_to_Date", "ECS-renewals", "Policy_Number=" & Me.Policy_Number))) Then
Me.Policy_paid_to_date.Value = "No PTD concept"
Else
Policy_paid_to_date = DLookup("Contractual_Paid_to_Date", "ECS-renewals", "Policy_Number=" & Me.Policy_Number)
End If
End If

MFYP_FRYP = DLookup("MFYP_FRYP", "Telecalling_database", "Instrument_number=" & Me.Form_S_No)
Servicing_Agent_ID = DLookup("SERVICING_AGENT_ID", "Telecalling_database", "Instrument_number=" & Me.Form_S_No)
Agent_Work_No = DLookup("Agent_Work_No", "Telecalling_database", "Instrument_number=" & Me.Form_S_No)
Agent_Mobile_No_1 = DLookup("Agent_Mobile_No_1", "Telecalling_database", "Instrument_number=" & Me.Form_S_No)
Agent_Mobile_No_2 = DLookup("Agent_Mobile_No_2", "Telecalling_database", "Instrument_number=" & Me.Form_S_No)
Agent_Home_No = DLookup("Agent_Home_No", "Telecalling_database", "Instrument_number=" & Me.Form_S_No)
INSTRUMENT_NUMBER = DLookup("INSTRUMENT_NUMBER", "Telecalling_database", "Instrument_number=" & Me.Form_S_No)
Instrument_amount = DLookup("INSTRUMENT_AMOUNT", "Telecalling_database", "Instrument_number=" & Me.Form_S_No)
Bounce_date = DLookup("Bounce_date", "Telecalling_database", "Instrument_number=" & Me.Form_S_No)
Bank_name = DLookup("BANK_NAME", "Telecalling_database", "Instrument_number=" & Me.Form_S_No)
BOUNCE_REASON = DLookup("BOUNCE_REASON", "Telecalling_database", "Instrument_number=" & Me.Form_S_No)
Service_provider = DLookup("SERVICE_PROVIDER", "Telecalling_database", "Instrument_number=" & Me.Form_S_No)
Draw_date = DLookup("DRAW_DATE", "Telecalling_database", "Instrument_number=" & Me.Form_S_No)
Type_of_Calling = DLookup("Type_of_calling", "Telecalling_database", "Instrument_number=" & Me.Form_S_No)

If (IsNull(ELookup("Instrument_Number", "History_Telecalling", "Instrument_Number=" & Me.INSTRUMENT_NUMBER)) = False) Then
DoCmd.OpenForm ("History_Query")
End If

Total_calls_for_the_day = DCount("Calling_end_Date_time", "Telecalling_Database", "[Calling_END_date_Time]>=Date() And Caller_Name = Telecaller_Name")
Total_calls_for_the_month = DCount("Calling_end_Date_time", "Telecalling_Database", "Year([Calling_END_date_Time])=Year(Now()) And Month([Calling_END_date_Time])=Month(Now()) And Caller_Name = Telecaller_Name") + (DCount("Follow_up_date", "History_Telecalling", "Year([Follow_up_date])=Year(Now()) And Month([Follow_up_date])=Month(Now()) And Caller_Name = Telecaller_Name"))
Repeat_calls_for_the_day = DCount("Follow_up_date", "History_Telecalling", "[Follow_up_date]>=Date() And Caller_Name = Telecaller_Name")
Repeat_calls_for_the_month = DCount("Follow_up_date", "History_Telecalling", "Year([Follow_up_date])=Year(Now()) And Month([Follow_up_date])=Month(Now()) And Caller_Name = Telecaller_Name")
Pending_Calls_for_the_Month = DCount("Caller_Name", "Telecalling_database", "Calling_code is null And Caller_Name = Telecaller_name")
End Sub

Thanks in advance..!!!

[nO_OnE]

I knoticed that you are using 'dlookup' multiple times for multiple fields for the same record. You can use 'DLookup' to return all of these values at once then split them into different variables.

[nO_OnE]

This code takes the first 4 fields you looked up with 'DLookup'. It combines them into one lookup function it stores them into one variable 'Longvar', it separates each field with a ';', and then splits them up into their appropriate variable names...

Longvar = DLookup("[Policy_No] & ':' & [GO_CODE_Ingenium] & ';' & [Modal_Premium] & ';' & [Frequency]", "Telecalling_database", "Instrument_number=" & Me.Form_S_No)

    Policy_Number = Split(Longvar, ";")(0)
    GO_Code = Split(Longvar, ";")(1)
    Modal_Premium = Split(Longvar, ";")(2)
    Frequency = split(Longvar, ";")(3)

[balvinder]

Thanks nO_OnE..

Will surely try this on monday when I'll reach office..

By the way I have found another alternative.
What i have done is, I have created one query to pull all fields from table for particular Instrument_number using [Forms]![Telecalling_entry]![Form_S_No] & call this saved query just before dlookup's & assigned query name in place of Table name.

Data has started coming more quickly compare to earlier. Now it's taking almost half time. But I'am going to check your method as well before making this code live among users.

Thanks for your valuable input.

[dmaruca]

You certainly can't speed up the round trip time of the network backend, but you certainly can cut down on the number of trips is has to make. That is a lot of DLookups. I would seriously consider using either DAO or ADO recordsets and grabbing all of the data at once. I recommend against delimiting your dlookups and combining them. It's inelegant and error prone. That's what recordsets are for.

[balvinder]

Dear dmaruca,

I am also interested in using recordset functionality & i appreciate your concern with many dlookup's. But i'am helpless since i don't know how to do it....I'am really looking forward to learn this but not able to find out any suitable & crisp place or link.

If you can help me in this...this would be really great...!!!

[dmaruca]

Check out some examples here.

http://msdn.microsoft.com/en-us/libr...ice.12%29.aspx

This is how to iterate rows in the most basic fashion. They are editing fields here, but the same loops are used to read.

http://msdn.microsoft.com/en-us/libr...ice.12%29.aspx

[balvinder]

Thanks a ton dmaruca...!!!

Link you gave are really great & user friendly..