Combo box and many-to-many relationship

[rogue94]

I am fairly new to MS Access and looking for a solution regarding a form I am trying to build.

The form should be used to show different user entries in the data base and to create or modify users.


Actually I am blocking on how to show available countries/servers and the ones which are already assigned to a user via a combo box and a many-to-many relationship.

To resume:
I have several tables which combined provide me the information I use to build the form (different access to different systems for a user).

My first table Users provides me information about first name, last name etc.
A second table Servers includes the list of different countries/servers a user can have access to.

Since a user can have access to different servers/countries and a country/server can have different users who are accessing it I am confronted with a many-to-many relationship.
Therefore I created a junction table which includes the userID as FK (from the User table) and the ID as another FK (from the Servers table).

What I am trying to achieve now but where I am blocking is:

• to have a combo box which lists all available servers/countries
• mark (check) the entries which are linked to the user record which is shown

example:

Servers: Belgium, Germany, Netherlands, Sweden, UK
User: John Doe => access to Germany & UK

• list/combo box should show all countries but select only the ones John doe has access to (when record John Doe is selected)

Is there any way to achieve this and what needs to be done ?
I am also open for any other solution if this cannot be done via a combo box.

Thanks for the help.

[aytee111]

When a user is selected on the form, a listbox could show all the servers they currently are connected with. A combobox would then list all servers that the user is NOT connected with and would be used to add new servers to this user.

Probably an easier way is to have a subform of servers for this user, that will combine both of those and records can be added/deleted as required.

[June7]

Agree, form/subform arrangement may be easiest. Main form bound to Users and subform bound to junction table with a combobox to select server. The RowSource for this combobox could be like:

SELECT ID, Country FROM Servers WHERE ID NOT IN (SELECT ServerID FROM UserServers WHERE UserID=[UserID]);