Click here to Skip to main content
15,867,870 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.

VB
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


What I have tried:

I have tried several variations using the "" and ' within the stLinkCriteria ='s code, and if the Dlookup as well.
Posted
Updated 3-Apr-21 19:40pm
Comments
Richard MacCutchan 4-Apr-21 3:30am    
Use the debugger to see the actual values that are being compared.
RedDk 8-Apr-21 12:12pm    
Sage advice from RMC. To debug open Access, tab to "Database Tools", choose "Visual Basic" to open code editor, then pick a module and highlight a variable. On the main menu, under debug, click "Add Watch" and see the "Watches" window with the chosen "Expression" now in the list it's value (most likely) [left arrow] Out of context [right-arrow].

The debugger is run by two keys on your keyboard, F8 to "Step into" the highlighted code's block, F5 to "Run" to the end of the procedure (assuming you've set no "Breakpoints"). Using F5 with breakpoints set will cause the debug run to "go" until it comes to a breakpoint at which time hitting F5 again will allow the debug run to continue to the next statement. By observing the "Value" of the highlighted Watch variable in the Watches window you should be able to see how everything proceeds.

Once familiar with this process you will begin to use Ctrl and Ctrl+Shift patterns with F8 to navigate around. It's as simple as that.

1 solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900