Solved

Form Can't Paste Values with Special Characters Through SQL

Posted on 2007-03-20
4
226 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
  • 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 500 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

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.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

856 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