INSERT Syntax Problem

[DS]

I'm inserting data into a table and it doesn't seem to be going in, I
think my Syntax is wrong....any help appreciated.

Private Sub List1_Click()
Forms!AddItems!Text22 = Me.List1.Column(0)
CurrentDb.Execute "Insert into
MenuCatIID(MenuID,MenuCatID,ItemID,PrepCatID,PrinterID,PriceLevel)"
Values " & Forms!AddItems!Text8 & ", " & Forms!AddItems!Text10 & ", " &
Forms!AddItems!Text12 & ", " & Forms!AddItems!Text20 & ", " &
Forms!AddItems!Text22 & ", " & Forms!AddItems!Text18 & """
DoCmd.Close acForm, "AddItemPrinter"
DoCmd.Close acForm, "AddItems"
Forms!MenuItems.List54.Requery
End Sub

Thanks
DS

 

[Larry]

Here is what I come up with, slightly reformatted for readability (though I
don't know how readable it will be in this newsgroup!):
Private Sub List1_Click()
Forms!AddItems!Text22 = Me.List1.Column(0)
CurrentDb.Execute "Insert into MenuCatIID
(MenuID,MenuCatID,ItemID,PrepCatID,PrinterID,PriceLevel) " & _
"Values (" & Forms!AddItems!Text8 & ", " & Forms!AddItems!Text10 & ",
" & _
Forms!AddItems!Text12 & ", " & Forms!AddItems!Text20 & ", "
& _
Forms!AddItems!Text22 & ", " & Forms!AddItems!Text18 & ")"

DoCmd.Close acForm, "AddItemPrinter"
DoCmd.Close acForm, "AddItems"
Forms!MenuItems.List54.Requery
End Sub

Basically the insert statement should be
INSERT INTO <tablename> (<columnname>,<columnname>) Values(value,value)

Right off I saw no space between the table name (which I assume is
MenuCatIID) and the first parenthesis and I did not see a parenthesis around
the data values.

Now, if your values you are inserting are text, you should put quotes around
them. In your example I would do that by using single quotes and doing the
following:
"Values ('" & Forms!AddItems!Text8 & "', '" & Forms!AddItems!Text10 & "', '"
{etc.}

If the values are dates, then you need to put number signs(#) (or hash marks
or whatever you want to call them) around the values instead of single
quotes. If your values are all numbers, then you are good to go as you are.

HTH,
Larry

 

[DS]

Here is what I come up with, slightly reformatted for readability (though I
don't know how readable it will be in this newsgroup!):
Private Sub List1_Click()
Forms!AddItems!Text22 = Me.List1.Column(0)
CurrentDb.Execute "Insert into MenuCatIID
(MenuID,MenuCatID,ItemID,PrepCatID,PrinterID,PriceLevel) " & _
"Values (" & Forms!AddItems!Text8 & ", " & Forms!AddItems!Text10 & ",
" & _
Forms!AddItems!Text12 & ", " & Forms!AddItems!Text20 & ", "
& _
Forms!AddItems!Text22 & ", " & Forms!AddItems!Text18 & ")"

DoCmd.Close acForm, "AddItemPrinter"
DoCmd.Close acForm, "AddItems"
Forms!MenuItems.List54.Requery
End Sub

Basically the insert statement should be
INSERT INTO <tablename> (<columnname>,<columnname>) Values(value,value)

Right off I saw no space between the table name (which I assume is
MenuCatIID) and the first parenthesis and I did not see a parenthesis around
the data values.

Now, if your values you are inserting are text, you should put quotes around
them. In your example I would do that by using single quotes and doing the
following:
"Values ('" & Forms!AddItems!Text8 & "', '" & Forms!AddItems!Text10 & "', '"
{etc.}

If the values are dates, then you need to put number signs(#) (or hash marks
or whatever you want to call them) around the values instead of single
quotes. If your values are all numbers, then you are good to go as you are.

HTH,
Larry

:

Thanks for the Primer, it helped alot. I'm up and running!
DS