Help with SQL Statement - Apostrophe Causing Error

I am using the codebelow in the Not in Lis tEvent of a Combo Box...

it works fine if a name is like this:

ABC Insurance CompanyAble Insurance


But.....
If I type a name with an apostrophe, I get an error when it tries to Insert the record.....

like:

Charlie's Insurance Company

Is there a string replace statement, or something I can do to fix this?

Thanks - AB

see code below..


Dim strsql As String, x As Integer
    Dim LinkCriteria As String
    x = MsgBox("Do You Want to Add this Insurance Company?", vbYesNo + vbQuestion, "Add Data")
    If x = vbYes Then
        strsql = "Insert Into tblInsuranceCompanies ([InsuranceCompany]) values ('" & NewData & "')"
        'MsgBox strsql
        CurrentDb.Execute strsql, dbFailOnError
        LinkCriteria = "[InsuranceCompany] = '" & Me!cboInsCo.Text & "'"
        DoCmd.OpenForm "frmAddInsuranceCompany", , , LinkCriteria
        Forms.frmAddInsuranceCompany.txtMode = "AddFromClaims"
       
        Forms.frmAddInsuranceCompany.Address.SetFocus

        Response = acDataErrAdded
    Else
        Response = acDataErrContinue
    End If
abinbostonAsked:
Who is Participating?
 
Rick_RickardsConnect With a Mentor Commented:
'Try this.  It's failing because of the appostrophe in Me!cboInsCo.Text

'******************

    Dim strsql As String, x As Integer
    Dim LinkCriteria As String
    x = MsgBox("Do You Want to Add this Insurance Company?", vbYesNo + vbQuestion, "Add Data")
    If x = vbYes Then
        strsql = "Insert Into tblInsuranceCompanies ([InsuranceCompany]) values ('" & Replace(NewData,"'","''") & "')"      'First line that was changed
        CurrentDb.Execute strsql, dbFailOnError
        LinkCriteria = "[InsuranceCompany] = '" & Replace(Me!cboInsCo.Text,"'","''") & "'"        'Second line that was changed
        DoCmd.OpenForm "frmAddInsuranceCompany", , , LinkCriteria
        Forms.frmAddInsuranceCompany.txtMode = "AddFromClaims"      
        Forms.frmAddInsuranceCompany.Address.SetFocus
        Response = acDataErrAdded
    Else
        Response = acDataErrContinue
    End If
0
 
Harisha M GCommented:
Hi,

yoursql = Replace(yoursql, "'","''")

---
Harish
0
 
Harisha M GCommented:
abinboston, rather this:

yoursql = Replace(yoursql, "'","\'")
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Harisha M GCommented:
Dim strsql As String, x As Integer
    Dim LinkCriteria As String
    x = MsgBox("Do You Want to Add this Insurance Company?", vbYesNo + vbQuestion, "Add Data")
    If x = vbYes Then
        strsql = "Insert Into tblInsuranceCompanies ([InsuranceCompany]) values ('" & Replace(NewData, "'", "\'" & "')"
        'MsgBox strsql
        CurrentDb.Execute strsql, dbFailOnError
        LinkCriteria = "[InsuranceCompany] = '" & Me!cboInsCo.Text & "'"
        DoCmd.OpenForm "frmAddInsuranceCompany", , , LinkCriteria
        Forms.frmAddInsuranceCompany.txtMode = "AddFromClaims"
       
        Forms.frmAddInsuranceCompany.Address.SetFocus

        Response = acDataErrAdded
    Else
        Response = acDataErrContinue
    End If
0
 
abinbostonAuthor Commented:
strsql = "Insert Into tblInsuranceCompanies ([InsuranceCompany]) values ('" & Replace(NewData, "'", "\'" & "')"
   

I get errors typing this into a code module - maybe the quotes?

AB
0
 
Harisha M GCommented:
Sorry :(

Replace ' by two singlequotes:

strsql = "Insert Into tblInsuranceCompanies ([InsuranceCompany]) values ('" & Replace(NewData, "'", "''" & "')"
0
 
abinbostonAuthor Commented:
strsql = "Insert Into tblInsuranceCompanies ([InsuranceCompany]) values ('" & Replace(NewData, "'", "''" & "')"

this still does not work?

Thanks - Ab
0
 
Harisha M GCommented:
what error does it give ?

What does MsgBox print ?
0
 
Rey Obrero (Capricorn1)Commented:
try this


strsql = "Insert Into tblInsuranceCompanies ([InsuranceCompany]) values (" &  chr(34) & NewData & chr(34) & ")"
0
 
Harisha M GCommented:
What is "NewData" ? Is it a table field or any form field  or any other variable ?
0
 
Rick_RickardsCommented:
NewData would be a paraemeter passed in the NotInList Event.  The author's code appears to be run from that event as a result of an entry made into a combo box that is not found in the list of Insurance Companies Avaiable.  This if further coroborated by their use of....

  Response = acDataErrAdded
'and
  Response = acDataErrContinue

These lines are used to indicate to access what has been done so that Access does not hit the user with the default message allerting a user to the fact that the item was not found in the list....

  Response = acDataErrAdded

Rick
0
 
Jim P.Commented:
My suggestion see what happens if you use this:

Replace(NewData,CHR(39),CHR(180))
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.