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

MojaveJim

New Member
Joined
Jun 7, 2019
Messages
3
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.
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

JonXL

Active Member
Joined
Feb 5, 2018
Messages
331
Office Version
365, 2016
Platform
Windows
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

New Member
Joined
Jun 7, 2019
Messages
3
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

Active Member
Joined
Feb 5, 2018
Messages
331
Office Version
365, 2016
Platform
Windows
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

Well-known Member
Joined
May 25, 2013
Messages
805
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
 
Last edited:

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,831
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

New Member
Joined
Jun 7, 2019
Messages
3
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,099,246
Messages
5,467,507
Members
406,543
Latest member
margram

This Week's Hot Topics

Top