Solved

Form Can't Paste Values with Special Characters Through SQL

Posted on 2007-03-20
4
227 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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

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.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

749 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