I'm getting closer.
Seems the reason it wasn't working is because I cannot have two separate SQL statements. If I'm assigning the recordsource to sqlSearch, it will use only one query. So, it seems I need a union query.
I'm not good with union queries. I wrote this:
Code:
Private Sub cmdSearch_Click() Dim sqlSearch As String
If Not IsNull(Me.cboSearchLastName) Then
sqlSearch = "SELECT tblCustomer.LastName, tblFacilityMgr.CustomerFK, tblFacilityMgr.BuildingFK, tblRooms.BuildingFK" _
& " FROM (tblBuilding INNER JOIN tblRooms ON tblBuilding.BuildingPK = tblRooms.BuildingFK) INNER JOIN" _
& " (tblCustomer INNER JOIN tblFacilityMgr ON tblCustomer.CustomerPK = tblFacilityMgr.CustomerFK) ON tblBuilding.BuildingPK = tblFacilityMgr.BuildingFK" _
& " WHERE LastName ='" & Me.cboSearchLastName & "'""" _
& " UNION ALL SELECT tblCustomer.LastName, tblRoomsPOC.CustomerFK, tblRooms.RoomsPK, tblRooms.BuildingFK" _
& " FROM (tblRooms INNER JOIN tblRoomsPOC ON tblRooms.RoomsPK = tblRoomsPOC.RoomsFK) INNER JOIN" _
& " (tblCustomer INNER JOIN tblRoomsPOC ON tblCustomer.CustomerPK = tblRoomsPOC.CustomerFK) ON tblRooms.RoomsPK = tblRoomsPOC.RoomsFK" _
& " WHERE LastName ='" & Me.cboSearchLastName & "'"
End If
Me.RecordSource = sqlSearch
End Sub
It says invalid syntax. I'm not sure what I did wrong. I copied the SQL statement down and created a query for it. If I remove ALL so that its just "UNION SELECT" then the query works without a problem. However if I use "UNION ALL" in vba, it says unsupported join expression. So I'm guessing UNION ALL doesn't work in vba. Regardless, do I have a problem with the " _ & " between each line?
Edit: I fixed my syntax error, now it says "Join expression not supported." I googled this, and found nothing good. It looks like vba doesn't support "complex queries"??? Is there a work-around?
Fixed it! I had to build the queries separately to find out what I was doing wrong.
Code:
Private Sub cmdSearch_Click()Dim sqlSearch As String
If Not IsNull(Me.cboSearchLastName) Then
sqlSearch = "SELECT tblCustomer.LastName, tblFacilityMgr.CustomerFK, tblFacilityMgr.BuildingFK, tblRooms.RoomsPK" _
& " FROM (tblBuilding INNER JOIN tblRooms ON tblBuilding.BuildingPK = tblRooms.BuildingFK) INNER JOIN" _
& " (tblCustomer INNER JOIN tblFacilityMgr ON tblCustomer.CustomerPK = tblFacilityMgr.CustomerFK) ON" _
& " tblBuilding.BuildingPK = tblFacilityMgr.BuildingFK" _
& " WHERE LastName ='" & Me.cboSearchLastName & "'" _
& " UNION SELECT tblCustomer.LastName, tblRoomsPOC.CustomerFK, tblRooms.BuildingFK, tblRoomsPOC.RoomsFK" _
& " FROM tblRooms INNER JOIN (tblCustomer INNER JOIN tblRoomsPOC ON tblCustomer.CustomerPK = tblRoomsPOC.CustomerFK)" _
& " ON tblRooms.RoomsPK = tblRoomsPOC.RoomsFK" _
& " WHERE LastName ='" & Me.cboSearchLastName & "'AND"
End If
If Not IsNull(Me.cboSearchLastName) Then
sqlSearch = "SELECT tblCustomer.FirstName, tblFacilityMgr.CustomerFK, tblFacilityMgr.BuildingFK, tblRooms.RoomsPK" _
& " FROM (tblBuilding INNER JOIN tblRooms ON tblBuilding.BuildingPK = tblRooms.BuildingFK) INNER JOIN" _
& " (tblCustomer INNER JOIN tblFacilityMgr ON tblCustomer.CustomerPK = tblFacilityMgr.CustomerFK) ON" _
& " tblBuilding.BuildingPK = tblFacilityMgr.BuildingFK" _
& " WHERE LastName ='" & Me.cboSearchFirstName & "'" _
& " UNION SELECT tblCustomer.FirstName, tblRoomsPOC.CustomerFK, tblRooms.BuildingFK, tblRoomsPOC.RoomsFK" _
& " FROM tblRooms INNER JOIN (tblCustomer INNER JOIN tblRoomsPOC ON tblCustomer.CustomerPK = tblRoomsPOC.CustomerFK)" _
& " ON tblRooms.RoomsPK = tblRoomsPOC.RoomsFK" _
& " WHERE LastName ='" & Me.cboSearchFirstName & "'"
End If
Me.RecordSource = sqlSearch
End Sub