In a form, I'm using the "Form_BeforeUpdate" event to update a "history table" before changes are made to the record. I'm using the strSQL command as shown below (***s)
strSQL = "Insert into tbl02PrioritizationDataHistory (TrackingNumber, Division, Manager, ManagerCode) " & _
"VALUES ('" & Me!TrackingNumber & "', '" & Me!Division & "', '" & Me!Manager & "', '" & Me!ManagerCode & "')"
CurrentDb().Execute strSQL, dbFailOnError
Note: With the exception of TrackingNumber, the other 3 fields (Division, Manager, ManagerCode) are all text fields.
This process works fine... However, I realize that entering a >>> ' <<< somewhere in the text field (e.g. "This year's budget") will throw a syntax error.
The exact error reads: Run-Time Error 3075. Syntax error (Missing Operator) in query expression....
I understand as to why this error is caused... unfortunately, I'm not sure how to fix it. Is there a chance to still use the single apostrophe in the textfield w/o throwing the syntax error? If not, is there an alternative solution? I'd rather not make the >>> ' <<< an "invalid" character.... users will want to use that.