Error 3061 - Too Few Parameters - Expected 1 (1 Viewer)

LeoM

Member
Local time
Today, 20:52
Joined
Jun 22, 2023
Messages
56
Good morning everyone. Time to time I face with the Error 3061 Too Few Parameters Expected 1. I tried to check around some solution but it seems i'm not able to solve. Below is my code. Mainly I have a table with a list of queries name (previous build based) and for each query i need:
1. Create a new Worksheet in the Summary Error Report (previously created) with the name of the Query;
2. Add records in the new worksheet with the result of the query.
The point 1 work fine, but as soon as i tried to read the records in the query (by the query name), it give me the error.

Set dba = CurrentDb()
Set rst = dba.OpenRecordset("tbl_QC_Result", dbOpenDynaset)

rst.MoveFirst
Do Until rst.EOF

If rst!Nr_Records > 0 Then

'Create a new worksheet with name of query

Dim ws As Worksheet
Set ws = oApp.ActiveWorkbook.Sheets.Add(After:=oApp.ActiveWorkbook.Sheets(oApp.ActiveWorkbook.Sheets.Count))
ws.Name = rst!Query_File_Name

Set rst_data = dba.OpenRecordset(rst!Query_Code, dbOpenDynaset) --------------> HERE I GET THE ERROR

'Write Eather

y = 1
With rst_data
While Not .EOF
For X = 0 To .Fields.Count - 1
oApp.ActiveWorkbook.ActiveSheet.Cells(y, X + 1) = .Fields(X).Name
Next X

.MoveNext
Wend
End With

rst_data.Close
Set rst_data = Nothing

'Write the records

Set rst_data = dba.OpenRecordset(rst!Query_Code, dbOpenDynaset)
y = 2
With rst_data
While Not .EOF
For X = 0 To .Fields.Count - 1
oApp.ActiveWorkbook.ActiveSheet.Cells(y, X + 1) = .Fields(X).Value
Next X
y = y + 1
.MoveNext
Wend
End With

rst_data.Close
Set rst_data = Nothing
End If

oApp.ActiveWorkbook.ActiveSheet.Cells.EntireColumn.AutoFit

rst.MoveNext
Loop
oApp.ActiveWorkbook.Close SaveChanges:=True

Here is the SQL of the query i use (which give error) :

SELECT [05-EDW-DE].[PROJECT CODE], [05-EDW-DE].[TAG NAME], [05-EDW-DE].[PRODUCTION CRITICAL ITEM]
FROM [05-EDW-DE]
WHERE ((([05-EDW-DE].[PROJECT CODE]) Like [Forms]![frm_project_selection_for_edw_de_qc]![PRJ]) AND (([05-EDW-DE].[PRODUCTION CRITICAL ITEM])=""));

Any idea ?
Thank you in advance for kindly support.
Cheers
 
You need to concatenate the form control values in your query and not refer to them directly if using in a recordset.
Else possibly use Eval() on the control.

Form always has to be open as well of course?

Could also use @theDBguy's code.
 
So, are you sure that form frm_project_selection_for_edw_de_qc is open and that [Forms]![frm_project_selection_for_edw_de_qc]![PRJ] is not Null?

Separately, you use LIKE, but without a wildcard, so you may as well use = operator in the query
 
Thank you for the answers.
So, the form is always open because this function is called from that form trough a button and [Forms]![frm_project_selection_for_edw_de_qc]![PRJ] is never Null .
I tried to use Eval but not work, I remove Like and not work, i use '=' and not work, the only way it works is if I remove the selection from the form. So is clear the error come from there but I really can't understand why.
I would like to add that if i run this query manually, keeping open the form, it work fine but using it as recordset in the function it give me that error.
 
Last edited:
Alternatively, you can just build the required SQL in your VBA and ignore the stored query.

Again, let us know if you need help to implement that.
 
Well, implementing that function it work very good (it seems even more quick). What i did :

If rst!Nr_Records > 0 Then
'Create a new worksheet with name of query
Dim ws As Worksheet
Set ws = oApp.ActiveWorkbook.Sheets.Add(After:=oApp.ActiveWorkbook.Sheets(oApp.ActiveWorkbook.Sheets.Count))
ws.Name = rst!Query_File_Name

Set rst_data = fDAOGenericRst(rst!Query_Code) -------- USED with the suggested Function
'Set rst_data = dba.OpenRecordset(rst!Query_Code, dbOpenDynaset) ------- REMOVED
........
........

Thanks a lot for the evaluable support.
Cheers
 

Users who are viewing this thread

Back
Top Bottom