-----Original Message-----
I am working on on off duty police database. I have the
hours the officers work. When the reservation date(s)
date is entered, I would like the drop down officer list
to list just the officers available. How anyone done
something similiar and can share the solution?
Thank you.
.
Hi Tammie,
please indicate how availability is determined.
Something to consider is that if you have a filter on a
combobox/listbox that for example excludes officers
assigned at that time, it will not show the officer for
the current record once the officer has been assigned. You
will have to consider using a separate form or equivalent
to list available officers. Making a selection
updates/edits the current record.
Are there date and time fields for start and end of shifts?
if 'yes' then use a reference to these fields in the row
source of the combobox/listbox.
For example (you will need to replace names with those
that you are using)... 2 forms,
Main Form....
frmRoster
with fields...
txtStaffID
txtStartDate
txtStartTime
txtEndDate
txtEndtime
(need to consider shift extending into another day)
cmdSelectOfficer
Other Form...
frmAvailibleStaff
with fields...
lstAvailable
cmdAdd
The cmdSelectOfficer_OnClick() event will open the form,
frmAvailableStaff
docmd.openform
formname:="frmAvailableStaff",WindowMode:=acDialog
name of query that is row source of listbox, lstAvailable
is qryAvailableStaff. This query will have fields that
include StaffID, StaffName and existing roster dates and
times. Though only showing StaffName.
The criteria row for each date and time to refer to the
appropriate control on the form, frmRoster. For example,
the field StartTime will have the criteria
<=frmRoster!txtStartTime
the field StartDate will have the criteria
<=frmRoster!txtStartDate
When you click the cmdAdd button it will assign the
selected OfficerID to the main form. For example,
frmRoster!txtStaffID=lstAvailable
Well, hope the above helps
Luck
Jonathan
