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