I use a function to replace the single quote with double single quotes.
In a standard module, paste:
Code:
Public Function ConvertQuotesSingle(InputVal)
ConvertQuotesSingle = Replace(InputVal, "'", "''")
End Function
Useage :
Code:
strSQL = "INSERT INTO tblAuditTrail (SessionID, SubmissionID, SessionNum, SessionTitle, FieldName, OldValue, NewValue, ChangeDate, ChangeBy) " & _
"VALUES('" & SessionID & "', '" & Submission_ID & "', '" & SessionNum & "', '" & Submission_Title & "', 'Session Title', '" & ConvertQuotesSingle(Me.Submission_Title.OldValue) & "', '" & _
ConvertQuotesSingle(Me.Submission_Title) & "', #" & Now() & "#, '" & NetworkUserName() & "');"
The nice thing about using a function is that you can use it for ANY text field, even if there are no single quotes in the data.
-------------------------------------------------------------------------------------------------------------------------
Just curious:
There are 3 variables in the VALUES clause:
, '" & Submission_Title & "',
'" & Me.Submission_Title.OldValue & "',
'" & Me.Submission_Title & "',
Are all 3 referring to the same control on the form?
There are fields named SessionID, SubmissionID and SessionNum.
Are these text fields? I ask because normally fields with "ID" or "Num" at the end of the name are numeric (numbers) not text....
As I said, I'm just curious.......