Insert into syntax error (1 Viewer)

[ClaraBarton]

2 days I've stared at this:

Private Sub GroceryListTable()
Dim db As DAO.Database
Dim rst As Recordset
Dim frm As Form
Dim strSQL As String
Set db = CurrentDb
Set frm = Me!frmCalendarWeek.Form!fsubGrocery.Form
Set rst = frm.RecordsetClone
strSQL = "INSERT INTO tempGroceryList (PrefixII, Location, GroceryName) " & _
        "VALUES (" & rst![PrefixII] & "," & rst![Location] & "," & rst![GroceryName] & ")"

CurrentDb.Execute "DELETE FROM tempGroceryList"
 
 rst.MoveFirst
        While Not rst.EOF
            CurrentDb.Execute strSQL, dbFailOnError
rst.MoveNext
Wend

Debug returns:
INSERT INTO tempGroceryList (PrefixII, Location, GroceryName) VALUES (F-1,Meat,hamburger)
The error is: too few parameters; expected 3
Or:
INSERT INTO tempGroceryList (PrefixII, Location, GroceryName) VALUES (F-1,Meat,flank steak)
syntax error. Missing operator in flank steak
Can you tell me what I'm missing?

 

[June7]

Need apostrophe delimiters for text field parameter.

strSQL = "INSERT INTO tempGroceryList (PrefixII, Location, GroceryName) " & _
        "VALUES ('" & rst![PrefixII] & "','" & rst![Location] & "','" & rst![GroceryName] & "')"

Date/time field parameter needs # as delimiter.

 

[ClaraBarton]

Thank you so much... Now I'm getting a table full of the same record... It doesn't movenext

 

[Minty]

Your strSQL needs to be inside your recordset loop for that to work unless you are only returning one record.

You aren't changing it when you move through the recordset.

 

[ClaraBarton]

rst.MoveFirst
 strSQL = "INSERT INTO tempGroceryList (PrefixII, Location, GroceryName) " & _
        "VALUES ('" & rst![PrefixII] & "','" & rst![Location] & "','" & rst![GroceryName] & "')"
        While Not rst.EOF
            CurrentDb.Execute strSQL, dbFailOnError
rst.MoveNext
Wend

???? How do I do that?

 

[ClaraBarton]

rst.MoveFirst
        While Not rst.EOF
         strSQL = "INSERT INTO tempGroceryList (PrefixII, Location, GroceryName) " & _
        "VALUES ('" & rst![PrefixII] & "','" & rst![Location] & "','" & rst![GroceryName] & "')"
    CurrentDb.Execute strSQL, dbFailOnError
rst.MoveNext
Wend

IT WORKS!!! Thank you so much!

 

[Minty]

You are welcome, good luck!

 

[Josef P.]

You could also think about replacing the loop with a single SQL statement.

strSQL = "INSERT INTO tempGroceryList (PrefixII, Location, GroceryName) " & _
        "select PrefixII, Location, GroceryName from <Source Of Me!frmCalendarWeek.Form!fsubGrocery.Form>"

 

[ClaraBarton]

Like a recordsource instead of a recordset?