Does anyone know a way to make this Dlookup perform faster? (1 Viewer)

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?

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.

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]));

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)?

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