Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-18-2010, 02:36 PM   #1
papic1972
Newly Registered User
 
Join Date: Apr 2004
Posts: 122
Thanks: 13
Thanked 0 Times in 0 Posts
papic1972 is on a distinguished road
Does anyone know a way to make this Dlookup perform faster?

Hi all,

This is a sub that i call upon opening form "frmDriverEdit" from a command button that is placed on another form "frmAtaGlance":

Sub CheckDriver1()

If DLookup("[ConfirmedLoad]=0", "qryDrivera") Or Not Forms!frmDriverEdit!lstDrivera.ListCount > 0 Then
Forms!frmDriverEdit!lstDrivera.BackColor = 16777215
Forms!frmDriverEdit!lstDrivera.Locked = False
Forms!frmDriverEdit!Check1312 = False
Forms!frmDriverEdit!lblUnconfirma.Visible = True
Forms!frmDriverEdit!lblConfirma.Visible = False
Else
If Not DLookup("[ConfirmedLoad]=0", "qryDrivera") Then
Forms!frmDriverEdit!lstDrivera.BackColor = 16769482
Forms!frmDriverEdit!lstDrivera.Locked = True
Forms!frmDriverEdit!Check1312 = True
Forms!frmDriverEdit!lblUnconfirma.Visible = False
Forms!frmDriverEdit!lblConfirma.Visible = True
End If
End If
End Sub

I find that "frmDriverEdit" is quite cumbersome & slow in opening up to display the correct data due to Dlookup. Does anyone know a way to make this perform alot faster?

papic1972 is offline   Reply With Quote
Old 10-18-2010, 03:08 PM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,477
Thanks: 15
Thanked 4,147 Times in 4,078 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Does anyone know a way to make this Dlookup perform faster?

My guess is that it would involve speeding up the query. How fast does it return what I assume is its single record? What is the SQL? There are code alternatives to DLookup that are technically faster, but I doubt it would be significant enough for you.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Old 10-18-2010, 04:22 PM   #3
papic1972
Newly Registered User
 
Join Date: Apr 2004
Posts: 122
Thanks: 13
Thanked 0 Times in 0 Posts
papic1972 is on a distinguished road
Re: Does anyone know a way to make this Dlookup perform faster?

Hi Paul,

Yes i need to speed up the query. Opening the form "frmDriverEdit" takes about 10 seconds, which is way too slow.


This is full sub:

Sub CheckDriver1()

If DLookup("[ConfirmedLoad]=0", "qryDrivera") Or Not Forms!frmDriverEdit!lstDrivera.ListCount > 0 Then
Forms!frmDriverEdit!lstDrivera.BackColor = 16777215
Forms!frmDriverEdit!lstDrivera.Locked = False
Forms!frmDriverEdit!Check1312 = False
Forms!frmDriverEdit!lblUnconfirma.Visible = True
Forms!frmDriverEdit!lblConfirma.Visible = False
Else
If Not DLookup("[ConfirmedLoad]=0", "qryDrivera") Then
Forms!frmDriverEdit!lstDrivera.BackColor = 16769482
Forms!frmDriverEdit!lstDrivera.Locked = True
Forms!frmDriverEdit!Check1312 = True
Forms!frmDriverEdit!lblUnconfirma.Visible = False
Forms!frmDriverEdit!lblConfirma.Visible = True
End If
End If


If DLookup("[ConfirmedLoad]=0", "qryDriverb") Or Not Forms!frmDriverEdit!lstDriverb.ListCount > 0 Then
Forms!frmDriverEdit!lstDriverb.BackColor = 16777215
Forms!frmDriverEdit!lstDriverb.Locked = False
Forms!frmDriverEdit!Check1315 = False
Forms!frmDriverEdit!lblUnconfirmb.Visible = True
Forms!frmDriverEdit!lblConfirmb.Visible = False
Else
If Not DLookup("[ConfirmedLoad]=0", "qryDriverb") Then
Forms!frmDriverEdit!lstDriverb.BackColor = 16769482
Forms!frmDriverEdit!lstDriverb.Locked = True
Forms!frmDriverEdit!Check1315 = True
Forms!frmDriverEdit!lblUnconfirmb.Visible = False
Forms!frmDriverEdit!lblConfirmb.Visible = True
End If
End If


If DLookup("[ConfirmedLoad]=0", "qryDriverc") Or Not Forms!frmDriverEdit!lstDriverc.ListCount > 0 Then
Forms!frmDriverEdit!lstDriverc.BackColor = 16777215
Forms!frmDriverEdit!lstDriverc.Locked = False
Forms!frmDriverEdit!Check1317 = False
Forms!frmDriverEdit!lblUnconfirmc.Visible = True
Forms!frmDriverEdit!lblConfirmc.Visible = False
Else
If Not DLookup("[ConfirmedLoad]=0", "qryDriverc") Then
Forms!frmDriverEdit!lstDriverc.BackColor = 16769482
Forms!frmDriverEdit!lstDriverc.Locked = True
Forms!frmDriverEdit!Check1317 = True
Forms!frmDriverEdit!lblUnconfirmc.Visible = False
Forms!frmDriverEdit!lblConfirmc.Visible = True
End If
End If


