Form Can't Paste Values with Special Characters Through SQL

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
LVL 2
JeffGMACIAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
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
 
PapaLoraxCommented:
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
 
JeffGMACIAuthor Commented:
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
 
JeffGMACIAuthor Commented:
Oh sweet, that worked perfectly.  I guess the chr(34) did the trick.  Why does it work that way?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.