Issue with filtering results on a form

[Aaron5714]

I have been trying to figure this out for hours; hopefully someone can point me in the right direction.

I have attached a sample of database I am working on to track contracts. Each contract can be associated with one or more "contract allocations" which specific the physical locations where the particular contract will impact. From the main form (“ContractsActive”), if you click a contract ID, the contract detail form will open and you can see the locations for that particular contract at the bottom of the form.

Here is my question: on the “ContractsActive” form I have added a combo box in the header area that is tied to the values in the Locations table. Currently, selecting a value from this combo box does nothing, but I would like to use it as a filter to limit the values in the datasheet below to only those contracts that are associated with the location selected. For example, if you select “UT Location” from the combo box, the datasheet below should only show contract IDs 1, 3, and 5, as they are the only contracts tied to that location. Then if you clear the combo box, the datasheet will show one record for each contract as it does now.



I almost got this working by adding the ContractAllocations table to the “ContractsActive” query which is the source for the form’s datasheet, then creating a macro on the After Update event of the combo box which set a filter on the LocationID field that I added to the query. The problem with that was that by adding the ContractAllocations table to the ContractsActive query, I get more than one record for each Contract ID when there are multiple locations associated with a contract. This is not a problem when a location filter is set, but I also want to be able to clear that filter on the ContractsActive form and only see one record per Contract ID, the way it looks now.

Hopefully that all makes sense. Any help would be greatly appreciated.


Thanks!
Aaron

[jzwp11]

The only way I know to do what you describe is to gather the list on contractID applicable to a certain location and then use that list as a filter for the subform. To obtain the list, you will have to open a recordset based on a query of the allocation/location tables to get the contractIDs pertinent to the selected location from the combo box. You would then loop through the recordset to gather the applicable contractIDs into a variable which you would then use to filter the subform. You did not have an option to select all locations, so I added that to the combo box. To do that, I altered the row source of the combo box to include a UNION query. I have included the necessary code in the After Update event of the location combo box in the attached DB

[Aaron5714]

jzwp11 - you are a genius. Thanks so much for your help with this!

The only way I know to do what you describe is to gather the list on contractID applicable to a certain location and then use that list as a filter for the subform. To obtain the list, you will have to open a recordset based on a query of the allocation/location tables to get the contractIDs pertinent to the selected location from the combo box. You would then loop through the recordset to gather the applicable contractIDs into a variable which you would then use to filter the subform. You did not have an option to select all locations, so I added that to the combo box. To do that, I altered the row source of the combo box to include a UNION query. I have included the necessary code in the After Update event of the location combo box in the attached DB

[jzwp11]

You're welcome.

[Aaron5714]

jzwp11: I have one more question on this. I noticed that the code you wrote for the location filter will clear/override any other filter that has already been applied on the subform. For example, if a user filters to a specific supplier or category using the built in filter options in the field headings, those filters are lost when a location filter is set from the combo box.

Is it possible to modify your code so that any other active filters remain in effect when a location is selected from the combo box?

Thanks again for your help with this!

[jzwp11]

Is it possible to modify your code so that any other active filters remain in effect when a location is selected from the combo box?

How are the other filters you mention being applied now?

I would typically include all of the filters in the header with the location combo box and then have a button and behind the button you would have code that uses all of the filter selections to filter the subform?