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

[LeoM]

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

 

[Gasman]

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.

Code Snippets

 

[cheekybuddha]

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

 

[LeoM]

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.

 

[cheekybuddha]

I tried to use Eval but not work

How did you try?

You can use a wrapper function to make it easier - see here on theDBguy's site

Shout if you need help to implement it.

 

[cheekybuddha]

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.

 

[LeoM]

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

 

[cheekybuddha]

Glad it's working for you!

Real thanks go to @theDBguy and @LPurvis for their function.