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:
Code:
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.