Hey, I am using VBA codes to insert data into the tables, however I found out one problem......when I try to insert ('), the code returns error.......but for quotation (") is fine.......my code is as follows:


sql = "INSERT INTO M_Stock (StockRef, StockDesc) VALUES ('" & txtStockRef & "', '" & txtStockDesc & "');"
set dbs = currentdb
dbs.execute sql


How do I change the code so that it will accept any symbol?? Thanks so much

any help would be highly appreciated. Thank you.

The problem is that it's thinking your single quote is ending/begining a string.

sql = "INSERT INTO M_Stock (StockRef, StockDesc) VALUES ('" & ReturnValue(txtStockRef) & "', '" & ReturnValue(txtStockDesc) & "');"
Set dbs = CurrentDb
dbs.Execute SQL

Public Function ReturnValue (varValue As Variant) As Variant
Select Case VarType(varValue)
Case vbString
ReturnValue = Replace(varValue,"'","''") 'Replace the single quote with two single quotes: this w/o spaces => " ' ' "
Case Else
ReturnValue = varValue
End Select
End Function

This should work, I just typed it on the go, so the variable types may not work out, but I think it will work. I'll test it when I get to work tomorrow.