SQL "insert into" code not working

[Mark Kubicki]

I'm trying to add some code that would add a record to a table
[tblInstallationNotes] based on the selection of a combobox
[cmbInstallationNoteSelection]

the fields to be added in the destination table [tblInstallationNotes] are:
PrintInstallationNote - Yes/No
BaseInstallationNote - Yes/No
InstallationNote - memo
Type - text

in source table [OptInstallationNotes] the fileds are:
OptionNumber - AutoNumber
InstallationNoteTitle - Text
Options - Memo

the Field [Type]is filled with the current entry on the form [Spec] in the
field [Type] (which is text)


of course, it's not working; would someone take a look over my shoulder -i
may be missing the obvious
-------------------------------------------------
Dim sSQL As String

sSQL = "INSERT INTO tblInstallationNotes (PrintInstallationNote,
BaseInstallationNote, InstallationNote, Type) " & _
"SELECT True, False, Options, '" & Forms![Spec].[Type] & _
"', from OptInstallationNotes where OptionNumber = " &
[cmbInstallationNoteSelection] & ";"

DoCmd.SetWarnings False
CurrentDb.Execute (sSQL), dbFailOnError
DoCmd.SetWarnings True

 

[Douglas J. Steele]

Get rid of the comma in front of " from OptInstallationNotes "

Note that Type is a reserved word, so you really should rename that field.
(For a comprehensive list of names to avoid, as well as a link to a free
utility to check your application for compliance, see what Allen Browne has
at http://www.allenbrowne.com/AppIssueBadWord.html )

 

[Marshall Barton]

I'm trying to add some code that would add a record to a table
[tblInstallationNotes] based on the selection of a combobox
[cmbInstallationNoteSelection]

the fields to be added in the destination table [tblInstallationNotes] are:
PrintInstallationNote - Yes/No
BaseInstallationNote - Yes/No
InstallationNote - memo
Type - text

in source table [OptInstallationNotes] the fileds are:
OptionNumber - AutoNumber
InstallationNoteTitle - Text
Options - Memo

the Field [Type]is filled with the current entry on the form [Spec] in the
field [Type] (which is text)


of course, it's not working; would someone take a look over my shoulder -i
may be missing the obvious
-------------------------------------------------
Dim sSQL As String

sSQL = "INSERT INTO tblInstallationNotes (PrintInstallationNote,
BaseInstallationNote, InstallationNote, Type) " & _
"SELECT True, False, Options, '" & Forms![Spec].[Type] & _
"', from OptInstallationNotes where OptionNumber = " &
[cmbInstallationNoteSelection] & ";"

DoCmd.SetWarnings False
CurrentDb.Execute (sSQL), dbFailOnError
DoCmd.SetWarnings True

On the surface, it looks ok to me. Double check the combo
box's BoundColumn to make sure it corresponds to the
OptionNumber field in its RowSource table/query.

If there is any chance that the Type (a poor choince for a
name) can have an apostrophe, use doubled double quotes
instead.

The Execute method never generates an unnecessary warning so
get rid of the Set Warnings. They are a waste of time and
may hide a useful clue about why it doesn't work.

Instead of telling us that "it's not working", it usually
helps if you tell us what actually happened.