Filter subform based on user in MS Access

Question

I have a database where I created a log in box. Each user accesses the file by entering his/her credentials in the log in box. Those credentials are stored in a separate table called "users". The same table also stores the field "region" corresponding to where each user is located.

In the database there are two other tables: "companies" and "subsidiaries". The "subsidiary" table contains a field called "region", with the same set of values as those stored in the "users" table (see above). These tables are populated using a form/subform combination. "Companies" is the parent (main form) and "subsidiaries" is the children (sub-form).

I need to have the sub-form display ONLY the records where the region field in the sub-form matches the region field corresponding to the user who just logged in through the log in box.

I am fairly new to Access and if someone could provide some guidance, that would be truly appreciated.

Best, Bernardo

Answer 1

In the RecordSource of the subform under Properties, you can write in a SQL query that conditions the records to the login region field. Similarly, you can also dynamically do so in VBA using an OnOpen event:

Forms!Companies!Subsidiaries.Form.RecordSource = "SELECT * FROM Subsidiaries WHERE [REGION] ='" & Forms!Login!Region & "'"

Please note this will not output a filtered form but simply the form with reduced set of records that is still editable (i.e., other records not viewable). Also, I am assuming that the table source of the subform shares the same name -Subsidiaries. Finally, the Login form must be opened in the background to use its specified Region text control.