What is the better way to filter out record selections in a form based on user input? _afterupdate or _beforeupdate?

Question

new to Access.

Here is the form layout: https://i.imgur.com/LVZrs9U.png

You can see there is 1 main project page with 6 different subforms in each tab (subform 2-7), all with their own table.

They are linked by Project Number (primary key) and Request Number.

The main purpose of this form is to record information for a specific project (Project Number: Unique) over the course of many meetings (Request Number: starts at 1).

For example, I want to record important information about project 101 and this is the 4th meeting we're having about it.

Some of the subforms could have many different records. For example, a customer could be talking about 11 different new data sources and so for project 101, request: 4, there would be 11 records for tab/subform 5.

I want a way where when the user puts in a Project Number and a Request Number, it pulls up the existing records for those.

I used this combobox trick for the main project management page (https://www.youtube.com/watch?v=rSPS24EfijI), but this will not work with tabs/subforms 2-7.

So I thought about using the following guide from Microsoft: https://support.office.com/en-us/article/locate-specific-records-in-a-database-e186238d-e4d7-4918-92a0-1236661c6f58

https://docs.microsoft.com/en-us/office/vba/api/access.form.filter(property)

Is it better to do it at the main page level:

Private Sub RequestNumber_AfterUpdate()
Page2.Filter = "ProjectNumber = 'Me.ProjectNumber.Value'"
Page2.Filter = "RequestNumber = 'Me.RequestNumber.Value'"

Page3.Filter = "ProjectNumber = 'Me.ProjectNumber.Value'"
Page3.Filter = "RequestNumber = 'Me.RequestNumber.Value'"

Page4.Filter = "ProjectNumber = 'Me.ProjectNumber.Value'"
Page4.Filter = "RequestNumber = 'Me.RequestNumber.Value'"

Page5.Filter = "ProjectNumber = 'Me.ProjectNumber.Value'"
Page5.Filter = "RequestNumber = 'Me.RequestNumber.Value'"

Page6.Filter = "ProjectNumber = 'Me.ProjectNumber.Value'"
Page6.Filter = "RequestNumber = 'Me.RequestNumber.Value'"

Page7.Filter = "ProjectNumber = 'Me.ProjectNumber.Value'"
Page7.Filter = "RequestNumber = 'Me.RequestNumber.Value'"

Page2.FilterOn = True
Page3.FilterOn = True
Page4.FilterOn = True
Page5.FilterOn = True
Page6.FilterOn = True
Page7.FilterOn = True

End Sub

Or at the subform level?

Private Sub ProjectNumber_BeforeUpdate(Cancel As Integer)

Me.Filter = "ProjectNumber = 'Me.ProjectNumber.Value'"
Me.Filter = "RequestNumber = 'Me.RequestNumber.Value'"


Me.FilterOn = True

End Sub

Both options didn't work with nothing changing. Should I be calling the form name? Tab control? The page of the tab?

Also, it shouldn't matter if I call on the project/request number in the main form or the subforms because they're all linked together, right? Aka, whatever I put in the main form gets cascaded down to the subforms.

Thank you.

Two pieces of advice. 1. You generally should only use BeforeUpdate if you want to validate data before it is saved on a table. For everything else use AfterUpdate, OnChange or OnLostFocus depending on the exact behavior you are looking for. 2. Any form control that is used for selecting or filtering should be unbound and any form control that contains data used to link between two tables should be locked. — SunKnight0
Tab control pages do not have filter property. Forms and reports have filter property. Do subforms have relationship with main form? Master/Child Links properties of subform/subreport container should synchronize records. Must reference subform and its properties through the container control that holds form. — June7
Huh that is very unusual, it wasn't synchronized for me earlier. I deleted the relationship and retried it and it synchronizes now. Thank you! — Anonymous

SunKnight0 SunKnight0 · Accepted Answer · 2019-05-08T20:14:22

Look at my note for generic advice but specifically:

Page2.Filter = "ProjectNumber = 'Me.ProjectNumber.Value'"

is wrong. use

Page2.Filter = "ProjectNumber = '" & Me.ProjectNumber & "'"

What is the better way to filter out record selections in a form based on user input? _afterupdate or _beforeupdate?

1 Answers