Edited record vanishes from filtered recordset (1 Viewer)

[pdanes]

This isn't really a technical question about Access, but more about design philosophy.

I have a fairly standard setup, with a set of records filtered in a continuous form, and clicking on one opens a popup dialog in which the record can be edited and re-saved. All pretty vanilla. But the main recordset can be filtered, and the record selected for editing can be changed in a way that no longer meets the filter criteria. This means that upon close and requery, that record will disappear. Sometimes users want to keep the original recordset, because they are doing things like cleaning up mistakes, and the filter was set to display the records with mistakes. As they correct the mistake and save the records, they want the record to vanish – they corrected the mistake and are no longer interested in that record. Other times, they want to still see that changed record, or see a newly created one that they created using one of the filtered ones as a template, but has been changed enough that it does not match the filtering criteria. Saving and closing makes it vanish into the luminiferous ether, and finding it again is sometimes a hassle.

The two possibilities seem about evenly split as to frequency, and the only thing I have come up with to deal with it is to check the main recordset upon closing and see if the new record will be visible. If not, I pop up a dialog asking if the user wants to continue with the old recordset or switch to see the record just being saved. It works, but I don't like it. It's an extra dialog that the user has to dismiss, and since it only shows up sometimes (when the record will not be visible), it disrupts the pace of the workflow.

I'm obviously not the first person to run into this situation. Does anybody know of a clean way to deal with this?

 

[MarkK]

You could put a "Retain Edits" checkbox on the main form, a Date/Time modified field in the RecordSource, and a filter clause sensitive to checkbox state and when the form was opened. This gives the user a low friction way to control this outcome. And for extra credit, store the Retain Edits setting as a user preference, so the next time that user opens that form, the checkbox state they prefer is retained.

 

[Mark_]

The times I've had to deal with "Keep the list you are working on", I do so by having a local table in the front end that holds the IDs of the records they need to work with. Then the query runs on this table and grabs the matching records to show. Other option for assigned records uses the same concept but has an "assigned to" in the back end so you could create a list of thousands of records and divide them up by your users.

This also works well with the concept of "Tagged" records that a user wants to deal with for ... reasons.

 

[pdanes]

You could put a "Retain Edits" checkbox on the main form, a Date/Time modified field in the RecordSource, and a filter clause sensitive to checkbox state and when the form was opened. This gives the user a low friction way to control this outcome. And for extra credit, store the Retain Edits setting as a user preference, so the next time that user opens that form, the checkbox state they prefer is retained.

Hey, that's a great idea. I could even make it a group of two toggles, one for each method of proceeding upon form close. First shipping of the new version, I would have neither selected, and not let the user edit a record until selecting a preference. That would force them to pay attention and make the choice once, but not annoy them with it every time after. Save it as a user preference, yes - I already have a number of such things - this would join them. And I could even do something like brighten the selection control when a record is saved that would now cause my closing dialog to appear. I vastly prefer making something noticeably colored, or flash briefly, over insisting the user deal with a dialog box.

Many thanks - that's exactly the kind of idea I was looking for.

 

[pdanes]

The times I've had to deal with "Keep the list you are working on", I do so by having a local table in the front end that holds the IDs of the records they need to work with. Then the query runs on this table and grabs the matching records to show. Other option for assigned records uses the same concept but has an "assigned to" in the back end so you could create a list of thousands of records and divide them up by your users.

This also works well with the concept of "Tagged" records that a user wants to deal with for ... reasons.

Thank you, but I don't think that would work well for me. These are all pretty independent users - they make their own choice about what they are working on, and there is almost zero interaction between datasets. It's a collection database for a paleontological department in a museum, and the curators each have their own records. Sometimes they work on tidying up old stuff, sometimes they create new ones, as new material is acquired or analysed. Their activities vary quite a lot, and they come up with new requirements often, generally about how to isolate and manage a specific subset of records. And in that subset, both of my scenarios happen about equally often - "Keep the list" and "Show me the changed stuff". I was looking for an unobtrusive way for each user to manage that choice, and MarkK (any relation?) suggested something I think will suit.

 

[RonPaii]

Thank you, but I don't think that would work well for me. These are all pretty independent users - they make their own choice about what they are working on, and there is almost zero interaction between datasets. It's a collection database for a paleontological department in a museum, and the curators each have their own records. Sometimes they work on tidying up old stuff, sometimes they create new ones, as new material is acquired or analysed. Their activities vary quite a lot, and they come up with new requirements often, generally about how to isolate and manage a specific subset of records. And in that subset, both of my scenarios happen about equally often - "Keep the list" and "Show me the changed stuff". I was looking for an unobtrusive way for each user to manage that choice, and MarkK (any relation?) suggested something I think will suit.

Building on Mark's suggestion; instead of opening the form with the recordset filtered to your criteria, use that criteria to fill a temp table with the ID's that match. The recordset now uses those IDs to select the records to be edited. Now when your users edit, save and requery, they see the original list of records and the changes.

 

[pdanes]

Building on Mark's suggestion; instead of opening the form with the recordset filtered to your criteria, use that criteria to fill a temp table with the ID's that match. The recordset now uses those IDs to select the records to be edited. Now when your users edit, save and requery, they see the original list of records and the changes.

Yes, that would work, but again, sometimes they do NOT want to see the records. As they make a change, they want it to vanish. The most common such scenario is the user has identified a set of records with a specific error, filtered by that error, and as he corrects the error, he is done and no longer wants to see the record. The filtered recordset visibly shrinks as those corrections are made, and when it is empty, they task is done. The other possibility is the creation of new records, which usually done by making a copy of an existing one and modifying it. Sometimes the user want to see that new record, sometimes keep the original 'template' set visible. I needed a way to handle both possibilities in a way that the user is not annoyed by constantly having click away a dialog. I'm pretty sure MarkK's suggestion will suit my needs. Thank you, though - this is also a good thought, and may well come in handy for some future situation.