I have a form where you choose the employees in a dropdown continous form.(From tblEmployee) that worked this week and store them in TSLabor table. In another form I track what activities were done during the days of the week (could have multiple activities for any given day) in tblActivities. In this form I have a sub form list of all the employees who are in tsLabor for this week. This way the user can input the hours for each employee. (Not all employees will have hours in every activity, but I can handle zero hours.) These records are stored in tsActivityLabor. I add a record for each employee in the TSLabor table with the activityID it each time a new activity record is created.
User can not add/delete in the subfom, but they can in the First form. My issue is how do I add/Delete from the tsActivityLabor table (for each activityID) when an employee is added/deleted from the First form.
I need to check to see if the employee has a record for each activityID for that week. If not add it.
Right now I create a record set of activityIDs for the week from tblActivities. I then create another recordset from the tblTSLabor of all the employees in that week. I then take each ActivityID from the first recordset and create a third recordset of all the employees for that ActivityID in the tsActivityLabor table. I then verify that each employee from the second recordset has a record in the third recordset.
There has to be a quicker way of doing this but I can't figure it out. One thought is to create an update query that could look at the tsActivityLabor table and verify that there is an employee record for each employee for each activity. Researched but can't find any examples of a query like this. any help would be appreciated. Adolph
