Please help! - Filter combo box in form based on user role table

Copper Contributor

Hello! I hope everything is going well for you.
I have a problem and I need your help. It's simple and straightforward:
* My database's first table has a list of countries (country ID and country name).
* The second table includes user roles (role ID and name).
* A third junction table connects the first and second tables in a many-to-many relationship. Many countries can be assigned to a single user role, and a single country can have multiple user roles.
* The users are listed in the fourth table (user ID and user name). The user roles table has a one-to-many connection. Each user has just one user role.
* The fifth and last table is divided into three columns: 1) user, 2) country, and 3) USD amount.

This is exactly what I need. I've made a form for introducing new records to the fifth table. A combo box for the user, another for the country, and a third text box for the USD amount are provided. I simply want the second combo box to display a list of the countries available to the user based on their roles. I don't want to create a different form for each country / role because I have a lot of them. I'm seeking for a single form that numerous users can use and where each one may entry records based on the countries they've been given.
Could you please assist me?
Thank you so much for your time!

1 Reply


see this demo.

open cboCountryQry in design view, this query is the Rowsource of countryCbo combobox.

see the AfterUpdate event of the combobox userCbo.