Saving user input including punctuation

[bjreb]

Hi All-

I have a form where the user enters a string into a text box. The string may contain apostrophes, quote marks and other punctuation. I then insert it into a larger SQL string to insert it into a table. It works fine when there is no punctuation, but add a quote or an apostrophe and the whole thing crashes. Is there a way to enter anything entered into the text box and have it come out as a formatted string?

Thanks

Andy

 

[plog]

Why is this not a bound form? Why are you constructing an SQL statement to do the insert?

When you do that and edit/add a string that can contain punctuation, this happens. To get around it, you must escape single and double quotes. You do this by using the Replace function (http://www.techonthenet.com/access/functions/string/replace.php). You replace ' with '' and " with "".

 

[arnelgp]

try this:

dim strSQL as String
strSQL = "Insert Into yourTable (yourFieldName) Values ('" & fnFixApostrophe(Me.yourTextBox) & "')"

Public Function fnFixApostrophe(ByVal strIn As String) As String
    On Error Resume Next
    Dim lngPos As Long
    Dim strTmp As String
    If Len(strIn) = 0 Then Exit Function
    lngPos = InStr(strIn, Chr(39))
    Do While lngPos <> 0
        strTmp = strTmp & left(strIn, lngPos) & Chr(39)
        strIn = Mid(strIn, lngPos + 1)
        lngPos = InStr(strIn, Chr(39))
    Loop
    fnFixApostrophe = strTmp & strIn
End Function