Solved Error 3061 too few parameters

[ClaraBarton]

When running this code I get too few parameters:

Public Function AddIngredients()
Dim dbs As DAO.Database
Dim rst As Recordset
Dim SQL As String
Dim intID As Long
Dim SQL2 As String

SQL = "SELECT t_recipeingredient.ingredienttext, t_recipeingredient.ingredientid " & _
            "FROM t_recipeingredient " & _
            "WHERE (((t_recipeingredient.ingredientid)=0))"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(SQL, dbOpenSnapshot)
intID = DMax("ingredientid", "t_ingredient") + 1

    Do While Not rst.EOF
        SQL2 = "INSERT INTO t_ingredient (ingredientid, name) VALUES (" & intID & ", " & "ingredienttext" & ")"

            dbs.Execute SQL2, dbFailOnError

        rst.MoveNext

    Loop

    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
        
End Function

The immediate window returns:
INSERT INTO t_ingredient (ingredientid, name) VALUES (55339, ingredienttext)
which looks right to me.

 

[theDBguy]

You need to add string delimiters to ingredienttext.

 

[ClaraBarton]

Aargh! gets me every time. Thank you so much.

 

[theDBguy]

Aargh! gets me every time. Thank you so much.

You're welcome. Good luck with your project.

 

[ClaraBarton]

You're welcome. Good luck with your project.

Oh wait! that isn't what I want. I'm trying to insert the field from the recordset called t_recipeingredient.ingredienttext. not the literal text.
Also I'm not incrementing the id number right.

 

[theDBguy]

Oh wait! that isn't what I want. I'm trying to insert the field from the recordset called t_recipeingredient.ingredienttext. not the literal text.
Also I'm not incrementing the id number right.

Okay, one problem at a time. How's this?

SQL2 = "INSERT INTO t_ingredient (ingredientid, name) VALUES (" & intID & ", '" & ingredienttext & "')"

 

[ClaraBarton]

Then I get "variable not defined" on ingredienttext. It's a field from the rst.

 

[theDBguy]

Then I get "variable not defined" on ingredienttext. It's a field from the rst.

Oh, then try adding rst! in front of it.

rst!ingredienttext

 

[Gasman]

When running this code I get too few parameters:

Public Function AddIngredients()
Dim dbs As DAO.Database
Dim rst As Recordset
Dim SQL As String
Dim intID As Long
Dim SQL2 As String

SQL = "SELECT t_recipeingredient.ingredienttext, t_recipeingredient.ingredientid " & _
            "FROM t_recipeingredient " & _
            "WHERE (((t_recipeingredient.ingredientid)=0))"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(SQL, dbOpenSnapshot)
intID = DMax("ingredientid", "t_ingredient") + 1

    Do While Not rst.EOF
        SQL2 = "INSERT INTO t_ingredient (ingredientid, name) VALUES (" & intID & ", " & "ingredienttext" & ")"

            dbs.Execute SQL2, dbFailOnError

        rst.MoveNext

    Loop

    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
       
End Function

The immediate window returns:
INSERT INTO t_ingredient (ingredientid, name) VALUES (55339, ingredienttext)
which looks right to me.

Shouldn't be ingredienttext though should it?, but the value of ingredienttext.

 

[ClaraBarton]

why is this so hard?
SQL2 = "INSERT INTO t_ingredient (ingredientid, name) VALUES (" & intID & ", '" & rst.ingredienttext & "')" 'method or data member not found
SQL2 = "INSERT INTO t_ingredient (ingredientid, name) VALUES (" & intID + 1 & ", " & "rst.ingredienttext" & ")" '3061

 

[theDBguy]

why is this so hard?
SQL2 = "INSERT INTO t_ingredient (ingredientid, name) VALUES (" & intID & ", '" & rst.ingredienttext & "')" 'method or data member not found
SQL2 = "INSERT INTO t_ingredient (ingredientid, name) VALUES (" & intID + 1 & ", " & "rst.ingredienttext" & ")" '3061

But I told you to use an exclamation point (!), not a period.

 

[ClaraBarton]

But I told you to use an exclamation point (!), not a period.

Ah! Good call, DBguy! Now, why won't my id's increment?

 

[theDBguy]

Ah! Good call, DBguy! Now, why won't my id's increment?

What is the current id now?

 

[ClaraBarton]

I've given up. I redid both tables and added an autonumber and updated them both with the query window. Thank you for your help.

 

[theDBguy]

I've given up. I redid both tables and added an autonumber and updated them both with the query window. Thank you for your help.

Glad to hear you got it sorted out. Cheers!