I'm trying to create a report for any given month which shows each employee and where they will be on each day of that month (Base1, Base2, Base3, Base4, Vacation).
The employees are stored in tblEmployees
The movements of the employees are stored in tblMovements
My current method works, but it is painfully slow and inefficient:
There is a combobox on a form where a month is selected.
VBA opens the report and puts this value into a textbox in the report header (e.g. 01/01/2013).
There are 31 other textboxes in the report header which have their controlsources set to that date +1 (or +2 or +3 etc.)
So now I have each day of that month laid out.
There are 31 textboxes in the detail which call a UDF to figure out where the employee was on that given day (passes the employeeID and relevant date).
There is also conditional formatting to color these textboxes accordingly.
I also need daily totals for each base.
So now I need another 4 x 31 textboxes in the detail to count which base they were at on that day (controlsource set to an IIF statement)
I also need another 5 x 31 textboxes in the report footer as running sums and a total.
So as you can guess, this report is not very efficient.
I'd like to find a better solution I'm struggling to see how I can build it any differently.
Does anyone have any better solutions?
I know I could create a temporary recordset of employees and loop through that, but I'm not sure how I reference the textbox controls for that row in the detail...
The employees are stored in tblEmployees
The movements of the employees are stored in tblMovements
My current method works, but it is painfully slow and inefficient:
There is a combobox on a form where a month is selected.
VBA opens the report and puts this value into a textbox in the report header (e.g. 01/01/2013).
There are 31 other textboxes in the report header which have their controlsources set to that date +1 (or +2 or +3 etc.)
So now I have each day of that month laid out.
There are 31 textboxes in the detail which call a UDF to figure out where the employee was on that given day (passes the employeeID and relevant date).
There is also conditional formatting to color these textboxes accordingly.
I also need daily totals for each base.
So now I need another 4 x 31 textboxes in the detail to count which base they were at on that day (controlsource set to an IIF statement)
I also need another 5 x 31 textboxes in the report footer as running sums and a total.
So as you can guess, this report is not very efficient.
I'd like to find a better solution I'm struggling to see how I can build it any differently.
Does anyone have any better solutions?
I know I could create a temporary recordset of employees and loop through that, but I'm not sure how I reference the textbox controls for that row in the detail...
