By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,318 Members | 2,107 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,318 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Share this Question
Share on Google+
4 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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.