Type Mismatch Error

[thexxvi]

Good morning.

I am working on a form and have a textbox (called Transaction ID) that allows a user to type in a string of 18 characters, no more, no less. The Transaction ID textbox is bound to the table called "Credit Listening Data" and the corresponding field in the table is called Transaction ID. I have an 'After Update' code in vba that reads like this:

Private Sub Transaction_ID_AfterUpdate()
If (DLookup("[Transaction ID]", "Credit Listening Data", "[Transaction ID]=[Forms]![Credit Listening Form]![Transaction ID]")) Then
MsgBox "The Transaction ID has already been used. Please enter a different Transaction ID.", vbInformation, "Duplicate Transaction ID"
End If
End Sub


Whenever I enter a string into that field that's not a duplicate value in the table, everything works fine. When I type in a duplicate value, I get a 'Type Mismatch' error that points back to this portion of the VBA code.

If (DLookup("[Transaction ID]", "Credit Listening Data", "[Transaction ID]=[Forms]![Credit Listening Form]![Transaction ID]")) Then


Any thoughts on how to resolve this?

Thank you in advance.

[NoellaG]

Hi,

You have to pull the variable part of the criterium out of the quotes. As the id field is a string you could try:

DLookup("[Transaction ID]", "Credit Listening Data", "[Transaction ID]= """ & [Forms]![Credit Listening Form]![Transaction ID] & """")

[thexxvi]

Thanks for the thought. I tried it, but I'm still getting the same 'Type Mismatch' error on the same line.

[thexxvi]

Alright so it looks like it's not really the code because it works fine when the field is all digits, but it doesn't work at all when there's any letters in the field. The table shows the field is short text and the field on the form is just a textbox. why would it react this way to letters? I need to be able to enter a combination of letters and numbers in the field, not just numbers.