One Form, Multiple Query Sources (but not at the same time)

[MojaveJim]

Greetings!

The setup:

tbl_MAIN has all my data in it.

frm_MAIN MENU allows user to search based on five fields, and opens another form: frm_DETAIL which displays all the tbl_MAIN data on one screen. It's useful but it's kinda crazy and busy. It's good, I like it.

frm_MAIN MENU also opens up other TABULAR forms that display a subset of the tbl_MAIN data, based on user name.

The Problem:

I want to be able to click on a button when in one of these other TABULAR forms, and display that line of data in the form frm_DETAIL. I can enter a command button to open a form, but frm_DETAIL is linked to a query, which is linked to frm_MAIN_MENU. So it ignores the data I'm looking at and just uses whatever state frm_MAIN _MENU is in.

These TABULAR forms only need to carry one piece of criteria, a project number.

I would rather not build 5 version of frm_Detail as that seems painful and tedious and inelegant and... and…

Can I somehow make frm_DETAIL dynamically change it's query, depending on what form I happen to be in?

The Other problem:

I'm a bit of an Access noob. If you tell me "Oh just put in a cross-Maud Dib on the Widget module," I won't understand. Some specifics would help me. I'm not averse to VBA, but would rather not if I don't have to.

Thank You for considering my problem.

 

[JonXL]

What do the different queries look like?

Are they the same except for the criteria used to specify which record to show on the DETAIL form?

 

[MojaveJim]

The queries use different criteria based on input boxes on the frm_Main_Menu.

So each query has a field in in with this as criteria: [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Like "*" & [Forms]![Main Menu]![srchName] & "*" where "srchname" would be a different input field for the other queries.[/FONT]

 

[JonXL]

If the queries are identical except for the field used in the criteria, I would opt for one query with a conditional criteria (compares to a different field based on the condition you decide).

I hope I'm understanding what you're trying to do.

 

[welshgasman]

Change the form recordsource for the relevant query.
Would need to be done with VBA though. Place the code in the form load event, pass a value via OpenArgs and use a case statement for the correct query.
If you *know* the number of queries will never change, hard code it. If there is a chance it will, place in a table and use a DLookup to find the query name based on the OpenArgs parameter.

HTH

 

[Micron]

just open the detail form, whose recordsource is the same as the main form, and on the Load event, set its filter to be the project value

These TABULAR forms only need to carry one piece of criteria, a project number.

research form filter to see how it's defined and applied - unless I totally missed your point

 

[MojaveJim]

Thank you for your responses to my problem.

Micron's suggestion worked for me! Thank you for that.

Went to the Macro Event of the Form Open button and put in the Detail Form I wanted, then put in a "Where" condition that worked... for 4 out of 5 forms!

There must be something else silly going on with that 5th one that won't work, it just comes back with the right form but empty data. Scratching my head. But having two detail pages is far better than six, so it's mostly a win! I'll take it for now and try and fix it later.

Thanks everyone for their suggestions!

 

[Micron]

Glad I was able to help. It's nice to hit the nail on the head once in a while.