If DLookup("[ConfirmedLoad]=0", "qryDriverd") Or Not Forms!frmDriverEdit!lstDriverd.ListCount > 0 Then
Forms!frmDriverEdit!lstDriverd.BackColor = 16777215
Forms!frmDriverEdit!lstDriverd.Locked = False
Forms!frmDriverEdit!Check1319 = False
Forms!frmDriverEdit!lblUnconfirmd.Visible = True
Forms!frmDriverEdit!lblConfirmd.Visible = False
Else
If Not DLookup("[ConfirmedLoad]=0", "qryDriverd") Then
Forms!frmDriverEdit!lstDriverd.BackColor = 16769482
Forms!frmDriverEdit!lstDriverd.Locked = True
Forms!frmDriverEdit!Check1319 = True
Forms!frmDriverEdit!lblUnconfirmd.Visible = False
Forms!frmDriverEdit!lblConfirmd.Visible = True
End If
End If


If DLookup("[ConfirmedLoad]=0", "qryDrivere") Or Not Forms!frmDriverEdit!lstDrivere.ListCount > 0 Then
Forms!frmDriverEdit!lstDrivere.BackColor = 16777215
Forms!frmDriverEdit!lstDrivere.Locked = False
Forms!frmDriverEdit!Check1321 = False
Forms!frmDriverEdit!lblUnconfirme.Visible = True
Forms!frmDriverEdit!lblConfirme.Visible = False
Else
If Not DLookup("[ConfirmedLoad]=0", "qryDrivere") Then
Forms!frmDriverEdit!lstDrivere.BackColor = 16769482
Forms!frmDriverEdit!lstDrivere.Locked = True
Forms!frmDriverEdit!Check1321 = True
Forms!frmDriverEdit!lblUnconfirme.Visible = False
Forms!frmDriverEdit!lblConfirme.Visible = True
End If
End If
End Sub


and the SQL behind qryDrivera (for example is



SELECT tblLocal.LoadLineID, tblLocal.Suburb, tblContract.Contract, tblLocal.Yard, tblLocal.BrickType, tblLocal.Qty, tblLocal.DeliveryDate, tblLocal.Plant, tblLocal.SpecialReq, tblLocal.TruckType, tblLocal.AreaKlm, tblLocal.Pallets, tblContract.ContractID, tblLocal.LoadBoxNo, tblLocal.Driver, tblLocal.Basket, tblLocal.JDNo, tblLocal.DriverNo, tblLocal.LoadBoxNo, tblLocal.Allocated, tblLocal.Abbrev, tblLocal.Confirmed, tblLocal.ConfirmedLoad, tblContract.Abbrev, IIf([tblContract].[Abbrev] Is Null,[Plant],[tblContract].[Abbrev]) AS Abbrev2, tblLocal.DeliverySlot, tblLocal.Builder, tblLocal.MinQty
FROM tblLocal INNER JOIN tblContract ON tblLocal.ContractID = tblContract.ContractID
WHERE (((tblLocal.DeliveryDate)=[Forms]![frmLoadAllocation]![cboAllocationDate]) AND ((tblLocal.LoadBoxNo) Like "1") AND ((tblLocal.Driver)=[Forms]![frmDriverEdit]![txtDriver]));

papic1972 is offline   Reply With Quote
Old 10-18-2010, 10:00 PM   #4
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,477
Thanks: 15
Thanked 4,147 Times in 4,078 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Does anyone know a way to make this Dlookup perform faster?

How do the various queries differ? Could they perhaps be one query that returns multiple records, and you use a recordset to get them all and then examine the results? That would make for 1 trip to the data instead of 5-10. Are the fields in the WHERE clause indexed? Given that all you test is one field, must the query return so many fields (especially from the joined table, the dropping of which would enable you to drop the join)?
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Old 10-19-2010, 02:45 PM   #5
papic1972
Newly Registered User
 
Join Date: Apr 2004
Posts: 122
Thanks: 13
Thanked 0 Times in 0 Posts
papic1972 is on a distinguished road
Re: Does anyone know a way to make this Dlookup perform faster?

Cool, thanks Paul. I'll test this & post the outcome here.

papic1972 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Using macro to make command buttons perform queries and reports elektro Macros 2 09-25-2016 11:03 PM
The Department of Positive Out of Body Possibilities Tim Brewer The Watercooler 905 06-15-2009 10:10 AM
Which is Faster: passthru qry or DLookup() craigachan General 5 04-21-2008 04:30 AM
How To Make Query Run Faster jereece Queries 5 03-26-2008 10:45 AM
Is there a way to make this work faster? bjreb Modules & VBA 4 01-08-2008 03:32 AM




All times are GMT -8. The time now is 01:19 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World