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

  • Thread starter Thread starter Bob
  • Start date Start date
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
 
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.
Click to expand...


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.
 
Back
Top