Run-Time Error ’13’ Type Mismatch – ACCESS DATABASE

I am trying to compare two text fields txtTrailerNumber and txtSealNumber to the database table Tab_TrailerDetails. [TrailerNumber] and [SealNumber] as listed in the table.

I am trying to get the database to look at the trailer number entered into the form, and if it finds a duplicate value it then looks at the seal number entered into the form. If both values have a duplicate found in the table it should throw up the Msg_Box error code.

Private Sub txtSealNumber_AfterUpdate()

Dim NewTrailer, NewSeal As String
Dim stLinkCriteria As String


'Assign the entered Trailer Number and Seal Number to a variable
NewTrailer = Me.txtTrailerNumber.Value
NewSeal = Me.txtSealNumber.Value

stLinkCriteria = ("[TrailerNumber]='" & NewTrailer & "'" And "[SealNumber]='" & NewSeal & "'")

If Me.txtTrailerNumber = DLookup("[TrailerNumber]", "Tab_TrailerDetails", stLinkCriteria) Then

   MsgBox "This trailer, " & NewTrailer & ", has already been entered in database," _
              & vbCr & vbCr & "along with seal " & NewSeal & "" _
              & vbCr & vbCr & "Please make sure Trailer and Seal are not already entered.", vbInformation, "Duplicate information"
     
'undo the process and clear all fields
    Me.Undo
    

End If

End Sub

Answer

The cause of the error is that you have a logical keyword, notably AND inside a string expression. Change your code to

stLinkCriteria = ("[TrailerNumber]='" & NewTrailer & "' And [SealNumber]='" & NewSeal & "'")

Advertisement

Answer