question

JosephWillis-7329 avatar image
0 Votes"
JosephWillis-7329 asked thedbguy answered

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
office-access-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

thedbguy avatar image
0 Votes"
thedbguy answered

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...




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.