Results 1 to 8 of 8

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

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  2. #2
    Board Regular JonXL's Avatar
    Join Date
    Feb 2018
    Posts
    197
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

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

    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?

  3. #3
    New Member
    Join Date
    Jun 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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: Like "*" & [Forms]![Main Menu]![srchName] & "*" where "srchname" would be a different input field for the other queries.

  4. #4
    Board Regular JonXL's Avatar
    Join Date
    Feb 2018
    Posts
    197
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  5. #5
    Board Regular
    Join Date
    May 2013
    Posts
    606
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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 by welshgasman; Jun 16th, 2019 at 04:07 PM.
    Office 2007
    Access novice. Sometimes trying to give something back

  6. #6
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,652
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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
    Tips for posting problems:
    1) "doesn't work" doesn't help. Post error message text/numbers and/or state what's happening.
    2) if posting code or sql, use code tags; specify on which code line errors occur, if applicable
    3) try to be specific; assume we know nothing about your issue - because we don't!

    Make all suggested changes in copies of your database or to its objects.

    "1 out of 1010 people understand binary. The other 1001 don't."

  7. #7
    New Member
    Join Date
    Jun 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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!

  8. #8
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,652
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •