Subform to be filtered on just one of it's linked fields

[Workingonit]

Hi!

I have a form [Art_DepartmentFilter] that has a subform [Art_ByRoomSubform]. The linked master/child fields are DeptID and RoomID.

There is a list box on the form that allows you to select the department, and this also updates a list box that shows the rooms in that department that have art in them.

I would like the user to be able to filter the form to show either all of the art in that department, or just the art in the specific room. I have two separate buttons, one for each filter (cmdDeptFilter, and cmdRoomFilter).

I realized I had to add the link for the RoomID in order to get the filter for the room to work (which it does nowoCmd.ApplyFilter , "[qry_artbyroom]![DeptID]=[forms]![art_departmentfilter]![textdeptid] and [qry_artbyroom]![roomid]=[forms]![art_departmentfilter]![textroomid]").

However, DoCmd.ApplyFilter , "[qry_artbyroom]![DeptID]=[forms]![art_departmentfilter]![textdeptid] and [qry_artbyroom]![roomid] like '*'" returns just the first room listed for the department, not all of the rooms.

How can I show all of the rooms for the department selected?

 

[CJ_London]

why do you have

and [qry_artbyroom]![roomid] like '*'"

I would just leave it off. if your query is still not bringing through all the rooms this implies a problem with qry_artbyroom

 

[Workingonit]

Thank you!

The same thing happens when I leave it off, it just shows the first room for the department.

I have tried to add these criteria directly to the query, and they give the proper results.

Do I have a relationship set up wrong? I have attached a snapshot of the query in design view.

 

[CJ_London]

nothing wrong with the query so far as I can see - I presume it is actually returning more than one room for the department?

I would take another look at what you say here

I have a form [Art_DepartmentFilter] that has a subform [Art_ByRoomSubform]. The linked master/child fields are DeptID and RoomID

Having seen your query I would have thought they both need to be deptID, but I may have misunderstood what you are saying

 

[Workingonit]

Yes the query shows all of the rooms for the department if I ask it to only filter for the department.

Sorry I wasn't clear about the linked fields for the subform:
The linked master fields are DeptID;RoomID
The linked child fields are DeptID;RoomID

 

[Workingonit]

Just to see what, if anything, it shows - I made the navigation buttons visible on the form. Instead of showing as one full list, the main form breaks down into "records" when the button to filter by departments is pressed. One department has 8 rooms in it, and instead of showing a list with 8 items listed, you have to navigate to the next room. Am I describing that effectively? How do I make it one continuous list?