Solved Weird Behaviors Filtering a Report in a Navigation Form (1 Viewer)

6thDAY

Member
Local time
Today, 04:56
Joined
Oct 10, 2021
Messages
36
I have a Navigation Form with multiple tabs. Each tab contains a subform with a different report, as well as buttons. In one of my tabs, I have a report that I want to filter based on a certain criteria. I created a combo box on top of the report, which filters all items in the report based on a criteria.

Assume we're on the specific tab mentioned, the diagram looks something like this:
untitled.png


I have the following code in the Subform containing the combo box filter and report, with let's say the values in the combo box are 1-5. :

Code:
Private rpt As Report
    
Private Sub Form_Load()
    Set rpt = Me.subreport_TestReport.Report
End Sub

Private Sub comboBox_FilterByID_AfterUpdate()

    rpt .Filter = "ID = " & Me.comboBox_FilterByID
    rpt .FilterOn = True

End Sub

I encounter the following issue with the above code as soon as I select a value in the combo box filter:

Capture.PNG


I updated the code:

Code:
Private Sub comboBox_FilterByID_AfterUpdate()

    Me.subreport_TestReport.Report.Filter = "ID = " & Me.comboBox_FilterByID
    Me.subreport_TestReport.Report.FilterOn = True

End Sub

Now, the report filters based on whatever value I select from the combo box, but I encounter the following issues where the value I select does not populate in the combo box, but instead, the value is populated with the "last" value I selected, not the current one. Secondly, every other value I select to filter, I receive this error:

Capture.PNG


The above error points me to the following code:
Code:
Me.subreport_TestCasesBCR.Report.FilterOn = True

I don't know what's causing this issue and I posted the same issue on StackOverflow, but all I was told was to avoid using a Navigation Form. Unfortunately I cannot re-factor the code.

Anyone know what is causing this issue?
 

isladogs

MVP / VIP
Local time
Today, 11:56
Joined
Jan 14, 2017
Messages
18,186
It would be courteous to provide the link to the SO thread.
Unfortunately navigation forms are very difficult to modify beyond their basic design due to their 'special nature'.
References to the subform controls must be done differently to those on standard forms.

The advice at SO is probably going to be correct. However it would help to upload a cut down version of your database.
It may help someone find a solution.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:56
Joined
Oct 29, 2018
Messages
21,357
Hi. Welcome to AWF!

I agree. Please consider posting a sample db, so someone could try to find a solution for you.
 

6thDAY

Member
Local time
Today, 04:56
Joined
Oct 10, 2021
Messages
36
I've created a sample database that produces the same issues as in my real database. Can you please inspect?
 

Attachments

  • Database1.accdb
    768 KB · Views: 323

6thDAY

Member
Local time
Today, 04:56
Joined
Oct 10, 2021
Messages
36
I've temporarily fixed causing the run-time error '2467', but the issue of the current value in the combo box remains. Can someone check the latest demo and open up "Form 1"?

Once it's open, select a value in the combo box. The report will filter fine, but the value is not retained in the combo box.
 

Attachments

  • Database1.accdb
    800 KB · Views: 426

Minty

AWF VIP
Local time
Today, 11:56
Joined
Jul 26, 2013
Messages
10,354
When you say retained - do you mean it disappears when you close the form?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:56
Joined
May 7, 2009
Messages
19,169
see the code of the combobox.
 

Attachments

  • Database1.accdb
    492 KB · Views: 412

6thDAY

Member
Local time
Today, 04:56
Joined
Oct 10, 2021
Messages
36
see the code of the combobox.
Excellent, I actually just figured it out as well but I think I did it sort of a cheat way since Navigation Forms dont play fair:

Code:
Private Sub comboBox_FilterByID_AfterUpdate()
    Dim temp As String
    temp = Forms(Screen.ActiveForm.name).Controls("NavSubfrm").Controls("comboBox_FilterByID")
   
    Forms(Screen.ActiveForm.name).Controls("NavSubfrm").Controls("subreport_TestReport").Report.Filter = "ID = " & temp
    Forms(Screen.ActiveForm.name).Controls("NavSubfrm").Controls("subreport_TestReport").Report.FilterOn = True
   
    Forms(Screen.ActiveForm.name).Controls("NavSubfrm").Controls("comboBox_FilterByID") = temp
End Sub

I feel like yours is more the correct approach. Opinion?
 

6thDAY

Member
Local time
Today, 04:56
Joined
Oct 10, 2021
Messages
36
see the code of the combobox.
Also this code:
Code:
.LinkMasterFields = "[comboBox_FilterByRating]"
.LinkChildFields = "rating"

