Relate all records in one table to all in another

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 3 Tables

Employee table, Product table and 3rd Junction Table with Employee Id,
Employee Commission % and Product Id

All Emloyees are linked to all Products Via Junction Table and each employee
is paid a different Commission % on each product.

My problem is that if a user enters a new Employee in the Employee Form they
will have no related Products unless the user manually adds each product and
commission in a sub form.

How can have a New employee automatically relate to all products and show a
list of all Products in the sub form so that all the user has to do is enter
the Commission Percentage

I hope that makes some sense,

Thanks, Sean
 
well, you can append the product IDs into the junction table when the
subform is entered. something along these lines in the subform control's
Enter event, as

If Me!SubformControlName.Form.RecordsetClone.RecordCount = 0 Then
CurrentDb.Execute "INSERT INTO JunctionTable ( " _
& "EmployeeId, ProductId ) SELECT " _
& Me!EmployeeId & ", ProductTable.ProductId " _
& "FROM ProductTable", dbFailOnError
Me!SubformControlName.Form.Requery
End If

substitute the correct names of the tables, fields, and the field in the
main form.

hth
 
Back
Top