I'm using the following code to refresh the form when a button is clicked:
It works well. I discovered a "black hole" scenario and would like to know how I can modify the code so that if the FindFirst fails, it will go to the next valid/available record (based on the applied filter) on the form rather than defaulting to the first valid record on the form.
For example, table has 100 rows, form is filtered and shows 30 rows. Current record is 19 of 30. If the current record no longer meets the criteria, the form now shows 1 of 29. Ideally, would like the form to be on 19 of 29. Since we don't know which one will become record 19, how can this be done? Would I need a before update to identify the current record in the recordset, do a move next and capture the id of that record?
Here is the situation, normally, when I'm using the filter by form option from the ribbon, I filter on something that will not change. Today, I needed to add another field to the filter to exclude those records that are complete. After I mark it as complete when done, the form refreshes and since the filter excludes the complete, the form refreshes to the first available record rather than the next available record. This causes a problem because the first available record, I was unable to complete and since my workflow has been that it returns to the current record, out of habit I start working on the "next" record, which it is not and then this record falls into the black hole because the method I'm using to show the status is a cycle, each click changes the status of the record (blank, to do, completed, blank...) so when I click to fix it, it ends up being marked as complete and disappears. If I don't remember which one it was, then I have no easy way to undo.
Code:
rs.FindFirst "Subject=" & Chr(34) & stSubject & Chr(34) & " AND [DateSent]=#" & dtDateSent & "#"
Me.Bookmark = rs.Bookmark
It works well. I discovered a "black hole" scenario and would like to know how I can modify the code so that if the FindFirst fails, it will go to the next valid/available record (based on the applied filter) on the form rather than defaulting to the first valid record on the form.
For example, table has 100 rows, form is filtered and shows 30 rows. Current record is 19 of 30. If the current record no longer meets the criteria, the form now shows 1 of 29. Ideally, would like the form to be on 19 of 29. Since we don't know which one will become record 19, how can this be done? Would I need a before update to identify the current record in the recordset, do a move next and capture the id of that record?
Here is the situation, normally, when I'm using the filter by form option from the ribbon, I filter on something that will not change. Today, I needed to add another field to the filter to exclude those records that are complete. After I mark it as complete when done, the form refreshes and since the filter excludes the complete, the form refreshes to the first available record rather than the next available record. This causes a problem because the first available record, I was unable to complete and since my workflow has been that it returns to the current record, out of habit I start working on the "next" record, which it is not and then this record falls into the black hole because the method I'm using to show the status is a cycle, each click changes the status of the record (blank, to do, completed, blank...) so when I click to fix it, it ends up being marked as complete and disappears. If I don't remember which one it was, then I have no easy way to undo.