0

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?

braX
  • 11,506
  • 5
  • 20
  • 33
cjones
  • 8,384
  • 17
  • 81
  • 175
  • You `Debug.Print conoProj(1)` but variable in code is `coProj(1)`. I don't use dynamic parameterized query. I would build a form and use VBA to build filter criteria and set form Filter and FilterOn properties. Even a subform. http://allenbrowne.com/ser-62.html – June7 May 10 '19 at 22:50
  • I think need PARAMETERS clause in the query. – June7 May 10 '19 at 22:58
  • Possible duplicate of [Is it possible to pass parameters programmatically in a Microsoft Access update query?](https://stackoverflow.com/questions/16568461/is-it-possible-to-pass-parameters-programmatically-in-a-microsoft-access-update) – June7 May 10 '19 at 23:02
  • I fail to see how a question regarding update queries is a duplicate of one asking about select queries. Please actually read the question instead of rushing to say it is a duplicate and then linking to something not relevant. – cjones May 13 '19 at 16:38
  • The referenced question has answers showing how to use PARAMETERS and QueryDef in VBA, which is basis of your question. Doesn't matter if it is SELECT or UPDATE, using PARAMETERS is same. Did you bother to read answers? However, as noted in Andre's answer, the VBA is useless for query used as RecordSource, so relevance is moot. – June7 May 13 '19 at 17:56

1 Answers1

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

Andre
  • 26,751
  • 7
  • 36
  • 80
  • There isn't a `.RecordSource` for Subform/Subreport that I am seeing. Only `.SourceObject` which is why I was trying to use a Query object so I could hopefully pass in paramters. Guess I'll need to look at MS Access specific VBA methods for handling tables as opposed to SQL. – cjones May 13 '19 at 16:36
  • If you have directly embedded a query into your main form, then you should use instead a subform (in datasheet view) that is based on that query. For the subform you then can set the record source. @sockpuppet – Andre May 13 '19 at 17:05