Form Input handling of Many-to-Many Relationship in Access

[JSMiami]

I have a form that is fed by a certain table. This table connects to
an intermeddiate table and then a look up table. All of this is done
to accomplish a many-to-many relationship. Imagine that the form's
table represents employees in a company and let's assume that an
employee can belong to multiple departments. The lookup table
mentioned above is represents the departments and the intermediate
table represents the many-to-many relationship. I would like to have
an employee input form that contains a drop-down list that allows a
user to select the multiple departments someone can belong to. Please
advise. Thanks in advance

[Pavel Romashkin]

Base the form on a query that returns employee list. On the form, create
a combo box which row source is set to something like

SELECT DepartmentName FROM tblDepartments INNER JOIN JunctionTable ON
tblDepartments .DepartmentID = JunctionTable.DepartmentID WHERE
JunctionTable.EmployeeID = Forms!MyForm!EmployeeID

replace DepartmentID and EmployeeID with appropriate PK/FK fields.
Good luck,
Pavel

JSMiami wrote:

I have a form that is fed by a certain table. This table connects to
an intermeddiate table and then a look up table. All of this is done
to accomplish a many-to-many relationship. Imagine that the form's
table represents employees in a company and let's assume that an
employee can belong to multiple departments. The lookup table
mentioned above is represents the departments and the intermediate
table represents the many-to-many relationship. I would like to have
an employee input form that contains a drop-down list that allows a
user to select the multiple departments someone can belong to. Please
advise. Thanks in advance

[Squirrel]

Hi there,

I needed to do just this very recently and learned how to do it via the
Northwind.mdb demo database which ships with Access. Just do a search on
your machine for Northwind.mdb. Look at the relationships window and you'll
see a junction table (Order Details) in use with Products and Orders. Look
at Customer Orders Subform2 and the query Order Details Extended in order to
get started.

HTH.
Linda
"JSMiami" <js*****@unete.com> wrote in message
news:44**************************@posting.google.c om...

I have a form that is fed by a certain table. This table connects to
an intermeddiate table and then a look up table. All of this is done
to accomplish a many-to-many relationship. Imagine that the form's
table represents employees in a company and let's assume that an
employee can belong to multiple departments. The lookup table
mentioned above is represents the departments and the intermediate
table represents the many-to-many relationship. I would like to have
an employee input form that contains a drop-down list that allows a
user to select the multiple departments someone can belong to. Please
advise. Thanks in advance

[JSMiami]

Pavel, Thanks for the response. I just have one question, What do I
bind the control source of the multi-select list box to?

Thanks

Pavel Romashkin <pa*************@hotmail.com> wrote in message news:<40***************@hotmail.com>...

Base the form on a query that returns employee list. On the form, create
a combo box which row source is set to something like

SELECT DepartmentName FROM tblDepartments INNER JOIN JunctionTable ON
tblDepartments .DepartmentID = JunctionTable.DepartmentID WHERE
JunctionTable.EmployeeID = Forms!MyForm!EmployeeID

replace DepartmentID and EmployeeID with appropriate PK/FK fields.
Good luck,
Pavel

JSMiami wrote:

I have a form that is fed by a certain table. This table connects to
an intermeddiate table and then a look up table. All of this is done
to accomplish a many-to-many relationship. Imagine that the form's
table represents employees in a company and let's assume that an
employee can belong to multiple departments. The lookup table
mentioned above is represents the departments and the intermediate
table represents the many-to-many relationship. I would like to have
an employee input form that contains a drop-down list that allows a
user to select the multiple departments someone can belong to. Please
advise. Thanks in advance

[Pavel Romashkin]

Well, you don't have to bind it at all. This is what I would do.
I would write code in the AfterUpdate event of the list box or make a
command button to do what you want to happen when the user selects
something from the list.

Pavel

JSMiami wrote:

Pavel, Thanks for the response. I just have one question, What do I
bind the control source of the multi-select list box to?

Thanks

Pavel Romashkin <pa*************@hotmail.com> wrote in message news:<40***************@hotmail.com>...

Base the form on a query that returns employee list. On the form, create
a combo box which row source is set to something like

SELECT DepartmentName FROM tblDepartments INNER JOIN JunctionTable ON
tblDepartments .DepartmentID = JunctionTable.DepartmentID WHERE
JunctionTable.EmployeeID = Forms!MyForm!EmployeeID

replace DepartmentID and EmployeeID with appropriate PK/FK fields.
Good luck,
Pavel

JSMiami wrote:

I have a form that is fed by a certain table. This table connects to
an intermeddiate table and then a look up table. All of this is done
to accomplish a many-to-many relationship. Imagine that the form's
table represents employees in a company and let's assume that an
employee can belong to multiple departments. The lookup table
mentioned above is represents the departments and the intermediate
table represents the many-to-many relationship. I would like to have
an employee input form that contains a drop-down list that allows a
user to select the multiple departments someone can belong to. Please
advise. Thanks in advance