DLookup Error

  • Thread starter Thread starter Joy
  • Start date Start date
J

Joy

In my database I have the following code to search for
possible duplicate records based upon CaseID and CaseType
ID. As the user types in the InsPolicyOrAcctNo, the
database searches for the possible duplicate. This code
worked fine, proviing a soft edit for possible duplicates
until I changed the tab order on the form. Now I get this
error and I just can't seem to understand why tab order
would mess with the code. The error is Run-time
error "3075" Syntaxs error (missing operator) in query
expression '[CaseTypeID]= AND[InsPolicyOrAcctNo]=
0000000000000"

Here is the code I'm using:

Private Sub InsPolicyOrAcctNo_Exit(Cancel As Integer)

Dim strExistingCaseID As String

strExistingCaseID = Nz(DLookup
("[CaseID]", "tblCases", "[CaseTypeID]=" & Me.CaseTypeID
& " AND [InsPolicyOrAcctNo]='" & Me.InsPolicyOrAcctNo
& "'"), "")

If strExistingCaseID <> "" Then
MsgBox "A possible duplicate case was created on "
& DLookup("[DateReceived]", "tblCases", "[CaseID]='" &
strExistingCaseID & "'") & "." & vbCrLf & vbCrLf & "Please
refer to this case ID: " & strExistingCaseID, vbOKOnly +
vbExclamation, "Warning"
End If

End Sub
 
Joy said:
In my database I have the following code to search for
possible duplicate records based upon CaseID and CaseType
ID. As the user types in the InsPolicyOrAcctNo, the
database searches for the possible duplicate. This code
worked fine, proviing a soft edit for possible duplicates
until I changed the tab order on the form. Now I get this
error and I just can't seem to understand why tab order
would mess with the code. The error is Run-time
error "3075" Syntaxs error (missing operator) in query
expression '[CaseTypeID]= AND[InsPolicyOrAcctNo]=
0000000000000"

Here is the code I'm using:

Private Sub InsPolicyOrAcctNo_Exit(Cancel As Integer)

Dim strExistingCaseID As String

strExistingCaseID = Nz(DLookup
("[CaseID]", "tblCases", "[CaseTypeID]=" & Me.CaseTypeID
& " AND [InsPolicyOrAcctNo]='" & Me.InsPolicyOrAcctNo
& "'"), "")

If strExistingCaseID <> "" Then
MsgBox "A possible duplicate case was created on "
& DLookup("[DateReceived]", "tblCases", "[CaseID]='" &
strExistingCaseID & "'") & "." & vbCrLf & vbCrLf & "Please
refer to this case ID: " & strExistingCaseID, vbOKOnly +
vbExclamation, "Warning"
End If

End Sub
Click to expand...


It looks like the Me.CaseTypeID has not been entered when
the code executes.
 
Back
Top