Run-time error ‘3061’: Too few parameters expected 1

0
votes

i had this error when i was trying to INSERT records to a local table with VBA.

I have checked the data type and putting in the quotes for the the short text data type but it doesn't work.

    table_newid = "SELECT Cint(t1." & id_name(i) & "_new), " & Replace(select_column_str, local_table_name, "t2") & " FROM " & vbCrLf & _
                        "(SELECT CInt(DCount(""[" & id_name(i) & "]"", """ & qry_distinct_id_name & """, ""[" & id_name(i) & "]<="" & [" & id_name(i) & "])) as " & id_name(i) & "_new, * FROM " & qry_distinct_id_name & ") AS t1 " & vbCrLf & _
                        "LEFT JOIN " & local_table_name & "_ALL as t2 " & vbCrLf & _
                        "ON t1." & id_name(i) & " = t2." & id_name(i) & " " & vbCrLf & _
                        "WHERE t2.database = '" & database_name & "'"

    strQuery = "INSERT INTO " & local_table_name & "_temp (" & temp_field(i) & ", " & Replace(select_column_str, local_table_name & ".", "") & ") " & vbCrLf & table_newid

    Debug.Print strQuery
    DoCmd.SetWarnings False
    db.Execute strQuery
    DoCmd.SetWarnings True

From the debug.print, i have got:

INSERT INTO TblLUMachineTypes_temp (MachTypeID_new, MachTypeID, MachTypeCode, MachTypeMod, MachTypeDesc, MachTypeDisc, NewCode, Approved, mttime, CreatedBy, CreatedTS, ModifiedBy, ModifiedTS) 
SELECT t1.MachTypeID_new, t2.MachTypeID, t2.MachTypeCode, t2.MachTypeMod, t2.MachTypeDesc, t2.MachTypeDisc, t2.NewCode, t2.Approved, t2.mttime, t2.CreatedBy, t2.CreatedTS, t2.ModifiedBy, t2.ModifiedTS FROM 
(SELECT CInt(DCount("[MachTypeID]", "qry_TblLUMachineTypes_id_distinct", "[MachTypeID]<=" & [MachTypeID])) as MachTypeID_new, * FROM qry_TblLUMachineTypes_id_distinct) AS t1 
LEFT JOIN TblLUMachineTypes_ALL as t2 
ON t1.MachTypeID = t2.MachTypeID 
WHERE t2.database = 'CPM-252-2'

When i copied this query and execute it manually, it works fine but not with VBA. Any idea?

Thanks in advance.

2
vbams-accessappendruntime-error

2 Answers

0
votes

Remove all the & vbCrLf from your code, they are not necessary and I assume they corrupt the SQL syntax.

0
votes

I found the problem. I found that qry_distinct_id_name query table has a Dlookup function in there that returns a string value, which will work when executing the query manual but doesn't work when you run it with VBA. So I have re-written the code to put in the quote before and after dlookup() function.