DLookup Multiple Criteria not selection correctly

[Penstar]

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 dbrderDetails 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]", "dbrderDetails", 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

 

[Penstar]

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 dbrderDetails 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]", "dbrderDetails", strWhere))
Then
Else
MsgBox "This Product and Packaging have already entered."
Cancel = True
Me.Undo
End If
End If