requery_with_position_return

Sometimes we have scenarios where we want to change a record, in ways that might cause it to “shift” its logical position within a dataset, or maybe even disappear. To take an example think of dismissing a notation. To make the record disappear we need to requery the dataset. The problem is, that this causes the recordset cursor to return to the top. We can try to re-locate the record after the requery, but in this case the record is gone, so how far should we scroll down the recordset?

 

Or what if we are changing a field value of a column which we are sorting by? We might want to re-locate the record, after doing our requery. Either way, the following code should return to the same record (or spot if the record is gone). Give a go and let me have your feedback in the comments section.

 

Version 1:

And calling it is really as simple as

 

Note that instead of using ME, you can pass in a form reference if you wish to requery another form than the one running the code.

It should support most datatypes, including dates, strings and of course long (autonumber)

Advertisements
2 comments on “requery_with_position_return
  1. Giorgio says:

    So
    requery_with_position_return me,’NameOfPrimaryKeyField’ would be called by the AfterUpdate event of the field we’re updating?

    • TheSmileyCoder says:

      That could be one option. E.g. if your form is showing all Open Tasks, and you change a task status to closed (And want it to disappear from the list) you could use this to accomplish that, and in that case the afterupdate event could be used.

Ask a question, leave some feedback, post a reply!

This site uses Akismet to reduce spam. Learn how your comment data is processed.