469,286 Members | 2,521 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,286 developers. It's quick & easy.

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

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
Nov 12 '05 #1
4 9220
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

Nov 12 '05 #2
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

Nov 12 '05 #3
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

Nov 12 '05 #4
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

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Krista | last post: by
2 posts views Thread by michael nieuwenhuizen | last post: by
1 post views Thread by Robert V | last post: by
2 posts views Thread by sindre | last post: by
11 posts views Thread by Bremse | last post: by
5 posts views Thread by Werner Partner | last post: by
11 posts views Thread by greg.scharlemann | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.