Trouble with the apostrophe in a text box

[evanark]

I get an error in my app when a user enters an apostrophe in text box during data entry, I use an before update event to check for duplicates and thats when the error is triggered.

Here is the code for the before update event

If DCount("*", "tblAccount", "[accSchoolName]='" & Me.accSchoolName & "' And [accSystemType]='" & Me.cboSystemType & "'") > 0 Then
    Beep
    Me.lblMessage.Caption = "School and system type combination already exist, please enter an new school name or select a different system type to continue. "
    Cancel = True
    Me!cboSystemType.Undo
Else
    Me.lblMessage.Caption = ""
End If

The text box is bound. How can I get around this issue?

Thanks in advance!

 

[pbaldy]

You can use the Chr(34) method discussed here:

http://www.baldyweb.com/BuildSQL.htm

 

[billmeye]

For the text field use
Replace(Nz(Me.cboSystemType, ""), Chr(39), Chr(39) & Chr(39))
in place of Me.cboSystemType in your Dlookup. It will take care of the single quote issue.

 

[evanark]

The code compiles but I get a run time error

If DCount("*", "tblAccount", "[accSchoolName]='" & Me.accSchoolName & "' And [accSystemType]='" & Replace(Nz(Me.cboSystemType, ""), Chr(39), Chr(39) & Chr(39)) & "'") > 0 Then

 

[billmeye]

Evan, what is the error message? Is Me.cboSystemType a text field? Try it on your other field Me.accSchoolName.

 

[evanark]

I figured it out I tried this

If DCount("*", "tblAccount", "[accSchoolName]='" & Replace(Me.accSchoolName, "'", "''") & "' And [accSystemType]='" & Me.cboSystemType & "'") > 0 Then

It accepted text with apostrophes

 

[evanark]

Bill, the cboSystemType is text field, Ill rerun the code with get the error so we can figure it out for future reference, Thank for you help! Ill try to get that error messge posted soon.

 

[billmeye]

Glad your figuring things out. I like to use Chr(39) etc. because it is easy to see as compared to "''" which can get a little tricky visually.