Do you know how if it's possible to link and do a "AND" of multiple fields? What would the syntax look like?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:56
Joined
Oct 29, 2018
Messages
21,357
Also this code:
Code:
.LinkMasterFields = "[comboBox_FilterByRating]"
.LinkChildFields = "rating"

Do you know how if it's possible to link and do a "AND" of multiple fields? What would the syntax look like?
If you want to use multiple fields to link the subform to the main form, you wouldn't use the AND operator. Instead, you would just separate the fields with a semicolon (;).
 

6thDAY

Member
Local time
Today, 04:56
Joined
Oct 10, 2021
Messages
36
If you want to use multiple fields to link the subform to the main form, you wouldn't use the AND operator. Instead, you would just separate the fields with a semicolon (;).
I'd figured that would have done it, but I got an error:
Capture.PNG


The code looks like this:

Code:
.LinkMasterFields = "comboBox_FilterByRating; comboBox_FilterByOther"
.LinkChildFields = "rating; other"

I also attempted:

Code:
.LinkMasterFields = "[comboBox_FilterByRating]; [comboBox_FilterByOther]"
.LinkChildFields = "rating; other"

But then go this error:
Capture.PNG
 

isladogs

MVP / VIP
Local time
Today, 11:56
Joined
Jan 14, 2017
Messages
18,186
Try putting quotes at the start and end of each individual item
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:56
Joined
Oct 29, 2018
Messages
21,357
I just did a quick test, and it worked for me. With regards to the first error, you'll need to reset both properties to empty first before changing them, unless the number of the new linked fields were going to be the same from the old one.
 

6thDAY

Member
Local time
Today, 04:56
Joined
Oct 10, 2021
Messages
36
I just did a quick test, and it worked for me. With regards to the first error, you'll need to reset both properties to empty first before changing them, unless the number of the new linked fields were going to be the same from the old one.
What syntax did you use?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:56
Joined
Oct 29, 2018
Messages
21,357
What syntax did you use?
Well, since it was just a test, I did this from the Immediate Window:
Code:
Forms!FormName.SubformName.LinkMasterFields="ID1;ID2"
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:56
Joined
May 7, 2009
Messages
19,169
on the sample db you posted, i don't see "Other" field in your table1/Form.
 

6thDAY

Member
Local time
Today, 04:56
Joined
Oct 10, 2021
Messages
36
on the sample db you posted, i don't see "Other" field in your table1/Form.

Please see new attachment. I added in a "Position" column for the report.

I was able to get it to filter using "BOTH" filter combo boxes thanks to @theDBguy, but I'm not sure how to do it, if there's just one filter selected?
 

Attachments

  • Database1.accdb
    832 KB · Views: 444

6thDAY

Member
Local time
Today, 04:56
Joined
Oct 10, 2021
Messages
36
on the sample db you posted, i don't see "Other" field in your table1/Form.

I actually figured it out, but I'm not sure if this is the correct approach. Can you confirm? I want to be a better developer, not just write hacky, bypass code.
Code:
Private Sub comboBox_FilterByRating_AfterUpdate()
    'arnelgp
    If Len(Me.comboBox_FilterByRating & "") <> 0 And Len(Me.comboBox_FilterByPosition & "") = 0 Then
        With Me.subreport_TestReport
            .LinkMasterFields = "comboBox_FilterByRating"
            .LinkChildFields = "rating"
        End With
    ElseIf Len(Me.comboBox_FilterByRating & "") <> 0 Then
        'Clear the linkage first
        With Me.subreport_TestReport
            .LinkMasterFields = ""
            .LinkChildFields = ""
        End With
        
        With Me.subreport_TestReport
            .LinkMasterFields = "comboBox_FilterByRating;comboBox_FilterByPosition"
            .LinkChildFields = "rating;position"
        End With
    Else
        With Me.subreport_TestReport
            .LinkMasterFields = ""
            .LinkChildFields = ""
        End With
    End If
    Me.subreport_TestReport.Requery
End Sub

The code I wasn't sure of was this:
Code:
'Clear the linkage first
With Me.subreport_TestReport
    .LinkMasterFields = ""
    .LinkChildFields = ""
 End With

Access complains when I attempt to set LinkMasterFields and LinkChildFields before setting it empty. Do I have to set it to empty first before linking it again?

Please see demo for reference.
 

Attachments

  • Database1.accdb
    864 KB · Views: 413

theDBguy

I’m here to help
Staff member
Local time
Today, 04:56
Joined
Oct 29, 2018
Messages
21,357
Access complains when I attempt to set LinkMasterFields and LinkChildFields before setting it empty. Do I have to set it to empty first before linking it again?
That was in my comment earlier. Did you miss it? Or, maybe I wasn't clear.
 

Users who are viewing this thread

Top Bottom