We need to automatically create a schedule for multiple users for completing a daily audit.
We have a database with Users in a users table. Each user is assigned a "Layer" and an "Area". We need a form that will fill out tblScheduledAudits. This will consist of adding a new record for each day for each user with the layer "Supervisor" only. The record will list the User Name, AuditForm reference, and the ScheduledDate.
The AuditForm variable will be an randomly chosen from the table tblAuditFormDefinitions, however, it must choose from the audits that are within the specific users "Area" (these are found in tblCustomizeAuditLocations). (For example: A supervisor that is in BUA (Area) will be assigned a random cell (LocationOfAudit, or LocationName) that is linked to that area in the tblCustomizeAuditLocations). For records in the tblAuditFormDefinitions that have the same Location_Of_Audit, the last record with that audit location should be chosen.
The ScheduledDate will be a date, Monday through Friday for the next 2 months starting from "today". (the day the user chooses to run this with a Form button).
In the case a user area is changed, or a new user is added, we need an option (a form button) to "refresh" the scheduled audits table from "tomorrow" through the next 2 months without losing any existing data from the beginning through "today" .