Edited record vanishes from filtered recordset (2 Viewers)

[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.

 

[tvanstiphout]

One possible solution would be to implement a MRU list like we did in Northwind 2 Developer Edition. That gives user a different way to access the 20-or so most recently edited records again.

 

[pdanes]

One possible solution would be to implement a MRU list like we did in Northwind 2 Developer Edition. That gives user a different way to access the 20-or so most recently edited records again.

That's also a good idea. I already have six command buttons with most recent six group IDs as captions, for recall of groups of records in another part of the app, but I currently have no way to recall individual recent records based on the simple fact that they had been recently edited. I do have a memory function in each of the filtering dialogs, where you can either call the filtering dialog form, or simply re-activate the most recent configuration of that filter again, but not any direct way to recall the specific records, unless the user happened to remember the specific ID.

New records generally have sequential IDs, so to find newly created ones, the user can generally filter for the largest numbers, but even that is not an iron-clad rule. And there is no way to recall recently edited records.

I've already implemented MarkK's suggestion of a control on the main form indicating which way the app should behave after closing a problematic record, which solves the primary problem of irritating the user with a popup, but there remained the secondary potential problem of having this control set the 'wrong' way in a specific situation, having the record disappear, and then having to go hunt for it. And one of the things that can and occasionally does happen is that a user will enter some info, but with an error, and is then unable to find the record, because he filters on what he thinks he entered, but actually entered something else.

An MRU control would solve both those problems at once, in a very simple fashion. Many thanks – I'm going to do this as well.

 

[DickyP]

I had a similar problem with my library database which I solved by adding a 'flag' field to each record and using the filter criteria to set the flag, then filtering on the flag setting.

Works well as long as you subsequently remember to reset the flag, of course

 

[LarryE]

And one of the things that can and occasionally does happen is that a user will enter some info, but with an error, and is then unable to find the record, because he filters on what he thinks he entered, but actually entered something else.

This sounds like a job for TempVars. Investigate how to use TempVars to keep track of the particular record even if it is "lost". With TempVars, you should be able to find it easily again.

 

[pdanes]

I had a similar problem with my library database which I solved by adding a 'flag' field to each record and using the filter criteria to set the flag, then filtering on the flag setting.

Works well as long as you subsequently remember to reset the flag, of course

It's not a regular occurrence, just something that can happen. Not a bad thought, but in my case, I think that flagging records and then having to keep track of what has been flagged would mean more work than it is worth. The MRU list should serve just as well, and older records will automatically roll off the end of the list. Appreciate the idea, though.

 

[MarkK]

And there is no way to recall recently edited records.

This is where the Modified As Date/Time field shines.

 

[pdanes]

This sounds like a job for TempVars. Investigate how to use TempVars to keep track of the particular record even if it is "lost". With TempVars, you should be able to find it easily again.

Yes, TempVars are certainly one mechanism that can be used for that. They lose their value when the app closes, though. My 'last group' caption buttons are stored in custom document properties, and repopulated when the app starts, as well as imported when a new version is distributed. I will probably go with that again, since I can see the possibility that a user may wish to find a record that he worked on yesterday, and a TempVar would lose that info.

 

[pdanes]

This is where the Modified As Date/Time field shines.

It would. I do not have that field on these records, but I'm considering adding it. I have zero issues with adequate disk space, and it would come in handy sometimes.

 

[DickyP]

It's not a regular occurrence, just something that can happen. Not a bad thought, but in my case, I think that flagging records and then having to keep track of what has been flagged would mean more work than it is worth. The MRU list should serve just as well, and older records will automatically roll off the end of the list. Appreciate the idea, though.

Actually I find having a 'flag' on a record useful quite regularly and it allows extra flexibility in my search dialogs. As for resetting I automatically clear them when I close down the databases Suits my way of working as I always have a search facility on main listing screens.

 

[pdanes]

Actually I find having a 'flag' on a record useful quite regularly and it allows extra flexibility in my search dialogs. As for resetting I automatically clear them when I close down the databases Suits my way of working as I always have a search facility on main listing screens.

Well, maybe. What criteria do you use to flag a record, and how do you deal with records that stay flagged when the app exits in an uncontrolled fashion?

 

[GPGeorge]

I am in the process of deploying a library database for a non-profit I support. We adapted the MRU feature from Northwind Developer into it. I'm hoping to start receiving feedback from the user in the next few days. It's probably too close to Christmas to expect much yet, though.

 

[pdanes]

I am in the process of deploying a library database for a non-profit I support. We adapted the MRU feature from Northwind Developer into it. I'm hoping to start receiving feedback from the user in the next few days. It's probably too close to Christmas to expect much yet, though.
View attachment 122632

That's a nice layout.