enter data into a table that is based on 2 other tables

B

Bob

Hi,
I have a small database that records in one table, employees
and in another table, the work sites they have the necessary skills to work
at.
This results in a many to many relationship so I have created a third table
with the primary keys of the first two, as well as, the date the skill was
obtained.
Table details are as follows:

tblEmployees
EmployeeID
FirstName
LastName

001 Fred Bloggs
002 Joe Blow

tblWorkSite
WorksiteID
WorksiteName

001 Alpha
002 Beta

tblEmployee_WorkSite
EmployeeID
WorkSiteID
Date

001 002 2006-07-21

I am trying to create a form with list/combo boxes holding Name data (not
just ID numbers) from the first two tables and then inserting the chosen
data (ID numbers, this time) into the third table.
The data in tables 1 and 2 must not be changed.

Any ideas greatly appreciated

Bob
 
M

Marshall Barton

Bob said:
I have a small database that records in one table, employees
and in another table, the work sites they have the necessary skills to work
at.
This results in a many to many relationship so I have created a third table
with the primary keys of the first two, as well as, the date the skill was
obtained.
Table details are as follows:

tblEmployees
EmployeeID
FirstName
LastName

001 Fred Bloggs
002 Joe Blow

tblWorkSite
WorksiteID
WorksiteName

001 Alpha
002 Beta

tblEmployee_WorkSite
EmployeeID
WorkSiteID
Date

001 002 2006-07-21

I am trying to create a form with list/combo boxes holding Name data (not
just ID numbers) from the first two tables and then inserting the chosen
data (ID numbers, this time) into the third table.
The data in tables 1 and 2 must not be changed.


You can do that by making the combo boxes Bound Column the
pk field from its respective table but the name field as the
first visible column (ColumnWidths 0;). The main form
would be unbound so you don't have to worry about the data
being changed. Then the subform control's Link Master/Child
Fields property will limit the subform's data to the
matching record. Make the two text boxes for the pk fields
on the subform invisible so they can not be edited.

A more common approach to the many-to-many form arrangement
is to only have the person (or site) combo box on the main
form. Then display the subform in continuous view with a
combo box to select/edit the site (or person) a;ong with a
text box for the date.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top