Adding code with apostrophe

  • Thread starter Thread starter Dan B
  • Start date Start date
D

Dan B

Hi;

I have some code that adds data to a table from a
dropdown list from the "not in list" event. Looks like
this:

Private Sub Txt_Cat_NotInList(NewData As String, Response
As Integer)

Dim ctl As Control

If MsgBox("Issue Category is not in list. Add it?",
vbOKCancel) = vbOK Then
Response = acDataErrAdded
Cat_Title = Txt_Cat
CurrentDb.Execute "INSERT INTO ICategory
(Cat_Title) VALUES ('" & NewData & "')"
Else
Response = acDataErrContinue
End If

End Sub

My problem: When I try to add text that contains an
apostrophe " ' ", I get an error message which really
doesn't surprise me. How can I change the values
statement to accept text such as "Mom's apple pie"?

Thanks,
Dan
 
Try using Chr(34) to put the double quotes around your NewData value.

CurrentDb.Execute "INSERT INTO ICategory (Cat_Title) VALUES (" & Chr(34) &
NewData & Chr(34) & ")"

hth,
 
Hi:

Another way to do this is to use this function -- paste these functions into
a Module. This would work even if there are single quotes, double quotes or
both.

---------------------------------------------------------------------------
Public Function GetSQLString(ByVal strString As Variant) As String
'NN - method to prevent quote errors in SQL Statements

If IsNull(strString) Then
GetSQLString = "''"
Exit Function
End If

If InStr(1, strString, Chr(34)) <> 0 And InStr(1, strString, "'") <> 0 Then
'has single quote and double quote - big trouble
strString = "'" & DoubleApostrophe(strString) & "'"
ElseIf InStr(1, strString, "'") <> 0 Then
strString = "'" & Replace(strString, "'", "''") & "'"
Else
strString = "'" & strString & "'"
End If
GetSQLString = strString


End Function

Function DoubleApostrophe(varValue) As Variant
'generic function to convert string from say 'Rick's parlor' to 'Rick''s
parlor'
'to prevent database SQL errors

Dim intCharPosition As Integer
Dim strTemp As String

If IsNull(varValue) Then
DoubleApostrophe = Null
Exit Function
End If
strTemp = varValue
intCharPosition = InStr(1, strTemp, "'")
Do While intCharPosition > 0
strTemp = Left(strTemp, intCharPosition) & "'" & Right(strTemp,
Len(strTemp) - intCharPosition)
intCharPosition = InStr(intCharPosition + 2, strTemp, "'")
Loop
DoubleApostrophe = strTemp


End Function
------------------------------------------------------------------------

Your SQL could be like this --
CurrentDb.Execute "INSERT INTO ICategory
(Cat_Title) VALUES (" & GetSQlString(NewData) & ")"

Regards,

Naresh Nichani
Microsoft Access MVP
 
Back
Top