0

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.

kevinykuo
  • 4,600
  • 5
  • 23
  • 31
  • 1
    There is a syntax for referring to parent/child; it is somewhat complex. The master reference is http://access.mvps.org/access/forms/frm0031.htm – Smandoli Oct 08 '14 at 16:50
  • @Smandoli the link you posted seems to be VBA syntax. For the trivial example in my question I could use VBA to build a SQL string and execute it. However, in reality I have complex queries in the Access database that I want to leverage and it's not feasible to redo everything in terms of VBA. Let me know if I'm misinterpreting. – kevinykuo Oct 08 '14 at 17:03
  • Correct about the VBA context. But `SELECT Forms!Form1!text1 AS Expr1` is a mash of SQL and Object/VBA syntax. It's hard to tell what you are doing! – Smandoli Oct 08 '14 at 17:11
  • I don't know what the idiomatic way to reference forms in Access SQL is but that query runs fine (no VBA involved.) It just pulls the value from `Form1!text1`. I guess what I want is something like `Me.text1` in the query and Access can understand that `Me` is the subform object where the query is called. – kevinykuo Oct 08 '14 at 17:17
  • possible duplicate of [MS Access: How to Supply Parameters to a Query-Based Subform Control](http://stackoverflow.com/questions/4684909/ms-access-how-to-supply-parameters-to-a-query-based-subform-control) – Smandoli Oct 08 '14 at 17:18
  • @Smandoli I briefly looked at that question. Is the idea to convert my `Query1` into one that takes parameters (if that's possible) and pass the parameters I want from the text field in my subform to the query via VBA? – kevinykuo Oct 08 '14 at 17:29
  • so after some more digging I found out about the `DoCmd.SetParameter` method and it seems somewhat promising... will investigate and report back – kevinykuo Oct 08 '14 at 20:00

1 Answers1

1

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...

Community
  • 1
  • 1
kevinykuo
  • 4,600
  • 5
  • 23
  • 31