Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Form Can't Paste Values with Special Characters Through SQL

Posted on 2007-03-20
4
Medium Priority
?
237 Views
Last Modified: 2012-05-05
Hey guys, I have an access database.  There is a form that users use to input data.  A SQL query then pastes the data from the form into a table.  Unfortunately, if the user inputs parenthesis, single quotes, and other special characters, the SQL gets confused.  Can I somehow reformat my SQL statement to take these values and allow the entry of special characters?  The "Me.cboDescription.Value" is what can't handle the single quotes and other things.  It's not a huge deal, but users are getting debug errors when they use quotes or parenthesis and don't understand why.


If MsgBox("Are you sure you want to add this information?", vbOKCancel) = vbOK Then
        AppSQL = "INSERT INTO tblNonRateStep (project_id, step_num, step_desc, step_owner, step_date, step_status) VALUES ('" & Me.id.Value & "','" & Me.cboStep.Value & "','" & Me.cboDescription.Value & "','" & Me.cboOwner.Value & "','" & Me.cboDate.Value & "','" & Me.cboStatus.Value & "')"
End If
0
Comment
Question by:JeffGMACI
[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
  • 2
4 Comments
 
LVL 2

Expert Comment

by:PapaLorax
ID: 18759642
Are you specifically doing this to avoid having bound forms?

If you are interested i have VBA code that can parse the bad characters out - but I am not aware of a way to push that back through the string with an INSERT command.

You could switch to using recordsets and then run an update with the recordset -- that would allow you to continue with unbound forms. Do you know how to do that?
0
 
LVL 2

Author Comment

by:JeffGMACI
ID: 18759664
Yeah, I needed to keep the form unbound because it's populating a subform that needs to refresh a certain way.  

I'm not sure how to run the update with a recordset.  is it very difficult to do?  If so I can just use the parse code.  I don't think it's a big issue, but people were confused trying to input apostrophes for possessives (like "Chris's" for example).
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 18759693
try this

AppSQL = "INSERT INTO tblNonRateStep (project_id, step_num, step_desc, step_owner, step_date, step_status) VALUES ('" & Me.id.Value & "','" & Me.cboStep.Value & "'," & chr(34) & Me.cboDescription.Value & Chr(34) & ",'" & Me.cboOwner.Value & "','" & Me.cboDate.Value & "','" & Me.cboStatus.Value & "')"
0
 
LVL 2

Author Comment

by:JeffGMACI
ID: 18759717
Oh sweet, that worked perfectly.  I guess the chr(34) did the trick.  Why does it work that way?
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

610 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