Checking for duplicates with DLookup 1

[sirnose1]

I have a textbox on a form that when you lose focus it checks for duplicate. Works fine EXCEPT: If I type a word with an apostrophe I get a syntax error. "tonys" will work, "tony's" will give me the error. Here is the code.

Criteria = "[Prodres] = '" & Me.[txtprodres] & "'"
If Not IsNull(DLookup("[Prodres]", "tblMain", Criteria)) Then
DoCmd.RunSQL " INSERT INTO tblProdDup(DupRec) VALUES (txtprodres);"
MsgBox " Similar existing record. Please check for duplication

 

[PHV]

Criteria = "[Prodres] = [tt]'"[/tt] & Replace(Me.[txtprodres], [tt]"'", "''") & "'"[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886