P
Pete
I am developing an app to manage care homes where there will be shifts
needing to be covered by Bank Staff members who will cover when the usual
members of staff cannot. The app should allow input for Care Home names and
Shift dates and times, and also Bank Staff names and their availability –
again with dates and times.
Once a shift is defined, the user should then select from a list of Bank
Staff members where their availability falls within the dates and times of
the shift being assigned a Bank Staff member.
I have begun with the following tables:-
tblBankStaff tblBS_Availability
BS_ID 1 to ∞ BS_ID
BS_Fname DateFrom
BS_Lname DateTo
tblCH_Shifts
tblCareHomes Shift_ID
CH_ID 1 to ∞ CH_ID
Name BS_ID
Location DateFrom
DateTo
The problem: I have a master/detail form – linked by CH_ID :-
Main form: Record Source: tblCareHomes
Sub form: Record Source: tblCH_Shifts
I am unable to make the subform display the previously allocated Bank Staff
correctly, using a combo box.
The subform’s Default View is set to ‘Continuous Forms’.
I have a combo box on the main form which lists all, and then finds, the
selected record (Care Home) when clicked. The subform then lists (filters)
all the previously entered shifts for the selected Care Home. The subform
holds the DateFrom & DateTo fields and a combo box, bound to the BS_ID field,
with the following attributes:-
Row Source: SELECT tblBankStaff.BS_ID, tblBankStaff.BS_Fname+" "+[BS_Lname]
AS Name
FROM tblBankStaff INNER JOIN tblBS_Availability ON
tblBankStaff.BS_ID = tblBS_Availability.BS_ID
WHERE
(((tblBS_Availability.DateForm)<=[Forms]![frmCH_Shifts].
[subfrmCH_Shifts]![txtDateFrom]) AND
((tblBS_Availability.DateTo)>=[Forms]![frmCH_Shifts].
[subfrmCH_Shifts]![txtDateTo]));
The combo appears on every record and will return records (Bank Staff) where
their availability dates fall within the Shift dates on the subform.
The app is allocating the correct BS_ID in the tbl_Shifts table, but will
not display them correctly in the actual subform where they are allocated.
They seem to either not display of flicker when clicking in the combo on
different records in the continuous subform.
Is this because the Row Source of the combo box is returning differing Bank
Staff members for each record - which is dependent on the Shift dates?
If so, how can I get around the problem to fulfil the objective outlined in
the opening paragraph above?
Many thanks.
needing to be covered by Bank Staff members who will cover when the usual
members of staff cannot. The app should allow input for Care Home names and
Shift dates and times, and also Bank Staff names and their availability –
again with dates and times.
Once a shift is defined, the user should then select from a list of Bank
Staff members where their availability falls within the dates and times of
the shift being assigned a Bank Staff member.
I have begun with the following tables:-
tblBankStaff tblBS_Availability
BS_ID 1 to ∞ BS_ID
BS_Fname DateFrom
BS_Lname DateTo
tblCH_Shifts
tblCareHomes Shift_ID
CH_ID 1 to ∞ CH_ID
Name BS_ID
Location DateFrom
DateTo
The problem: I have a master/detail form – linked by CH_ID :-
Main form: Record Source: tblCareHomes
Sub form: Record Source: tblCH_Shifts
I am unable to make the subform display the previously allocated Bank Staff
correctly, using a combo box.
The subform’s Default View is set to ‘Continuous Forms’.
I have a combo box on the main form which lists all, and then finds, the
selected record (Care Home) when clicked. The subform then lists (filters)
all the previously entered shifts for the selected Care Home. The subform
holds the DateFrom & DateTo fields and a combo box, bound to the BS_ID field,
with the following attributes:-
Row Source: SELECT tblBankStaff.BS_ID, tblBankStaff.BS_Fname+" "+[BS_Lname]
AS Name
FROM tblBankStaff INNER JOIN tblBS_Availability ON
tblBankStaff.BS_ID = tblBS_Availability.BS_ID
WHERE
(((tblBS_Availability.DateForm)<=[Forms]![frmCH_Shifts].
[subfrmCH_Shifts]![txtDateFrom]) AND
((tblBS_Availability.DateTo)>=[Forms]![frmCH_Shifts].
[subfrmCH_Shifts]![txtDateTo]));
The combo appears on every record and will return records (Bank Staff) where
their availability dates fall within the Shift dates on the subform.
The app is allocating the correct BS_ID in the tbl_Shifts table, but will
not display them correctly in the actual subform where they are allocated.
They seem to either not display of flicker when clicking in the combo on
different records in the continuous subform.
Is this because the Row Source of the combo box is returning differing Bank
Staff members for each record - which is dependent on the Shift dates?
If so, how can I get around the problem to fulfil the objective outlined in
the opening paragraph above?
Many thanks.