OK, time for another sub query. In this case the sub query selects all LevelID's from the junction table where the DoorID is not equal to the single door selected in the list box. So, for example, if we have the following data in the junction table;
Code:
LevelID DoorID
1 1
1 2
2 1
In the above case the sub query would select Level1 but would not select Level2
The outer query then selects LevelID's from the junction table that are not in the sub query. You could do this with a Not In clause, but I have chosen to do it here with a Left Join using Null criteria as a demonstration of another way it can be done. Sometimes (not in this case but in some cases) this is easier than building the criteria for a Not In clause so this is just an example. The modified code is below. The only thing that has changed is the SQL string for the first case in the Select Case statement. I have highlighted the section in red.
Code:
Private Sub cmdSearch_Click()
Dim intCount As Integer
Dim lngDoorID As Long
Dim strDoors As String
Dim strFilter As String
Dim strSQL As String
Dim vItem As Variant
Dim blnNoRecords As Boolean
'Determine how many Doors were selected
intCount = Me.lstDoors.ItemsSelected.Count
If intCount = 0 Then 'No Doors selected
MsgBox "Please select at least one Door."
Else
Select Case intCount
Case 1 '1 Door selected
'Return the DoorID
For Each vItem In Me.lstDoors.ItemsSelected
lngDoorID = Me.lstDoors.ItemData(vItem)
Next
'Query with a subquery to select Levels that
'are related to only this Door and no others
strSQL = "SELECT tblDoorLevels.LevelID " _
& "FROM tblDoorLevels LEFT JOIN " _
& "(SELECT tblDoorLevels.LevelID " _
& "FROM tblDoorLevels " _
& "WHERE tblDoorLevels.DoorID <> " & lngDoorID & ") AS T " _
& "ON tblDoorLevels.LevelID = T.LevelID " _
& "WHERE T.LevelID Is Null;"
Case Is > 1 'Multiple Doors selected
'Build a comma separated string of DoorIDs
For Each vItem In Me.lstDoors.ItemsSelected
strDoors = strDoors & Me.lstDoors.ItemData(vItem) & ", "
Next
'Remove the trailing comma & space
strDoors = Left(strDoors, Len(strDoors) - 2)
'Query with a subquery to select Levels where the Count of records
'equals the number of Doors selected in the list box
strSQL = "SELECT T.LevelID FROM " _
& "(SELECT tblLevels.LevelID FROM tblLevels " _
& "INNER JOIN tblDoorLevels ON tblLevels.LevelID = tblDoorLevels.LevelID " _
& "WHERE tblDoorLevels.DoorID In (" & strDoors & ")) AS T " _
& "GROUP BY T.LevelID " _
& "HAVING Count(T.LevelID)=" & intCount
End Select
'See if the query returns anything
With CurrentDb.OpenRecordset(strSQL)
If .RecordCount = 0 Then 'No records found
MsgBox "There are no Levels that match the Doors selected."
Else 'Records found
'Place the results of the query in a filter
strFilter = "LevelID In(" & strSQL & ")"
'Apply the filter to the form
Me.Filter = strFilter
Me.FilterOn = True
End If
End With
End If
End Sub
So if you select a single Door it will return Levels that are associated with only that Door (if there are any). If you select multiple Doors it will only return Levels that are associated with all Doors selected.
HTH