Need Help adding additional Criteria to Combo Box

[Swillsy]

Hi All Having problems with two combo boxes on one of my forms - It works fine in the sense that I can get one to populate the options in the second one like so.

SELECT DISTINCT Rooms.RoomNo, Rooms.RoomNo
FROM Rooms
WHERE (((Rooms.Block)=[forms]![Scheduling]![Blockcombo1].[Value])

AND (((select [Rooms].[RoomNo] from [Rooms] where not exists (select * from [Rooms] where [Rooms].[RoomID] = [Schedule Details].[roomID])))<>False)); --This is not working for me

However I really want to add an 'availiability criteria' to the box so that the combo box only displays
1) Rooms in that Block
2) Available rooms in that block

I am currently querying the availability of rooms via a 'Schedule Details' table using these queries.

SELECT [Schedule Details].RoomID, [Schedule Details].StartDate, [Schedule Details].EndDate
FROM [Schedule Details]
WHERE ((([Schedule Details].StartDate)<=[Enter End Date]) AND (([Schedule Details].EndDate)>=[Enter Start Date]));

To find which rooms are booked - followed by this

SELECT [Schedule Details].RoomID
FROM Rooms LEFT JOIN BookedRooms ON Rooms.RoomID=BookedRooms.RoomID
WHERE (((BookedRooms.RoomID) Is Null));

Could I get some help adding these together As I am Currently struggling a bit

 

[Tiro Cupidus]

Join your Availability query to your combobox selection query.

SELECT DISTINCT RoomNo
FROM Rooms INNER JOIN qry_AvailableRooms ON Rooms.RoomID = qry_AvailableRooms.RoomID
WHERE Block=[forms]![Scheduling]![Blockcombo1])