Solved Combo Box Filter Not Filtering Report Correctly in Navigation Form When Using Multiple Reports? (1 Viewer)

6thDAY

Member
Local time
Today, 08:45
Joined
Oct 10, 2021
Messages
36
I have a Navigation Form with 2 buttons, 1 filter combo box, and a subform beneath containing a report. Each button displays a different report in the subform. The combo box filters the same field in both reports.

It looks like so:

Capture.PNG


The subform in the Navigation form is linked based on their LinkMasterFields and LinkChildFields properties.

When the navigation is opened, the first report is displayed. However, filtering the value for the field Animal using the combo box does not filter the report, nor does the value in the combo box get retained. Every selection made in the combo box disappears.

However, if I click the button Table2 to display the secondary report, filtering works fine and the value is retained in the combo box after selection.

Clicking Table1 to display the first report again results in the filtering issue.

This issue is related to the following post, in which @arnelgp graciously helped me with:
Solved - Weird Behaviors Filtering a Report in a Navigation Form | Access World Forums (access-programmers.co.uk)

Attached is the demo representing the issue.

@arnelgp if you are available, can you provide assistance since you helped me resolve the above related issue?
 

Attachments

  • Database11 (1).accdb
    1.2 MB · Views: 453

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:45
Joined
May 7, 2009
Messages
19,169
again Navigation form is not very cooperative.
what i did is to Edit query.
 

Attachments

  • Database11 (1) (1).accdb
    816 KB · Views: 432

Minty

AWF VIP
Local time
Today, 15:45
Joined
Jul 26, 2013
Messages
10,354
I hate to sound like a stuck record here, but at least 5 or 6 people have advised you against using the in-built navigation forms in other threads, as (you have discovered) they are a pain in the backside to work with.

You can create a "navigation" form of your own that avoids all the pitfalls and gives you complete control.
I'd make the move now rather than cludging together workarounds forever and a day.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:45
Joined
May 7, 2009
Messages
19,169
i made some more modification and i think it is ok for now.
 

Attachments

  • Database11 (1) (1).accdb
    876 KB · Views: 381

6thDAY

Member
Local time
Today, 08:45
Joined
Oct 10, 2021
Messages
36
I just went through the code in `Form_Form1` and `Form_Navigation Form` and have a couple questions regarding this:

1. Excuse my limited knowledge, but based on what I see and walking through the lines of code in `Form_Form1`, you're essentially filtering the data selected, i.e. `Animal`, copying that SQL criteria and putting it back into the Query in which the Report has its `Record Source` set as, which shows the filtered data. I know you mentioned Navigation Forms are hard, soI'm assuming there's no way to filter the Reports directly using `MasterFields` and `ChildFields`, and this query is the alternative to make it work in a Navigation Form?

2. I'm having trouble following the code in `Form_Navigation Form`. Can you please elaborate?

3. Is there a need to have duplicate code in `createOutput` and `Form_Output` and can the Module 1 code be used by calling the `createOutput` function in `Form_Output` instead?

Thank you.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:45
Joined
May 7, 2009
Messages
19,169
1. yes you are correct, i am modifying the Query (which the reports are based) and adding a Criteria.
i find it hard to use LinkFields as it also "blank out" the combo after the selection.

2. the code on form_navigation form, (open event), is just Re-instating the original query back.
the querydef with prefix "zz" is the same as those without with same query name.
the only difference is that those with "zz", they don't have any filter on them.
while those without the "zz" (which are the recordsource of your reports), we modify their
sql when we change value of combobox.

3. i transfer createOuput to a separate module so it can be called from anywhere your db.
 

6thDAY

Member
Local time
Today, 08:45
Joined
Oct 10, 2021
Messages
36
1. yes you are correct, i am modifying the Query (which the reports are based) and adding a Criteria.
i find it hard to use LinkFields as it also "blank out" the combo after the selection.

2. the code on form_navigation form, (open event), is just Re-instating the original query back.
the querydef with prefix "zz" is the same as those without with same query name.
the only difference is that those with "zz", they don't have any filter on them.
while those without the "zz" (which are the recordsource of your reports), we modify their
sql when we change value of combobox.

3. i transfer createOuput to a separate module so it can be called from anywhere your db.

Thanks @arnelgp as always. You saved my day!
 

Users who are viewing this thread

Top Bottom