DLookup Multiple Criteria not selection correctly

[Guest]

I thought I had this code working (it looked like it was untill I tested
properly).

I want the dlookup to find if there are any existing records containing the
same combination of Me.OrderID, Me.ProductID and Me.PackID (i.e a duplicate
record). The dlookup however does not seem to be selecting properly. It
seems to lookup details from the whole dbo_OrderDetails table, rather than
the specitic OrderID (me.orderID)

This is doing my head in!


Dim strWhere As String
If Not IsNull(Me.OrderID) Then
strWhere = "[OrderID]=" & Me.OrderID
End If

If Not IsNull(Me.ProductID) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "[ProductID]=""" & Me.ProductID & """"
End If

If Not IsNull([PackID]) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & Me.PackID
End If

If Len(strWhere) = 0 Then
MsgBox "No Criteria Entered"
Else
If IsNull(DLookup("[OrderID]", "dbo_OrderDetails", strWhere)) Then
Else
MsgBox "This Product and Packaging have already entered."
Cancel = True
Me.Undo
End If
End If

 

[Ken Snell \(MVP\)]

Assuming that you're correctly delimiting just the text field with the '
character, you didn't put the field name in the part of the code where you
test for PackID value; here is corrected code, assuming that PackID is a
numeric field:

If Not IsNull([PackID]) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "PackID=" & Me.PackID
End If

 

[Guest]

Problem solved! Thank you very much.

Assuming that you're correctly delimiting just the text field with the '
character, you didn't put the field name in the part of the code where you
test for PackID value; here is corrected code, assuming that PackID is a
numeric field:

If Not IsNull([PackID]) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "PackID=" & Me.PackID
End If

--

Ken Snell
<MS ACCESS MVP>

I thought I had this code working (it looked like it was untill I tested
properly).

I want the dlookup to find if there are any existing records containing
the
same combination of Me.OrderID, Me.ProductID and Me.PackID (i.e a
duplicate
record). The dlookup however does not seem to be selecting properly. It
seems to lookup details from the whole dbo_OrderDetails table, rather than
the specitic OrderID (me.orderID)

This is doing my head in!


Dim strWhere As String
If Not IsNull(Me.OrderID) Then
strWhere = "[OrderID]=" & Me.OrderID
End If

If Not IsNull(Me.ProductID) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "[ProductID]=""" & Me.ProductID & """"
End If

If Not IsNull([PackID]) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & Me.PackID
End If

If Len(strWhere) = 0 Then
MsgBox "No Criteria Entered"
Else
If IsNull(DLookup("[OrderID]", "dbo_OrderDetails", strWhere))
Then
Else
MsgBox "This Product and Packaging have already entered."
Cancel = True
Me.Undo
End If
End If