Dlookup results in error if an an apostrophe is in the Vendor Name

[hardhitter06]

Hi All,

Access 2003.

I have an Input form that stores a new "VendorName" and "VendorFedID". I later use this table to select the Vendor on a different form and attach a W9 file.

On the first input form adding a vendor (with fed id), if I add a Vendor's name with an Apostrophe somewhere in the field, I get an Acces Halt-Debug error.

Here is my code for this Vendor Field:

Private Sub VendorName_BeforeUpdate(Cancel As Integer)
 Dim Answer As Variant
[B][COLOR="Red"][COLOR="Yellow"] Answer = DLookup("[VendorName]", "tblInputNewVendor", "[VendorName] = '" & Me.VendorName & "'")[/COLOR][/COLOR]
 If Not IsNull(Answer)[/B] Then
 MsgBox "Duplicate Vendor Name Found." & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
 
 Cancel = True
 Me.VendorName.Undo

 Else:
 End If
End Sub

The line in yellow is where the error is taking place. Is there a way to allow apostrophes?

Thank you in advance,

Josh

 

[rainman89]

look here

http://www.access-programmers.co.uk/forums/showthread.php?p=65846

Seems that a double apostraphe will work.

 

[hardhitter06]

Rainman, there is a lot of code in there and I'm not sure which one I'm trying to mimic...could you point me a step closer..

 

[rainman89]

You want to change

"[VendorName] = '" & Me.VendorName & "'

to

"[VendorName] = """ & Me.Me.VendorName & """

Or look at pat hartmans explanation on the link i sent you

 

[hardhitter06]

Did you mean Me.Me.VendorName for the change OR Me.VendorName?

Either way, I am getting a compile error: expected: list separtor or )

Answer = DLookup("[VendorName]", "tblInputNewVendor", "[VendorName] = """ & Me.Me.VendorName & """)

 

[hardhitter06]

Adding or taking away the ")" doesn't work...but that seems fairly obvious

 

[SOS]

That yellow is a bad color to use, by the way.

Anyway, this also works great:

Answer = DLookup("[VendorName]", "tblInputNewVendor", "[VendorName] = " & Chr(34) & Me.VendorName & Chr(34))

 

[hardhitter06]

SOS to the rescue...thanks buddy..that works great!

And sorry about the yellow, I went to change it to Red and I guess the edit didn't take effect.

 

[rainman89]

Sorry that was a typo.

Should be me.vendorname

This should work. (going off of pat hartmans answer)

Answer = DLookup("[VendorName]", "tblInputNewVendor", "[VendorName] = "& """" & Me.VendorName & """")