Solved

Help with SQL Statement - Apostrophe Causing Error

Posted on 2006-06-20
12
274 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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 350 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
hit enter key to run macro 13 23
Can not open the Access Help ? 3 53
DSN-LESS connection to MS Access database 6 29
Dirty form - conditional formatting 5 17
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

785 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