Fed up with Dlookup's

B

balvinder

Registered User.
Local time
Today, 22:06
Joined
Jun 26, 2011
Messages
47
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..:p

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.

Code: 
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 '%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..!!!
 
I would use saved queries, and only use Domain Functions to extract the final value.
 
I agree with Thales750. Also, I'd be interested in seeing your tables and relationships as well as the indexes you have set up.
 
Sorry...Thales750...

I actually don't know what are the saved queries..:confused:

If you can please clarify with some example then it would be great to understand.

jdraw --> I have only primary key "Instrument_Number" in my table (Telecalling_Database). Also i haven't establish any relationship with anything. This is just direct pick from table to VBA form post selecting Telecaller name from drop down list of VBA form.

What i think is initial DCount functions are not taking much time but dlookup's & last dcount functions are.

I'am more interested in solution if that exist: fetch all dlookup details through one command from my table(something like Select TOP 1 row) & show it on VBA form text boxes.

Please correct me if i'am wrong somewhere...
 
Are you saying you only have 1 table? Could you show us the table layout?

A stored query or a saved query or named query is a query that you have set up and saved with a name.
see this for a sample
http://www.baycongroup.com/access2007/05_access.html

Also, what is the purpose of your sub procedure-- in plain english? Perhaps there are other options.
 
I have attached my table layout & My VBA form Snapshot. Well, i don't have any saved or stored query for this procedure.

My objective for using this procedure is to find out "Instrument Number" along with tele-calling details when user select his/her name from drop down. (See VBA form image)

As per process, we have to follow some conditions to find out instrument number on which tele-calling need to be done. It is actually somewhat based on Dialor concept (Call center, BPO's).

I have similar kind of code for "SAVE" button to pull out next tele-calling details post saving current details in table. There also, it is taking time to pull next details (almost 7-10 seconds) however record is getting saved in less than second time. I have identified that by putting msgbox command post save code.
 

Attachments

  • Telecalling_Database.accdb
    Telecalling_Database.accdb
    1.6 MB · Views: 121
  • VBA Form Snapshot.jpg
    VBA Form Snapshot.jpg
    94.2 KB · Views: 114
Hmmm, I would suggest...

That that table should be split into two (or for me, more) tables. You are treating Access like Excel which is part of the problem. If it were me I would break the tables apart, create the proper relationships then you could create your form on stored queries without all the DCount's and DLookup's. For example...

tblClients
cClientID (PK)

tblClientPolicies (I am thinking Client's can have more then one policy which would make this table twice as useful.)
cpClientPolicyID (PK)
cpClientID (FK - relate to tblClients)

tblTransactions
tTransactionID (PK)
tClientID (FK - relate to tblClients)
tInstrumentID (FK - relate to tblInstruments)
tAgentID (FK - relate to tblAgents)

tblTeleCalls
tcTeleCallID (PK)
tcClientID (FK - relate to tblClients)

tblInstruments
iInstruments (PK)

tblAgents
aAgentID (PK)
 
Thanks for valuable suggestion...

But would like to know one thing...will this reduce time frame it is currently taking in pulling next calling details from table?

I have some doubts because when it is taking so much time right now to extract information directly from table without any extra linkages with any other table, it will surely take more time with relationships tables.

Well, simultaneously i'am trying for saved query process.

Just for information: I have recently split my database & it's in network shared drive now. But before splitting it was taking less than one second to pull details from table in both "Telecaller_name change () & SAVE() events.

Can this could be reason for slowness?
 
Thanks Thales750, jdraw & GinaWhipp for your valuable suggestions...!!!

I guess your saved query idea is working..I have checked by splitting my database again at home & its working almost 5 times faster than dlookup. Rest will post tomorrow afternoon when i'll reach office & test it on network drive.

:)
 
Just a question...

You seem to have an unbound form. Any reason for this?

Some 29 of the fields that you are looking up are coming from the same record. So I would set the Record Source of your form to the Telecalling_database table. Then all you need to do is set the filter for the form like this:
Me.Filter = "[Instrument_number]=" & Me.Form_S_No
Me.FilterOn = True

That’s the worst of your problem sorted I think. that kills 29 dlookups

But I agree with Gina about splitting the tables also.

And you still need to deal with the statistics elements. As suggested, these could be queries.

Chris
 
Dear Stopher,

I'am sorry but I'am not really aware with Bound or Unbound thing. As i said I'am quite new in VBA hence I used text box from control toolbox & just coded as i want. If you could please throw some light on this part..that would be great help to my knowledge.

However that Saved Query idea worked pretty well. That reduced almost half time as it was taking earlier with direct dlookup's with table.

But I'am interested in information regarding recordsource part you described in last post. If that can reduce more time than saved query then I'll try to use it. But would need your help since i don't know anything about recordsource.
 
Normalization is the most important consideration going forward. Everything else is a band aid, on a band aid.

The dark side is learning too much about code before you learn enough about data structure, learn the force young Padawan.

“Once you start down the dark path, forever will it dominate”.
Yoda
 
You must log in or register to reply here.

Similar threads

B
Recordset use..!!!
Replies
17
Views
2,188
pbaldy
pbaldy
B
ADO code Error
Replies
3
Views
1,827
JANR
J
B
Update Command Error
Replies
5
Views
704
boblarson
boblarson
B
Concatenating Date & Text using Currentdb.execute
Replies
2
Views
1,473
balvinder
B

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom