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)

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

  2. memphismaven says:

    I am so glad you are back!! Your posts are incredibly helpful.

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

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