I just finished some painting and am looking at your database.
I made a revised Click Event for the Search Button, just to see what I needed to populate the form -this is a test only.
I entered Jones in Lastname, and clicked Search.
Code:
Private Sub cmdSearch_Click()
Dim strFilter As String
If Not IsNull(Me.txtSearchLastName) Then
strFilter = strFilter & _
"([LastName] Like '" & _
Me.txtSearchLastName & "*')"
Call MsgBox(strFilter, vbOKOnly, "Debug")
'Me.frmSubFacilityMgr.Form.Filter = strFilter
'Me.frmSubFacilityMgr.Form.FilterOn = True
'Now need to find if that is lastname of a FacilityMgr and if so
'which building, then adjust the recordsource of the form and requery
Dim isFacilityMgr As String
isFacilityMgr = "SELECT Customer.LastName, Customer.CustomerID, FacilityMgr.BuildingID," _
& " Building.BuildingName, Rooms.RoomName, Rooms.SecOptionID" _
& " FROM (Building INNER JOIN (Customer INNER JOIN FacilityMgr ON " _
& " Customer.CustomerID = FacilityMgr.CustomerID) ON Building.BuildingID = FacilityMgr.BuildingID)" _
& " INNER JOIN Rooms ON Building.BuildingID = Rooms.BuildingID " _
& " WHERE Customer.LastName Like '" & Me.txtSearchLastName & "*';"
Debug.Print isFacilityMgr
Me.RecordSource = isfaciityMgr
Me.Requery
End If
End Sub
This gives me the output in the jpg
Note; There are 6 records--Was there something to display for these???
I think it's time to review POC and Facility Mgr --exactly what each is; how they are related; responsibilities...