Insert symbols into table

[klwu]

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

 

[klwu]

any help would be highly appreciated. Thank you.

 

[modest]

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.