comma confussion!

[Stu Dongel]

Hello all,
I have a bit of code that i execute to check for a record, then alow or
disalow the record to be edited.
it grabs the value from an unboud text box, here in lies the problem,
if there is a comma in the name that is entered, its a nongo, error
3705. i get so confused when alowing for special characters in vb, can
any one recomend a change?


thanks in advance!:
Stu

*****************************CODE********************************

Private Sub cmdAddClient_Click()

Dim varFindDup As Variant
Dim varFindRep As Variant
Dim intReplaceRep As Integer


varFindDup = DLookup("[Client]", "tblClientList", "[Client] = '"
& Me![txtAddClient] & "'")

varFindRep = DLookup("[AccountManager]", "qryFindRep", "[Client]
= '" & Me![txtAddClient] & "'")


If IsNull(varFindDup) Then

DoCmd.RunSQL "INSERT INTO tblClientList
(AccountManagerID, Client) VALUES ('" & Me.txtID & "', '" &
Me.txtAddClient & "')"
' DoCmd.Requery

Else
intReplaceRep = MsgBox("This Client Belongs to " &
varFindRep & " are you SURE you want to change it?", vbYesNo)

If intReplaceRep = vbYes Then

DoCmd.RunSQL "UPDATE tblClientList SET
[AccountManagerID] = '" & Me.txtID & "' WHERE [Client] = '" &
Me.txtAddClient & "'"

Else
MsgBox "Action Canceled"
End If

End If
End Sub
****************************END CODE***********************************

 

[Sandra Daigle]

When you say comma are you really referring to an apostrophe (')? Instead of
using the single quote or apostrophe character as the string delimeter
within your SQL try using a pair of double quote characters. For example:

varFindDup = DLookup("[Client]", "tblClientList", "[Client] = """ &
Me![txtAddClient] & """")

Access will replace each pair of quote characters with a single quote
character that will be used as the string delimiters for the comparison
value. Since the apostrophe is no longer in use a string delimiter it can be
used as part of the comparison string itself.

 

[Stu Dongel]

I did mean apostrophie! thank you so much, worked like a charm!