Solved

Help with SQL Statement - Apostrophe Causing Error

Posted on 2006-06-20
12
277 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
vb.net dbnull syntax 1 58
Access lists formating 8 50
microsoft Access form help 5 15
how to get hundreds part from the number 1 31
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

734 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