I have a query called qryAlloc_Source that has two paramaters under one criteria:
>=[forms]![frmReportingMain]![txtAllocStart] And <=[forms]![frmReportingMain]![txtAllocEnd])
A have a separate query that ultimately references qryAlloc_Source (there are a couple queries in between), and that query runs fine when I double click it in the UI, but if I try to open it in VBA, I get an error. My code is:
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("qryAlloc_Debits")
I am getting run-time error 3061, Too few parameters. Expected 2. I've read that I may need to build out the SQL in VBA using the form parameters, but it would be pretty complex SQL given that there are a few queries in the chain.
Any suggestions as to a workaround? I considered using VBA to create a table from the query and then just referencing that table–I hate to make extra steps though.
Best Answer
The reason you get the error when you just try to open the recordset is that your form is not open and when you try to access
[forms]![frmReportingMain]
it's null then you try to get a property on that null reference and things blow up. TheOpenRecordset
function has no way of poping up a dialog box to prompt for user inputs like the UI does if it gets this error.You can change your query to use parameters that are not bound to a form
Then you can use this function to get the recordset of that query.
The disadvantage to this is that when you call this now on a form that is bound to it it doesn't dynamically 'fill in the blanks' for you.
In that case you can bind forms
qryAlloc_debts
and have no where clause on the saved query, then use the formsFilter
to make your where clause. In that instance you can use your where clause exactly how you have it written.Then if you want to still open a recordset you can do it like this