comma confussion!

  • Thread starter Thread starter Stu Dongel
  • Start date Start date
S

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***********************************
 
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top