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

MojaveJim

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

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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?
 
Upvote 0
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]
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
Glad I was able to help. It's nice to hit the nail on the head once in a while.
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top