ComboBox only display available items

[detrie]

Hello All...
I could use a jumping off point.

I have a table “tblResrvations”
I have a table “tblEquipment”

I have a form “frmReservations” with a subForm “frmSubEquipment”
frmReservations includes 2 fields, FromDate and ThruDate

“frmSubEquipment” has a ComboBox “cmbEquipment”

When I create a new reservation, I first enter the FromDate and ThruDate.
I need “cmbEquipment” to only display equipment that is not “reserved” (that is to say where the equipment is not committed on or between FromDate and ThruDate.

TIA
Detrie

 

[KeithG]

The below SQL will should display all Equipment that is not booked between the two dates. I was not sure of your column names so you will need to adjust it to suite your needs. Also I made the query a parameter query but you can reference the controls you input your dates to instead. Let me know if it works.


SELECT EquipmentID,EquipmentName FROM tblEquipment
WHERE EquipmentID Not IN (SELECT EquipmentID FROM tblEquipment INNER JOIN tblReservations ON tblEquipment.EquipmentID=tblReservations.EquipmentID WHERE DateFrom>=[FromDateParameter] AND DateTo<=[ToDateParameter])