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

[juanss1989]

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!

[CJ_London]

from your description, sounds like you are talking about what is typicaklky called cascading combos. If so your rowsource for the combo (cboCountry?) should be something like

SELECT tblCountry.countryID,tblCountry.countryname
FROM  tblJunction inner join tblCountry on tblJunction.countryID=tblCountry.countryID
WHERE tblJunction.UserID=[cboUser]

in the cboUser afterupdate event put

cboCountry.requery

to refresh the list based on the user selected

depending on how your form is constructed as intended to be used you may need additional code.