Solved: How to capture a user generated form filter

[HokieJoe]

I'm trying to allow a user to drill down to a selection of records using the individual drop-down filters in the datasheet view of a split form, and from there make modifications to just the filtered records using VBA code. I'm a novice, and I can't figure out how to use code to save the manual filter choices. When I open and edit the recordset based on the form's recordsource query, it edits all the records in the query, not just the filtered set. I think I need to save the filtered version of the query behind the form before I can work with just those records, but I don't know how to do it with code.:help

[geekgirlau]

To capture the current filter, you should be able to use

strFilter = Me.Filter

You can then create an update query using the filter. For example,

strSQL = "UPDATE MyTable " & _
"SET Field1 = 1 " & _
"WHERE " & strFilter

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

[CreganTur]

geekgirl - I think the OP wants to keep a historical record of filters entered by the user.

I would suggest creating a table that will hold these filter values- be sure to set the filter values as a primary key so you don't get duplicates clogging it up.

Set a combobox on your Form that pulls the values from this table, and make sure Limit To List = False; that way the user can type in new values. You may need to setup an update query to load new filter options into the table.

HTH:thumb

[HokieJoe]

Thanks geekgirlau and CreganTur! The Me.filter is just what I needed since I don't need to keep a record of the filters. The point is for the user to be able to choose the group of records by any filter or group of filters, then update those records. We are tracking the installation of instruments on a construction project. As a group of instruments are received, installed, wired, calibrated, etc., the progress can be updated for the selected group, then the filters deleted. Thanks again for the help.