CODEWITHSUNDEEP
vbams-access
kevinykuo
kevinykuo

Reputation: 4762

Dependent queries and referencing subform controls

The situation

My database contains several queries and a form, let's call them Query1a, Query1b, and Query2, and Form1.

Form1 contains controls Text1, ComboBox1, and a button Command1.

Queries 1a and 1b reference controls on Form1 explicitly, e.g. via a SELECT Forms!Form1!Text1 as Expr1. Query2 depends on Queries 1a and 1b.

Command1 runs DoCmd.OpenQuery "Query2" on click, and everything works fine.

The Problem

I have various forms with structures similar to Form1, and I'd like to put them all in a navigation tab. I go to create -> navigation -> horizontal tabs and drag Form1 to a tab.

Now, when I try to use the Form1 in the navigation form, Access pops up and asks me for the value of Text1, because Query1a is trying to look for it in Forms!Form1 but my Text1 really lives in Forms!NavigationForm!Form1.Form.

Attempts

I could go into each of the queries and change the code to explicitly reference the subform, but that's tedious, not very elegant, and it would break the ability to use the form outside of the navigation form.

Inspired by the comments, I also tried the following:

  1. Add a PARAMETERS declaration to Query1a and Query1b
  2. In my button in Form1, I set the parameters programmatically via QueryDef. This way, I can do something like Dim qdf As DAO.QueryDef Set qdf = CurrentDb.QueryDefs("Query1a") qdf.Parameters("Forms!Form1!myparam").Value = Me!Text1

However, I don't know where to go from there, as my QueryDef object is invisible to Query2.

One thing that would make life easier is to have the queries be able to reference the form object from where they are called, but I don't know if that's possible.

Upvotes: 0

Views: 277

Answers (1)

kevinykuo
kevinykuo

Reputation: 4762

Ended up doing this via parameter queries, and passing controls values via QueryDef to Query2's parameters. This works because of the comment by @VBlades in Pass parameter to a query from another query in Access

QueryDef objects in Access can see the Parameters of the queries they are built on. Say Query2 is the query that sits on top of GET_CUSTOMER; you can write code similar to this: QueryDefs("Query2").Parameters("customer_id") = 123, even though the param does not belong to the top level. So the params of the underlying query can therefore be accessed by the higher-level ones (off the top of my head, I don't know what happens if there are identically named parameters in the stack of queries; I think they are all given the same value once the param is set). So yes, it can be done.

In other words, any parameters I pass to a query also gets seen by all queries dependent on it. I don't know what happens if there are name clashes.

So I guess my OP is a dupe but it took me a while to realize that...

Upvotes: 1

Related Questions