Solved

Help with SQL Statement - Apostrophe Causing Error

Posted on 2006-06-20
12
276 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

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…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

713 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