INSERT INTO Too few parameters

Question

I have a table named tb_tools, with the following fields:

TOOL_ID (Short text) [key_field]
DESCRIPTION (Short text)
RACK (Short Text)
COLUMN (Number)
COMMENTS (Long text)

In a form called frm_insertion i have a button with a click event

Private Sub btn_add_Click()
    Dim part_temp As String
    Dim tempNum As Integer
    Dim tempStr As String

    tempNum = 12
    tempStr = "test"

    tool_temp = Nz(DLookup("[TOOL_ID]", "tb_tools", "[TOOL_ID]='" & [box_dien] & "'"), "-1")

    If StrComp(tool_temp, "-1", vbTextCompare) = 0 Then
        CurrentDb.Execute "INSERT INTO [tb_dies] ([TOOL_ID], [DESCRIPTION], [RACK], [COLUMN], [COMMENTS]) " _
            & "VALUES (" & tool_temp & "," & tempStr & "," & tempStr & "," & tempNum & "," & tempStr & ")"
    End If
End Sub

I get the error "Too few parameters. Expected 1" in the INSERT INTO line. I've checked the field names. In fact, i copied and pasted them into the code directly from the table.

I've also used the code provided here: http://forums.devarticles.com/microsoft-access-development-49/check-if-a-field-exists-in-a-table-using-vba-58559.html To check if the fields are available

build your SQL string into a string then do currentdb.execute(ssql). That way you can debug.print SQL before executing it or msgbox(sSQL). My guess is one of your variable values is blank, or has special characters that need to be handled. By showing the SQL the code generates, we can back into what the problem is. Consider if your values looks like.... doesn't seem like you've got apostrophe's around your string values which would cause issue as well.. Consider using paramaterized queries to avoid the headache and avoid SQL injection issues. — xQbert

Darren Bartrup-Cook Darren Bartrup-Cook · Accepted Answer · 2018-12-11T15:55:28

This may be easier to follow when using parameters in a query.

Private Sub btn_add_Click()

    Dim qdf As DAO.QueryDef

    Set qdf = CurrentDb.CreateQueryDef("", _
        "PARAMETERS lToolID LONG, DescText TEXT (255), RackText TEXT (255), " & _
        "ColumnNum LONG, CommentText LONGTEXT; " & _
        "INSERT INTO tb_dies (TOOL_ID, DESCRIPTION, RACK, COLUMN, COMMENTS) " & _
        "VALUES (lToolID, DescText, RackText, ColumnNum, CommentText)")

    With qdf
        .Parameters("lToolID") = 1
        .Parameters("DescText") = "Some Text"
        .Parameters("RackText") = "Some Rack Text"
        .Parameters("ColumnNum") = 5
        .Parameters("CommentText") = "Some really long comments"
        .Execute
    End With

    Set qdf = Nothing

End Sub

The SQL reads as:

PARAMETERS  lToolID LONG
            , DescText TEXT (255)
            , RackText TEXT (255)
            , ColumnNum LONG
            , CommentText LONGTEXT; 
INSERT INTO tb_dies (TOOL_ID, DESCRIPTION, RACK, COLUMN, COMMENTS) 
VALUES      (lToolID, DescText, RackText, ColumnNum, CommentText);

INSERT INTO Too few parameters

2 Answers