apostrophe error in macro

Hello

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.

Re: apostrophe error in macro

hey jva, cheers for the reply, i tried what u suggested, and moved it from a conditional macro to VBA.

i now have the following code:

DLookup("[invoiceno]", "[invoice log table]", "[invoiceno] = " & Chr$(34) & [Forms]![Invoice Log Table 4]![InvoiceNo] & Chr$(34) & " and [Supplier Name]= " & Chr$(34) & [Forms]![Invoice Log Table 4]![Supplier Name] & Chr$(34))

the code returns a blank supplier name if the supplier name has an apostrophe, and it crashes if the supplier name doesn't contain an apostrophe.... :confused:
any ideas?

Re: apostrophe error in macro

I'm not sure what you mean....

replace in the underlying table? or in the code?

Cheers.

Johnny

Re: apostrophe error in macro [solved]

I am not sure if that's possbile since i'm calling the field, and not a specific record.

I solved this by using the following code:

Private Sub Supplier_Name_BeforeUpdate(Cancel As Integer)
On Error GoTo SuppName_Err
Dim sCriteria As String


sCriteria = "[invoiceno] = '" & [Forms]![Invoice Log Table 4]![InvoiceNo] & "'" & "And [Supplier Name] = " & """" & Me![Supplier Name] & """"


If Nz(DLookup("[invoiceno]", "[invoice log table]", sCriteria), 99) <> 99 Then
MsgBox "Duplicate Invoice Number. Please erase supplier and check your entry.", vbOKOnly, "Supplier/Invoice Number Validation"
Cancel = True
End If


SuppName_Exit:
   Exit Sub


SuppName_Err:
    MsgBox Error$
    Resume SuppName_Exit


End Sub