Run-Time Error '13' Type Mismatch - ACCESS DATABASE

Question

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 1

Hi. Couple of things: When checking for duplicates,

1. I usually use the BeforeUpdate event, and
2. I tend to use the DCount() function

So, for example, perhaps something similar to this:

Private Sub txtSealNumber_BeforeUpdate(Cancel As Integer)

Dim strCriteria As String

strCriteria = "TrailerNumber='" & Me.txtTrailerNumber & "' AND SealNumber='" & Me.SealNumber & "'"

If DCount("*", "Tab_TrailerDetails", strCriteria) > 0 Then
    Cancel=True
    Me.txtSealNumber.Undo
    MsgBox "Please make sure...", vbInformation, "Duplicate!"
End If

End Sub

Hope that helps...