problem with "Insert Into"

[Jerry Natkin]

I'm having a peculiar problem. I'm inputting, via an InputBox, a string
"strNewTheme" to be inserted into a table named "Themes", via the SQL"

DoCmd.RunSQL " INSERT INTO Themes " & "(Theme) VALUES " & "(" &
strNewTheme & ");"

I guess my syntax is messed up, since Access interprets the value I put in
as a parameter, and comes back with a box asking me for the value of the
value I put in (see attached jpg).

Can someone clarify this?

Thanks,

Jerry

 

[Douglas J. Steele]

Is Theme a text field? If so, you need quotes around the value you're trying
to insert:

DoCmd.RunSQL "INSERT INTO Themes (Theme) VALUES (""" & _
strNewTheme & """)"

That's three double-quotes in front, and three double-quotes after. Note
that you will have problems if strNewTheme contains a double quote. If
there's a chance that you might have double quotes, but never have single
quotes, you can use

DoCmd.RunSQL "INSERT INTO Themes (Theme) VALUES ('" & _
strNewTheme & "')"

Exagerated for clarity, that's


DoCmd.RunSQL "INSERT INTO Themes (Theme) VALUES ( ' " & _
strNewTheme & " ' )"

If there's a chance of both double and single quotes, use

DoCmd.RunSQL "INSERT INTO Themes (Theme) VALUES ('" & _
Replace(strNewTheme, "'", "''") & "')"

Exagerated again, that's

DoCmd.RunSQL "INSERT INTO Themes (Theme) VALUES ( ' " & _
Replace(strNewTheme, " ' ", " ' ' ") & " ' )"

 

[Jerry Natkin]

Is Theme a text field? If so, you need quotes around the value you're
trying to insert:

DoCmd.RunSQL "INSERT INTO Themes (Theme) VALUES (""" & _
strNewTheme & """)"

That's three double-quotes in front, and three double-quotes after.
Note that you will have problems if strNewTheme contains a double
quote. If there's a chance that you might have double quotes, but
never have single quotes, you can use

DoCmd.RunSQL "INSERT INTO Themes (Theme) VALUES ('" & _
strNewTheme & "')"

Exagerated for clarity, that's


DoCmd.RunSQL "INSERT INTO Themes (Theme) VALUES ( ' " & _
strNewTheme & " ' )"

If there's a chance of both double and single quotes, use

DoCmd.RunSQL "INSERT INTO Themes (Theme) VALUES ('" & _
Replace(strNewTheme, "'", "''") & "')"

Exagerated again, that's

DoCmd.RunSQL "INSERT INTO Themes (Theme) VALUES ( ' " & _
Replace(strNewTheme, " ' ", " ' ' ") & " ' )"

That worked perfectly. I don't anticipate anyone putting quotation marks
in this field, but of course it's what we don't anticipate that kills.

Thanks a lot.
Jerry