I need help making the form to enter hours used for a large group of individuals; I'm not sure how to generate the new/empty records for multiple associated records and show them all at once.
This database is designed to track hours, by pay period, for 200 employees, giving the certification for each group of hours used. Each employee is assigned to just one section, each certification is assigned to exactly one employee, and each hours incident (for lack of a better term) is assigned to exactly one certification and exactly one pay period.
I need to give the payroll clerks a blank form for each pay period, showing all the employees in the section that clerk is responsible for, and creating a new hour record for each employee for that pay period. We need to be able to show all the employees for that section in one long list; no shuffling between records with the record selector.
(In an ideal world, we would only create new records if the employee actually used hours in that pay period, but I'm willing to accept creating a lot of "hours" records with 0 hours used, if that's the easier way to do it.)
Associated Tables/Fields
List_Sections: SectionID
List_Employees: EmployeeID, SectionID
List_Certifications: CertID, EmployeeID
List_PayPeriods: PeriodID
Table Being Added To
List_Hours: HoursID, HoursUsed, CertID, PeriodID (HoursID is an autonumber)
The form will present these questions to the clerk:
- Who are you? (they select their name from the combobox; this is solved)
- Which section do you want to add hours for? (can only choose from the sections they're responsible for; this is solved)
- Which pay period are you entering data for? (can select any pay period going forward two weeks or back a year)
This should populate a datasheet/tabular subform with the following columns:
- EmployeeID (one line automatically generated for every employee in the section)
- PeriodID (automatically populated by what the clerk chose in question 3)
- HoursUsed (they will type this in)
- CertID (this is a combobox limited to certifications associated with the employee in question; I need to sort out how to make the default value for new hours records be the newest certification for that employee)
- HoursID (this will be the autonumber, and I may not actually stick it on the form, because I suspect my users may try and delete/change it and become frustrated.)
I'd also like to pull up any existing records that happen to have been created previously for this pay period for the given employee, but that's a secondary priority (I already have a query that will alert us to strange situations like someone using 160 hours in two weeks, and a form to edit existing hours entered.)
Relevantish information:
- I am using Access 2010.
- My understanding of Microsoft Office products is great, but my understanding of VBA is poor and it's been ten years since I did this much work with any kind of code more advanced than "Concatenate" in Excel; I'm extremely proud of my finally-functioning cascading comboboxes.
- I have a copy of "Access 2010: The Missing Manual" right here on my desk, but no other paper references. If this answer is somewhere in this manual, I will go demand a refund from the person who taught me how to find things in books.
- Okay, fine, I'll admit it, they're actually going to see fields like "Employee's Full Name" and "Certification Description With Several Relevant Details" on the form. Those are on the relevant tables, periodically changed by an update query, which (once I have this database done) will be run automatically once every day or two. Because I was told to never put calculated fields or lookups in my tables.
I've been trying to think this problem through and I'm stuck at "I wonder if you can do something like run an append query as an "AfterUpdate" event procedure..." I'm hoping there's a vastly more-elegant solution that hasn't occurred to me yet.
