Special Characters in INSERT INTO query 1

[meirfridman]

Hallo all,

I am busy building a database with a table of log records recording any changes made to the foundation data.
This is the code i use:

CurrentDb.Execute "INSERT INTO tblLogRecords (fldLoginId, fldTime, fldAction, fldDescription) VALUES (" & LoginId & " , Now() , 'Changed type name' , 'In category " & CategoryName & ":" & vbCrLf & "From " & OLdData & " To " & Nz(Me.TypeName, "Empty") & " ' )"

The problem is, if the user inserts special characters I get error 3075.
Is there any way i can force the program not treat data entry as source code?

Thanks

 

[Andrzejek]

What type of special characters?
If only [tt]'[/tt] is the problem, you can do this:

CurrentDb.Execute "INSERT INTO tblLogRecords (fldLoginId, fldTime, fldAction, fldDescription) VALUES (" & LoginId & " , Now() , 'Changed type name' , 'In category " & CategoryName & ":" & vbCrLf & "From " & OLdData & " To " & Nz([blue]Replace([/blue]Me.TypeName[blue], "'", "''")[/blue], "Empty") & " ' )"

Have fun.

---- Andy

 

[meirfridman]

Thank you Andy.
I checked the rest of the characters and it seems that only the ' causes problems.
I hope the problem is solved.

 

[Andrzejek]

Make sure in other places (CategoryName, OLdData) if there could be ', replace it with 2 of them.

Glad to help

Have fun.

---- Andy

 

[meirfridman]

Thanks for the tip.
I tried but it seems that it have to be writen like this:

NewData = Replace (Nz (Me.TypeName, "Empty"), "'", "''")

Otherwise if the update(TypeName) is null, the function (Replase) returns an error.