INSERT Query not compiling

[Marc Meltzer]

I have a form with six controls (text box and some combo boxes). I want
to insert their values into a table. For the combo boxes, the first
column is a number, the second column is the associated text that is
chosen on the form. I want to insert the numbers into the destination
table. Please help me figure out what my query should look like,
because it won't run as currently written.

strSQL1 = "INSERT INTO TBL_Albums (Album_Title, Format_Num, " & _
"Genre_Num, Artist_Num, Label_Num, Year_Num) " & _
"VALUES ([Forms]![FRM_Add_Album]![Album_Title], " & _
"[Forms]![FRM_Add_Album]![Album_Format.Column(0)], " & _
"[Forms]![FRM_Add_Album]![Album_Genre.Column(0)], " & _
[Forms]![FRM_Add_Album]![Album_Artist.Column(0)], " & _
[Forms]![FRM_Add_Album]![Album_Label.Column(0)], " & _
[Forms]![FRM_Add_Album]![Album_Year.Column(0)]);"

Access is telling me the bracketing is invalid. I've tried adding and
removing them in multiple locations, but nothing I try seems to work.

Thanks in advance!
Marc

 

[strive4peace]

Hi Marc

try this:

with [Forms]![FRM_Add_Album]

strSQL1 = "INSERT INTO TBL_Albums " _
& " (Album_Title, Format_Num, " _
& "Genre_Num, Artist_Num, Label_Num, Year_Num) " _
& "SELECT '" & ![Album_Title] & "', " _
& "'" & !Album_Format.Column(0) & "', " _
& "'" & !Album_Genre.Column(0) & "', " _
& "'" & !Album_Artist.Column(0) & "', " _
& "'" & !Album_Label.Column(0) & "', " _
& "'" & !Album_Year.Column(0) & "';"
end with

I am assuming that your fields are text -- if not, remove
the delimiters

also, if you are in the code behind FRM_Add_Album, you can use

Me

instead of
[Forms]![FRM_Add_Album]


this is not correct:
[Album_Format.Column(0)]

this is:
[Album_Format].Column(0)

if column 0 is the bound column, you can remove the column
reference and simply say --> [Album_Format]

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

 

[Marc Meltzer]

Crystal,

That did the trick. I might be getting the hang of this VBA stuff.

Thanks!
Marc

 

[strive4peace]

You're welcome, Marc happy to help

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com