Sorry to be picky but you refer to ID's when your tables use PK/FK - I presume these are PK's?
If so, then I presume the recordsouce for the mainform is
Code:
SELECT roomsPK, BuildingFK, RoomName, BuildingName
FROM tblRooms INNER JOIN tblBuilding on tblrooms.BuildingFK=tblBuilding.BuildingPK
And basically that will produce a (potentially) multiple row result based on a customer search filter from your five unbound combo's?
I also presume each of your combos rowsource include the PK as the bound source e.g.
Code:
SELECT *
FROM tblOrganisation
SELECT CustomerPK, FirstName
FROM tblCustomers
etc
If this is the case I would modify your code to
Code:
SELECT roomsPK, BuildingFK, RoomName, BuildingName, tblCustomers.*
FROM ((tblRooms INNER JOIN tblBuilding on tblrooms.BuildingFK=tblBuilding.BuildingPK) INNER JOIN tblRoomsPOC ON tblRooms.PK=thbRoomsPOC.RoomsFK) INNER JOIN tblCustomer ON tblCustomer.CustomerPK=tblRoomPOC.CustomerFK
then your vba code would become
Code:
me.filter="CustomerFK=" & cboFirstName & " OR CustomerFK=" & cboLastName & " OR OrganisationFK=" & cboOrganisation & " OR .....
me.filteron=true
I need to be able to display Building Name and Room name in the main form
As explained before and as you recognise, if a customer has more than one room then this will be a multiple return - you can only display one room per page so will need to go to next record to see the next room - you can't use subforms in a continuous form.
Whether you go for my simplified suggestion or stick with yours, the result is the same - multiple returns. You understand this but the filtering will not reduce this to a single record unless you also include a filter for the roomPK - hence my suggestion in the previous post which is to make the main form unbound and have another subform to display the records instead.