Need Query have focus *in front* / "before* form (or use subform)

[skydivetom]

Experts:



I need some assistance with either a) tweaking some VBA AND b) modifying how queries have focus OR c) modifying the existing processes and potentially use subforms (vs. queries for data entry).

Allow me to provide some background first:

1. Upon opening the database, the default's form is a "Main Menu" (containing four listboxes such as 'Forms', 'Queries', 'Reports' & 'Admin').
* Go to the (blue) listbox "Admin" and select menu item "Unhide Ribbon & Navigation Pane" to display all table/query objects, etc.

2. At this point, the database displays the following DB objects:
a. Table "src_Data"
b. Query "Q100_ViewData"
c. Query "SampleQuery"
d. Form "F001_SYS_MainMenu"
e. Form "F100_ViewData"

3. If you click on "F001_SYS_MainMenu", you'll be returned to the "main menu".

4. Additional background before I post the issue/question for which I need some help:
a. In (green) listbox "Forms", click on menu item "View Query Data"
b. Another form ("F100_ViewData") opens; here, the user must select 1) Field Type and 2) Parameter Value.
c. Upon selection of e.g, "Source: Owner" & "All", click "Execute Query".

Now, here's what I need some help with:
- Once a field type/parameter value was selected and I clicked on "Execute Query", the form ("F100_ViewData") "sits" on top of the query.
- However, at this time, I'd like to be able to enter the query (i.e., field "tgt_Column") and make a value selection from the drop-down menu.
- Also, if I were to click on "Close Query" command button, naturally the query closes. So, ultimately, I NEVER had the opportunity to make a value selection in field/column "tgt_Column" (in the query).

My question(s):
1. How should I tweak the VBA to somehow bring focus onto "Q100_ViewData" so that I can select values from "tgt_Column".
2. Then, if I finish the selection process and close the query, it would be great if the form is still open (behind the query) and I could immediately filter for another 1) Field Type and 2) Parameter Value selection.
3. Alternatively, I am open to using a form/subform assuming that my record filtering will be applied to, e.g., the subform.

Does anyone have a recommendation as to how I can best achieve a seamless process for filtering for records and then being able to select/change values in field/column "tgt_Column" (either directly in the query or via form/subform)?

Thank you for your help in advance!

EEH

[pbaldy]

Most of us would use a form or subform. We never let users directly access tables or queries; you have little control over the user or program flow in that situation. I'd go the form/subform route since you're open to it.

[skydivetom]

pbaldy -- thank you for the feedback. I'm not entirely certain as to how I can integrate the subform onto the existing form. If you could provide a simple example, I'd greatly appreciate it. Thank you.

[skydivetom]

I created a subform (based on the query). Unfortunately, when clicking the main form's command button "Execute Query" (with different values), the subform is NOT refreshed. Instead, I actually get an error message indicating that "Access can't find the field...." (see attached).

I used the following VBA in the command button's OnClick property:

    'Clear listbox selection after running query
    For Each varItem In Me.lstDataSource.ItemsSelected
        Me.lstDataSource.Selected(varItem) = False
    Next varItem
        

    Forms![F100_ViewData].[F101_ViewData_Subform].Form.Requery

How should I modify the VBA so that the subform is refreshed once different values are selection in the option group/listbox and I click on "Execute Query"? Btw, the underlying query does change (in the background) as intended.

Thank you,
EEH

P.S. I also attached the updated sample DB containing the subform.

[pbaldy]

Was this resolved in your other thread?