Passing parameters to query with VBA in Access and getting ""

Question

Doesn't seem like I can just build the query in VBA which I usually do.

I have a form where users pick parameters from lists and the such. They click the "Submit" button which should filter down a table so that they can make changes on just those records. That table is embedded in the same form at the bottom of the form. It is blank until they set the parameters and submit.

Because it is a table in the form, and is a Subform/Subreport, from what I've read the only way to populate it is with prebuilt Access query. Thus, I need to pass in variables to make it dynamic.

So the query numberSections is such:

SELECT title, group, group_num
FROM groupings
WHERE co = [co] AND project = [project]
ORDER BY ID;

It is my understanding that the brackets indicate a variable.

The VBA is such:

Function RunQueryForGroupings(coProj As Collection)

    Dim qdf As DAO.QueryDef
    Set qdf = CurrentDb.QueryDefs("numberSections")
    qdf.Parameters("co").Value = coProj(1)
    qdf.Parameters("project").Value = coProj(2)
    qdf.Execute
    Set qdf = Nothing

End Function

When it gets to qdf.Parameters("co").Value = coProj(1) I get the following error:

Run-time error '3265':

Item not found in this collection.

At first, I thought coProj(1) did not have a value for some reason, but it does because I can Debug.Print(coProj(1)), so it has to do with the declaration.

Any suggestions?

Answer 1

Setting parameters to a querydef will do you no good for a SELECT query that is the record source of a form. It would only be useful for opening a recordset.

qdf.Execute is only valid for action queries (INSERT, UPDATE, DELETE).

But it doesn't matter - if you want to dynamically fill a subform, just address it like this:

Me.mySubformControl.Form.RecordSource = strDynamicSQL

Forms: Refer to Form and Subform properties and controls