query for cascade combo boxes (1 Viewer)

eugzl

Member
Local time
Today, 01:28
Joined
Oct 26, 2021
Messages
35
I create query for cascade combo boxes:
SQL:
SELECT tblLocations.LocationID, tblLocations.FacilityID, tblLocations.BuildingID, tblLocations.WingID, tblLocations.FloorID, tblFacilities.Facility & " " & tblBuildings.Building & " " & tblWings.Wing & tblFloors.Floor & "-" & tblLocations.Room AS Location
FROM tblWings INNER JOIN (tblFloors INNER JOIN (tblFacilities INNER JOIN (tblBuildings INNER JOIN tblLocations ON tblBuildings.BuildingID = tblLocations.BuildingID) ON tblFacilities.FacilityID = tblLocations.FacilityID) ON tblFloors.FloorID = tblLocations.FloorID) ON tblWings.WingID = tblLocations.WingID
WHERE (((tblLocations.FacilityID)=[Forms]![fRequest]![cboFacility]) AND ((tblLocations.BuildingID)=[Forms]![fRequest]![cboBuilding]) AND ((tblLocations.WingID)=[Forms]![fRequest]![cboWing]) AND ((tblLocations.FloorID)=[Forms]![fRequest]![cboFloor]));
The criteria in this query include parameters: Facility, Building, Wing, Floor. How to modify the query if not all Facilities have Buildings with Wing? How to keep cascade boxes workable in case if will skipped Wing value?
 
Last edited:

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 22:28
Joined
Aug 30, 2003
Messages
35,522
Well, the technique above should "keep cascade boxes workable in case if will skipped Wing value". If you want some sort of dynamic method where some facilities need wing and some don't, you'll need to incorporate that into your data somehow. Personally I'd build the SQL in code, which would let you incorporate that logic easier (I suspect).
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:28
Joined
Feb 19, 2002
Messages
34,345
The simplest method is to use "dummy" values so that every facility has at least one "wing". Otherwise, you will need to change the joins from inner to LEFT
 

Users who are viewing this thread

Top Bottom