Hi Suzy
There is a strong temptation here to compromise on the table design so as to
make the desired user interface easy to implement. This is NEVER a good
idea.
The best table design here is two tables - Employees and WorkSchedule.
Employees holds all your employee information - names, addresses, etc and
has a numeric primary key "EmployeeID" (let's say it is an autonumber).
EmployeeID has only two fields - WorkDate (date field) and EmployeeID
(numeric). These two fields together make up a composite primary key (an
employee can't work twice on the same date) and there is a one-to many
relationship between Employees and WorkSchedule on EmployeeID.
Now it is a simple matter to list all the days an employee is working, or to
list all the employees working on a particular day. The problem is to
create your form with an array of checkboxes to build the schedule.
We will actually base the form on a query that looks and behaves like a
crosstab query. Create a new query and paste the following code into the
SQL view window, then save the query as "frmWorkSchedule":
=================== start code =====================
PARAMETERS [Forms]![frmWorkSchedule]![txtStartOfWeek] DateTime;
SELECT Employees.EmployeeID, [LastName] & ", " & [FirstName] AS EmpName,
Exists (Select EmployeeID from WorkSchedule as W
where W.EmployeeID=Employees.EmployeeID
and WorkDate=[Forms]![frmWorkSchedule]![txtStartOfWeek]) AS Mon,
Exists (Select EmployeeID from WorkSchedule as W
where W.EmployeeID=Employees.EmployeeID
and WorkDate=[Forms]![frmWorkSchedule]![txtStartOfWeek]+1) AS Tue,
Exists (Select EmployeeID from WorkSchedule as W
where W.EmployeeID=Employees.EmployeeID
and WorkDate=[Forms]![frmWorkSchedule]![txtStartOfWeek]+2) AS Wed,
Exists (Select EmployeeID from WorkSchedule as W
where W.EmployeeID=Employees.EmployeeID
and WorkDate=[Forms]![frmWorkSchedule]![txtStartOfWeek]+3) AS Thu,
Exists (Select EmployeeID from WorkSchedule as W
where W.EmployeeID=Employees.EmployeeID
and WorkDate=[Forms]![frmWorkSchedule]![txtStartOfWeek]+4) AS Fri
FROM Employees
ORDER BY Employees.LastName, Employees.FirstName;
=================== end code =====================
Now, create a new tabular (continuous) form with qryfrmWorkSchedule as its
RecordSource. In the detail section, place a textbox bound to EmpName and
five checkboxes bound to Mon, Tue, Wed, Thu and Fri. Name these checkboxes
"chkMon", "chkTue", etc. Select all these controls and set Enabled=No and
Locked=Yes.
In the header of the form, place appropriate heading labels over each column
(Employee, Mon, Tue,...) and add another unbound textbox named
txtStartOfWeek with a date format of your choosing. Save the form as
frmWorkSchedule.
You now have a read-only form which will show who is working on which day of
the week starting with the date in the header textbox (assuming that date is
a Monday). The read-only part is just a temporary problem! ;-)
Now, open the form again in design view and create five small transparent
command buttons. Name these cmdMod, cmdTue, etc and place each one over its
corresponding checkbox. Now set the OnClick property for cmdMon to
=ToggleWork(0), and for cmdTue to =Togglework(1) etc.
Finally, paste this code into the form module:
=================== start code =====================
Option Compare Database
Option Explicit
Private Function ToggleWork(DayOffset As Integer)
Dim EmpID As Long, dt As Date, sDate As String
EmpID = Me!EmployeeID
dt = txtStartOfWeek + DayOffset
sDate = Format(dt, "\#yyyy\-mm\-dd\#")
If Me("Chk" & Format(dt, "ddd")).Value Then
CurrentDb.Execute "Delete from WorkSchedule where EmployeeID=" _
& EmpID & " and WorkDate=" & sDate
Else
CurrentDb.Execute "Insert into WorkSchedule " _
& "(EmployeeID, WorkDate) Values (" & EmpID & ", " & sDate & ")"
End If
Me.Requery
With Me.RecordsetClone
.FindFirst "EmployeeID=" & EmpID
Me.Bookmark = .Bookmark
End With
End Function
Private Sub Form_Load()
'default to next week
txtStartOfWeek = Date + 7
txtStartOfWeek_AfterUpdate
End Sub
Private Sub txtStartOfWeek_AfterUpdate()
' make sure it's a Monday
txtStartOfWeek = txtStartOfWeek - Weekday(txtStartOfWeek) + vbMonday
Me.Requery
End Sub
=================== end code =====================
Save the form and open it. Now, when you click on one of the checkboxes,
you are actually clicking on the transparent command button that overlays
it. The Click event code examines the current state of the checkbox (does a
WorkSchedule record exist for the given date/employee or not?) and either
creates the corresponding record or deletes it. Then the form is requeries
and repositioned back to the current employee record.
If you enter a date in the header textbox, the AfterUpdate event code
ensures that it is a Monday and then requeries the form.
When the form loads, the start date is initially set to the Monday of the
next week.
Post back and tell us how you get on.
--
Good Luck
Graham Mandeno [Access MVP]
Auckland, New Zealand
SuzyQ77 said:
No VBA or VB6. I have made simple databases in the past. Perhaps
checkboxes
is the wrong choice for this new database. I can easily print off those
employees working on Monday, one for Tuesday, etc. by using "yes" as the
criteria. My problem comes when I'm trying to print off the following
report. (or query)
Monday Tuesday Wednesday Thursday Friday
Jones Jones Jones Jones
Smith
Thomas Thomas Thomas
These schedules change from week to week. When I try to print off a
report
now, I
get their names down the side and "X's" across. I need the report to look
like the one above. I don't need to deal with dates, just straight Monday
through Friday. I have also tried fields in a table (and a form created
from
the table) of Day 1, Day 2, Day 3, etc. Then in the form view, typing in
Monday, Tuesday, Wednesday, etc. Every week I go back to the form and
delete
or clear the field on the day they won't be working. I have 65 employees,
and about 30 of them work any one day. I am using an Excel worksheet
right
now and cutting and pasting their name across the spreadsheet. I need to
get
this on one page also, currently not a problem in Excel. This is not the
quickest method. So I'm open to ideas! Thanks.
