Allow for apostrophes in select code

[noservice]

I have a bit of code that writes an audit of which controls have been
changed, what they used to be, and what they were changed to.
Everything works great until we use a word that has an apostrophe
(e.g. Timothy's). I know there is a way to modify the select string to
allow for apostrophes, but am unsure how to code it correctly. The
code is as follows.

Begin Code
----------------------------------------------------------------------------------

Public Function WriteAudit(frm As Form, lngID As Long) As Boolean
On Error GoTo err_WriteAudit

Dim ctlC As Control
Dim strSQL As String
Dim bOK As Boolean

bOK = False

DoCmd.SetWarnings False

' For each control.
For Each ctlC In frm.Controls
If TypeOf ctlC Is TextBox Or TypeOf ctlC Is ComboBox Then
If ctlC.Value <> ctlC.OldValue Or IsNull(ctlC.OldValue)
Then
If Not IsNull(ctlC.Value) Then
strSQL = "INSERT INTO tblAudit ( ClientID,
FieldChanged, FieldChangedFrom, FieldChangedTo, User, DateofHit ) " &
_
" SELECT " & lngID & " , " & _
"'" & ctlC.Name & "', " & _
"'" & ctlC.OldValue & "', " & _
"'" & ctlC.Value & "', " & _
"'" & GetUserName_TSB & "', " & _
"'" & Now & "'"
'Debug.Print strSQL
DoCmd.RunSQL strSQL
End If
End If
End If
Next ctlC

WriteAudit = bOK

exit_WriteAudit:
DoCmd.SetWarnings True
Exit Function

err_WriteAudit:
MsgBox Err.Description
Resume exit_WriteAudit

End Function


----------------------------------------------------------------------------------
End Code

Any assistance would be greatly appreciated!

Sincerely,

Michael

 

[Douglas J. Steele]

See my May, 2004 "Access Answers" column in Pinnacle Publication's "Smart
Access"

You can download the column (and sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html

 

[noservice]

See my May, 2004 "Access Answers" column in Pinnacle Publication's "Smart
Access"

You can download the column (and sample database) for free athttp://www.accessmvp.com/djsteele/SmartAccess.html

Thank you very much Mr. Steele. I will read this and apply it to my
coding. I really appreciate your help with this. Have a great
afternoon!