I use Delphi 10.2 with ComponentAce's Absolute Database framework. So my question here can pertain to TAbsQuery or, I presume, the conventional Delphi TQuery class.
My database has a date field named lastViewed. I have the RequestLive set to true so that any changes made to various fields in the current record are persisted to the table. Additionally, in my BeforeScroll event (i.e., prior to advancing to another record) the lastViewed field is updated to the current date. It works just fine. However, I have a menu option that displays a list of all records sorted by the last viewed date. In other words,
SELECT * FROM myTable ORDER BY lastViewed
It looks just fine when the results are initially shown in the DBGrid (TAbsGrid, in my case). But as I scroll down the list, the lastViewed field is updated and that record is pushed to the bottom of the grid. I would like the original order of the list to be preserved as I scroll up or down the list.
Turning LiveRequest to false will obviously trigger a database error when updating the DB fields in my BeforeScroll event.
The same issue would occur if, say, I want to show results for all records having the myFavoriteColor field with a RED value. Changing the myFavoriteColor value to GREEN, that row would disappear from my query results list.
Is there a way to keep the current query results in tact, while still being able to update the underlying table with new data?
Thank you for reading this.