Why isn't this Dlookup code firing?

[papic1972]

Hi All,

I have the following code in an onclick event on a form (frmLoadAllocationUN):

If DLookup("LoadBoxNo = " & Forms!frmLoadAllocationUN!cboLoad.Column(0), "qryDrivera", "DriverNo = " & Forms!frmLoadAllocationUN!cboDriverBD1.Column(1)) Then

MsgBox "You have already allocated a load to Load " & Forms!frmLoadAllocationUN!cboLoad.Column(0) & ". Please allocate to another load slot.", vbCritical, "Message from ZP"
Else
MsgBox "Test OK"

End If


It fails to work & i don't understand why! Am i missing something in the first line?

 

[JANR]

DLookup uses this syntax:

Dlookup("Field", "Domain", "Criteria")

aka you can't put logic inside the statement.

If cboDriverBD1.Column(1) is a NUMBER

If Me!cboLoad = Nz(Dlookup("LoadBoxNo", "qryDivera", "DriverNo = " & Me!cboDriverBD1.Column(1)),0) Then 
    MsgBox "...."
End If

If cboDriverBD1.Column(1) is TEXT:

If Me!cboLoad = Nz(Dlookup("LoadBoxNo", "qryDivera", "DriverNo = '" & Me!cboDriverBD1.Column(1) & "'"),"") Then 
    MsgBox "...."
End If

JR

 

[papic1972]

OK, thanks JR.

cboDriverBD1.Column(1) is a number so i've used your first statement.
However, the code is still not firing properly.

It seems to be only looking up the very first record in qryDrivera only & not scanning through the whole query. Does this sound right?

 

[boblarson]

A Dlookup pulls the first value it finds. It doesn't search through all values if it finds one that matches. If you want a specific value then you need to make sure your query is sorted to have the value you want come first (don't use FIRST as that doesn't work, MAX would be the way to go).

 

[papic1972]

Thanks Bob. The problem i have is that i can't use MAX, i need to check which LoadBoxNo hasn't been used for the procedure to continue. LoadBoxNo can be any number between 1 and 5 (inclusive). Am i using Dlookup incorrectly here?

 

[JANR]

If you want to check to see if a driver dosen't get allocated lets say loadbox number 3 twice, then you can add a second argument in the criteriapart of the Dlookup or you can use Dcount.

Dim strWhere As String
strWhere = "DriverNo = " & Me!cboDriverBD1.Column(1)
strWhere = strWhere & " And LoadBoxNo = " & Me!cboLoad  'If cboLoad is a Number.
'strWhere = strWhere & " And LoadBoxNo = '" & Me!cboLoad & "'"  ' Use this if cboLoad is Text
 
If Dcount("*", qryDivera", strWhere) > 0 Then
   MSgBox " Already Allocated "
End If

JR

 

[papic1972]

JR,

That hit the spot! Perfect!
Thank you so much!

 

[papic1972]

JR,

I have one other question!
If i populate cboLoad & then decide to remove the value in cboLoad i get the following error:
Run-Time error '3075':
Syntax error (missing operator) in query expression 'DriverNo=1' And LoadBoxNo ='

and the code breaks to highlight

If Dcount("*", qryDivera", strWhere) > 0 Then


How do i stop this error from appearing?

 

[JANR]

You test for IsNull in cboLoad, if it's Null then you don't call Dount but exit the sub

Something like this:

Dim strWhere As String
If IsNull(Me!cboDriverBD1) Then
   MsgBox " Select Driver!"
   Exit Sub
End If
 
If Not IsNull(Me!cboLoad) Then
   strWhere = " DiverNo = " & Me!cboDriverBD1.Column(1)
   strWhere = strWhere & " And LoadBoxNo = " & Me!cboLoad
Else
   Exit Sub
End If
 
If Dcount("*", "qryDivera", strWhere) > 0 Then
   MsgBox " Already Allocated"
End If

JR

 

[papic1972]

That is awesome JR!! I can't thank you enough!!!
Thank you x million!!