Help with SQL statement in VBA

[businesshippieRH]

I have a SQL statement that worked just fine until I tried to add an extra field to my append query. I can't figure out what I'm missing, but I continue to get a Error 3346: The number of query values and destination fields are not the same. I'm sure somebody with better eyes can find the missing syntax.

DoCmd.RunSQL "INSERT INTO tbl_Records ( RecordDistinction, RecordDateMONTH, RecordDateYEAR, Revision, RecordNumber, RecordName )" & _
"SELECT [Forms]![frm_NEWDateDist]![cmb_RecordDist] AS Expr1, [Forms]![frm_NEWDateDist]![txt_NewMo] AS Expr2, [Forms]![frm_NEWDateDist]![txt_NewYr] AS Expr3, 1 AS Expr4, " & NewRecNumber & " AS Expr5, " & RecName & " AS Expr6;"

RecName and NewRecNumber refer to strings created a few lines up. They work fine and have been debugged. Thanks in advance!

 

[madefemere]

Hello,

And like this :

[FONT=Arial][SIZE=2]
DoCmd.RunSQL "INSERT INTO tbl_Records ( RecordDistinction, RecordDateMONTH, RecordDateYEAR, " _
                & Revision, RecordNumber, RecordName )" & _
"SELECT" & [Forms]![frm_NEWDateDist]![cmb_RecordDist] _
 & "," [Forms]![frm_NEWDateDist]![txt_NewMo] _
& "," [Forms]![frm_NEWDateDist]![txt_NewYr] _
& "1,'" & NewRecNumber "','" & RecName & "'"

[/SIZE][/FONT]

 

[businesshippieRH]

I used that code and cleaned up the breaks a bit to make this:

DoCmd.RunSQL "INSERT INTO tbl_Records ( RecordDistinction, RecordDateMONTH, RecordDateYEAR, " & _
"Revision, RecordNumber, RecordName )" & _
"SELECT [Forms]![frm_NEWDateDist]![cmb_RecordDist], " & _
"[Forms]![frm_NEWDateDist]![txt_NewMo], " & _
"[Forms]![frm_NEWDateDist]![txt_NewYr], " & _
"1,'" & NewRecNumber & "','" & RecName & "';"

However, instead of inserting my strings I have:
" & RecName & " in the Record Name
and Null in RecordNumber.

 

[businesshippieRH]

So: This code ran correctly once and then returned to the same behavior. Any ideas?

DoCmd.RunSQL "INSERT INTO tbl_Records ( RecordDistinction, RecordDateMONTH, RecordDateYEAR, " & _
"Revision, RecordNumber, RecordName )" & _
"SELECT [Forms]![frm_NEWDateDist]![cmb_RecordDist], " & _
"[Forms]![frm_NEWDateDist]![txt_NewMo], " & _
"[Forms]![frm_NEWDateDist]![txt_NewYr], " & _
"1,'" & NewRecNumber & "',' & RecName & ';"

All I did was change to ' rather than "' on RecName.
Thanks!

 

[JHB]

Text values need to be surrounded by ', numbers not; looking at your field names I suppose:
RecordNumber is number
RecordName is text
So:

DoCmd.RunSQL "INSERT INTO tbl_Records ( RecordDistinction, RecordDateMONTH, RecordDateYEAR, " & _ 
"Revision, RecordNumber, RecordName )" & _ 
"SELECT [Forms]![frm_NEWDateDist]![cmb_RecordDist], " & _ 
"[Forms]![frm_NEWDateDist]![txt_NewMo], " & _ 
"[Forms]![frm_NEWDateDist]![txt_NewYr], " & _ 
"1," & NewRecNumber & ",'" & RecName & "';"

 

[businesshippieRH]

Works like a charm! Thanks!

 

[JHB]

You're welcome.