A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Combo box won't display correctly



 
 
Thread Tools Display Modes
  #1  
Old October 22nd, 2008, 05:43 PM posted to microsoft.public.access.tablesdbdesign
Pete
external usenet poster
 
Posts: 384
Default Combo box won't display correctly

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.
  #2  
Old October 22nd, 2008, 06:00 PM posted to microsoft.public.access.tablesdbdesign
Pete
external usenet poster
 
Posts: 384
Default Combo box won't display correctly

The tables in my initial post should read:-

tblBankStaff
BS_ID (1 to ∞ with tblBS_Availability)
BS_Fname
BS_Lname

tblBS_Availability
BS_ID
DateFrom
DateTo

tblCareHomes
CH_ID (1 to ∞ with tblCH_Shifts)
Name
Location

tblCH_Shifts
Shift_ID
CH_ID
BS_ID
DateFrom
DateTo
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:24 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.