Filtering Combo box From Another Combo box based on a Multi-valued Field

[fitzdesignz]

I have a form where among other things two combo boxes exist. One is cboProcedureRoom and cboAppointmentTime. I want to limit the values in cboAppointmentTime based on the selection in cboProcedureRoom.



This query is placed in Row Source:

SELECT tbl_AppointmentTimes.ApptTimes, tbl_AppointmentTimes.Room 
FROM tbl_AppointmentTimes 
WHERE (((tbl_AppointmentTimes.Room) In (Forms!frm_ScheduleApptEdit!cboProcedureRoom)));

tbl_AppointmentTimes.ApptTimes is a standard single value field with times listed in increments of 30 minutes ( 8:00, 8:30, 9:00 etc..)
tbl_AppointmentTimes.Room is is a multivalued field that indicates which room should be used for that time slot.

tbl_AppointmentTimes
TimeID -----ApptTime-----Room
1 --------------8:00------- 1,2,3,4,5
2 --------------8:30-------
3 --------------9:00------- 4
4 --------------9:30------- 1,2,3,5
5 --------------10:00------- 4

Basically rooms 1,2,3 and 5 schedule every 90 mins and room 4 schedules every 60 mins

On my schedule appointment form I want the cboAppointmentTime to limit the results to the times available to that room. So if the user selects room 2 from the cboProcedureRoom the only options in the cboAppointmentTime is 8:00 and 9:30 or if they select room 4 they get 8:00, 9:00 and 10:00 as options.


If I change the multivalue field to a single value field this works as it should. but then I'm only limited to one time slot per room. : (

I'm new to access so please accuse me if my terminology is off.

[isladogs]

Although I wouldn't often suggest this you could use five Boolean fields Room1, Room2 etc in place of your MVF Room field.
Or better still, just have 2 Boolean fields: Room4 and Other
Set the values true for the times where each room has appointments.
In the after update event for your room combo, set the row source for the other combo using a select case set of statements.

It's a unnormalised solution which is why I wouldn't normally suggest it, but better than a MVF and perhaps ok here.

[davegri]

Multivalued fields generally can be replaced by a Many-to-Many relationship setup.
Attached is a DB with a table for rooms, a table for appointment times and a junction table to join them.
The scheduling form has a dropdown for the room number, and side by side listboxes to assign appointment times to the chosen room.
Also included are a couple of reports to show the scheduling.

MTM  Schedule Rooms-davegri-v01.zip

The db does not account for overlap if scheduling can begin on the half hour.
If you don't schedule on the half-hour, just delete those rows in the AppointmentTimes table and all is well.
If you do schedule on the half hour, the DB needs modification to forbid overlapping times within a room for the 90 minute rooms.