help with " and ' in the values (i've tried everything and i'm still lost!)

[mollycase]

hi everyone -

i am making a db that has the names of paintings. some paintings would come out Hunting Scene (generic) or specific - "The Hunter's Scene". So while I have been able to find solutions for dealing with EITHER apostrophes OR double quotes, i can't find one that will work with both. i am very much the novice at this and just doing this for fun.

i have tried using a public const QUOTE as suggested elsewhere on this forum, using the REPLACE function, using Chr(34) etc, setting Dim DQ as String and defining as Chr$(34), etc etc. i can't get anywhere.

here is the code i'm using. i got the original code from Martin Green's site which i've seen ref'd here a number of times.

this is the not-messed-with code for this field. this is exactly what i have for every other field and it works just fine (of course, no quotes or apostrophes).
---------------------------------------------------------------
Private Sub TitleID_NotInList(NewData As String, Response As Integer)
On Error GoTo TitleID_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String

intAnswer = MsgBox("The title " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Paintings Inventory")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tblTitle([Title]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

Response = acDataErrAdded
Else
MsgBox "Please choose a title from the list." _
, vbInformation, "Painting Inventory"
Response = acDataErrContinue
End If
TitleID_NotInList_Exit:
Exit Sub
TitleID_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume TitleID_NotInList_Exit
End Sub
------------------------------------------------------

thanks a bunch for any help! i've spent the last five hours on the webbie web looking for answers and going through 2 access books and one on access vba. i'd think this was a more common problem! or i'm just missing something basic

mol

 

[WayneRyan]

mol,

One way to avoid the punctuation:

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("Select * From tblTitle")

rs.AddNew
rs!Title = NewData
rs.Update

hth,
Wayne

 

[mollycase]

thanks

ok - i'll give that a try.
sometimes i feel like homer simpson as i bungle thru this coding stuff

 

[mollycase]

where exactly should i put that? i did this and now i get a syntax error (which i've been getting) about an operator missing because it sees the " in the ptg title.
------------------------------------------------------------------------
Private Sub TitleID_NotInList(NewData As String, Response As Integer)
On Error GoTo TitleID_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("Select * From tblTitle")

rs.AddNew
rs!Title = NewData
rs.Update


intAnswer = MsgBox("The title " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Paintings Inventory")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tblTitle([Title]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

Response = acDataErrAdded
Else
MsgBox "Please choose a title from the list." _
, vbInformation, "Painting Inventory"
Response = acDataErrContinue
End If
TitleID_NotInList_Exit:
Exit Sub
TitleID_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume TitleID_NotInList_Exit
End Sub

 

[WayneRyan]

Molly,

I really think this should work. Can you post your db if it doesn't.

Private Sub TitleID_NotInList(NewData As String, Response As Integer)
On Error GoTo TitleID_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("Select * From tblTitle")

intAnswer = MsgBox("The title " & Chr(34) & NewData & _
                    Chr(34) & " is not currently listed." & vbCrLf & _
                    "Would you like to add it to the list now?" _
                     , vbQuestion + vbYesNo, "Paintings Inventory")
If intAnswer = vbYes Then
   [B]rs.AddNew
   rs!Title = NewData
   rs.Update[/B]
   Response = acDataErrAdded
Else
   MsgBox "Please choose a title from the list." _
          , vbInformation, "Painting Inventory"
   Response = acDataErrContinue
End If

TitleID_NotInList_Exit:
  Exit Sub

TitleID_NotInList_Err:
  MsgBox Err.Description, vbCritical, "Error"
  Resume TitleID_NotInList_Exit
End Sub

Wayne

 

[mollycase]

hey - i can see what did wrong already!

i did not place the rs. etc stuff after then if then for the msg box. i thought it should go somewhere else than where i put it. giving it a whirl, will post back. thanks!
If intAnswer = vbYes Then
rs.AddNew
rs!Title = NewData
rs.Update

 

[mollycase]

Success!!!!!!!!!!

thank you very much!

so just to sort of make sure i get this...the code you provided works because it uses the recordset instead of the table to add the data with the punctuation? and the code i was originally using was trying to insert directly into the table causing some sort of problem with the way the VBA/Access/SQL saw it? is the recordset being used in a temporary fashion in the ether of Access thinking, sort of tricking it into seeing it differently? i realize i have not used technical terms here

i hope there's a short answer!

thanks again!

 

[WayneRyan]

Molly,

When you use SQL to add the data, you have to deal with the Parser.
SQL will want to use EITHER the single or double-quote to delimit the
data. If it's in the data, SQL will have a problem.

A recordset isn't subject to the parsing, so it doesn't matter.

You can also do something like:

DoCmd.RunSQL "Insert Into tblTitle (Title) " & _
             "Select Forms![YourForm]![YourTitle]

But, in your case, "the NewData" wasn't in a form control.

glad you got it working,
Wayne

 

[mollycase]

cool

ok, that was a short answer! easy enough.

mol