I have a conditional macro that I use to check data entered on a form [invoice log table 4]. My checking looks at the supplier name [supplier name], and invoice number [invoiceno] fields, in a table [invoice log table], to see if the invoice already exists, and should thus not be entered again.
I'm doing this checking with the following bit of code as the condition
DLookUp("[InvoiceNO]","[Invoice log table]","[InvoiceNO] = '" & [Forms]![Invoice Log Table 4]![InvoiceNO] & "'" & " AND [Supplier Name]= '" & [Forms]![Invoice Log Table 4]![Supplier Name] & "'") Is Not Null
This code works fine until the supplier name has an apostrophe in it, at which point an syntax error message pops up. I've tried to use 4 double quotes ("""") in front of and after the supplier name, which worked for the VBA part, but it doesnt seem to work here.
Looking forward to your bright ideas and thanks in advance.