Reading apostrophes and quotation marks into strings

[alexmetcalf]

Howdy all,

I need to get the values of certain fields into string variables in my code. Problem is, some of these fields are text or memo and may have apostrophes and quotation marks in them. This always gives VBA fits, so I've been using a find&replace on the tables to make the apostrophes commas for a while. Is there some way to handle them more elegantly? Something that will allow me to leave apostrophes in my data? Thanks in advance.

[Norie]

How and why are you putting these values into strings in VBA?

[alexmetcalf]

The database holds and manages questions being written for a scholastic trivia competition. I'm very new at both Access and VBA, so my methods may not be the most elegant. One place where they read into string values is in building an SQL statement. For instance, the following block of code appends the contents of a recordset to a table. My problem arises when the variables I store the values in contain apostrophes or quotation marks, VBA thinks my SQL statement ends. This may well not be the best way to do this, but it works, so long as there are no apostrophes. I'd appreciate any advice on making it work all the time, apostrophes or not. Thanks again.


While Not SciRecordSet.EOF

'Get values
ID = SciRecordSet.Fields("ID").Value
Topic = "'" + SciRecordSet.Fields("Topic").Value + "'"
Question = "'" + SciRecordSet.Fields("Question").Value + "'"
Answer = "'" + SciRecordSet.Fields("Answer").Value + "'"
If (SciRecordSet.Fields("Supplemental").ActualSize > 0) Then
Supplemental = ", '" + SciRecordSet.Fields("Supplemental").Value + "'"
Else
Supplemental = ", ' '"
End If

'Build SQL statement.
SciAppSQL = "INSERT INTO " + NameTable
SciAppSQL = SciAppSQL + " (ID, Topic, Question, Answer, Supplemental)"
SciAppSQL = SciAppSQL + " VALUES ( " + CStr(ID) + ", " + Topic + ", " + _
Question + ", " + Answer + Supplemental + ")"

DoCmd.RunSQL SciAppSQL

'Go to next record in recordset
SciRecordSet.MoveNext
Wend