Lets assume that the daily work schedules are recorded in a table with
columns WorkDate, JobPositionID and EmployeeID, and that the absenteeism
records are in a table with columns AbsenceDate and EmployeeID. In each
case the EmployeeID column would reference the primary key of an Employees
table, and the JobPositionID would reference the primary key of a
JobPositions table.
I'd suggest that you use two subforms, one based on the work schedule table,
the other on the absenteeism table. Both subforms would be in continuous
form view and would be embedded in a main unbound form. In this unbound form
include an unbound combo box, cboDates say, in which a date can be selected.
You can get the combo box to list a set of dates before and after the current
date by putting the following code in the form's Open event procedure:
Dim n As Integer
Me.cboDates.RowSourceType = "Value List"
For n = -10 To 10
Me.cboDates.AddItem DateAdd("d", n, VBA.Date)
Next n
Me.cboDates = VBA.Date
This would show 10 days before and after the current date for instance, and
would select the current date by default when the form is opened.
To restrict both subforms to the selected date all you need to do is set
each subform control's LinkMasterFields property to the name of the combo box:
cboDates
and their LinkChildFields properties to the name of the date column in each
one's underlying table, in the case of the work schedule subform:
WorkDate
and in the case of the absenteeism subform:
AbsenceDate
In each subform you can use combo boxes for the EmployeeID and JobPositionID
controls. The EmployeeID combo boxes would be set up as follows:
ControlSource: EmployeeID
RowSource: SELECT EmployeeID, FirstName & " " & LastName FROM Employees
ORDER BY LastName, FirstName;
BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm
If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.
The JobPositionID combo box in the wok schedule subform would be set up in
the same way, but its ControlSource property would be JobPositionID and its
RowSource property:
SELECT JobPositionID, JobPosition, FROM JobPositions ORDER BY JobPosition;
The form can be used for entering both work schedule allocations and
absenteeism records for each day simply by selecting a date in the unbound
combo box and then entering rows in each subform as appropriate, selecting
the employees and job positions in the combo boxes.
You could still print the form, but a better solution would be to create an
unbound report which mirrors the form by having two subreports, but a text
box in place of the unbound date combo box. You can then lay out the report
appropriately for printing rather than screen display. In the report you'd
reference the unbound combo box on your form as the ControlSource of its
corresponding unbound text box, e.g.
=Forms![YourMainForm]![cboDates]
All you need to do to print the report is include a button in the unbound
main form with code in its Click event procedure to print the report:
DoCmd.OpenReport "YourReportName"
or the button wizard can insert the code for you.
Ken Sheridan
Stafford, England
