Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Help with SQL Statement - Apostrophe Causing Error

Posted on 2006-06-20
12
Medium Priority
?
283 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:abinboston
  • 6
  • 2
  • 2
  • +2
12 Comments
 
LVL 37

Expert Comment

by:Harisha M G
ID: 16947378
Hi,

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

---
Harish
0
 
LVL 37

Expert Comment

by:Harisha M G
ID: 16947383
abinboston, rather this:

yoursql = Replace(yoursql, "'","\'")
0
 
LVL 37

Expert Comment

by:Harisha M G
ID: 16947394
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:abinboston
ID: 16947435
strsql = "Insert Into tblInsuranceCompanies ([InsuranceCompany]) values ('" & Replace(NewData, "'", "\'" & "')"
   

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

AB
0
 
LVL 37

Expert Comment

by:Harisha M G
ID: 16947453
Sorry :(

Replace ' by two singlequotes:

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

Author Comment

by:abinboston
ID: 16947475
strsql = "Insert Into tblInsuranceCompanies ([InsuranceCompany]) values ('" & Replace(NewData, "'", "''" & "')"

this still does not work?

Thanks - Ab
0
 
LVL 37

Expert Comment

by:Harisha M G
ID: 16947517
what error does it give ?

What does MsgBox print ?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 16947593
try this


strsql = "Insert Into tblInsuranceCompanies ([InsuranceCompany]) values (" &  chr(34) & NewData & chr(34) & ")"
0
 
LVL 16

Accepted Solution

by:
Rick_Rickards earned 1400 total points
ID: 16947599
'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
 
LVL 37

Expert Comment

by:Harisha M G
ID: 16947605
What is "NewData" ? Is it a table field or any form field  or any other variable ?
0
 
LVL 16

Expert Comment

by:Rick_Rickards
ID: 16947625
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
 
LVL 38

Expert Comment

by:Jim P.
ID: 16955255
My suggestion see what happens if you use this:

Replace(NewData,CHR(39),CHR(180))
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question