Solved

Form Can't Paste Values with Special Characters Through SQL

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

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 …
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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…

695 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