locked
Retain position on continuous form through requery RRS feed

  • Question

  • I have a continuous form that, for reasons of efficiency, must use an underlying query that groups records and provides totals. Therefore, the underlying recordset is (correctly) not updateable. However, I do need to flip the value of one of the (header) fields from True to False or False to True. So I have a button in the detail section of the form that checks the current value, creates the SQL statement to change the value, and runs the SQL statement.

    So far, so good. Now, to get the new data to appear, I must requery the form, but that, of course causes me to lose my position on the continuous form.

    So I set a variable to the value of the current PK in the header table before my requery, then use Bookmark to return to the record that was current before the update & requery. Again, so far, so good...except for one thing.

    The user may have the current record in any vertical position on the visible part of the list. That is, the record being edited could be at the top of the visible records, the second one down, etc, or even the last record visible (I have a vertical scroll bar). Using Bookmark returns me to that record, but it automatically places that current record at the top of the visible portion of the record list (form's detail section) after the requery. This is quite confusing for the users, and they can easily lose their places.

    Is there a way, not only to return the current record after the requery, but to return that record to the same vertical position within the form's records in the detail section so that there is no apparent jumping around?


    Tuesday, January 27, 2015 6:12 PM

Answers

  • Yes and sorry for the late reply only just came across this and just looking out for new devs happening by this issue

    Stop trying to Requery your Form and Requery the underlying RecordSet.

    The Form's data is what you want to Requery so just to that:

      Forms!frm_Name.Form.RecordSet.Requery

    Or if you are in the form or a continuous subform and updating with a query afterupdate for example.

      Me.RecordSet.Requery 

    Lebans get record scrollbar controller is a sledgehammer approach to a misinterpretation of what developers are trying to do I mean it doesn't even work 100% right and batters loading times.

    You are looking at the recordset to update not the form so do that.

    Me.RecordSet.Requery

    The form will auto refresh as that is the property of a requery but the form will not change scroll location.

    Note that if someone adds 50 records (unlikely) it won't be perfectly aligned but the user will still have focus on the current record so it's not a problem they type away and the record snaps to view.





    • Edited by Xyloz_Quin Sunday, December 13, 2020 6:06 AM
    • Proposed as answer by Dirk Goldgar Sunday, December 13, 2020 5:05 PM
    • Marked as answer by Brian D. Hart Sunday, December 13, 2020 7:28 PM
    Sunday, December 13, 2020 5:59 AM

All replies

  • I think you could use the tecniques and code in Stephen Lebans's SetGetScrollbars sample database to do this.  Check out his page here:

        http://www.lebans.com/setgetsb.htm
        SetGetSB.zip is a database containing functions to allow a user to Set or Get the current position of a ScrollBar Thumb for a Form.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by Caillen Tuesday, February 3, 2015 12:49 PM
    • Unmarked as answer by Brian D. Hart Sunday, December 13, 2020 7:29 PM
    Tuesday, January 27, 2015 7:42 PM
  • Hi Brian D. Hart,

    In early 2000s I published the solution here:

    http://am.rusimport.ru/MsAccess/topic.aspx?ID=24

    That sample isn't English localized. That's why I redesigned it for you. You can download the ajusted version from here:

    https://www.dropbox.com/s/521xxb81ubgh2bc/VakshulRequeryEN2010.accdb?dl=0

    By the way, the key moment of it was discussed early here:

    https://social.msdn.microsoft.com/Forums/office/en-US/102244b7-9ae7-446e-9d31-b23eda5f758b/how-to-determine-the-number-of-records-displayed-in-a-continuous-form?forum=accessdev

    Sergiy Vakshul

    • Proposed as answer by Sergiy_Vakshul Tuesday, February 3, 2015 10:39 PM
    • Unproposed as answer by Brian D. Hart Sunday, December 13, 2020 7:29 PM
    Tuesday, February 3, 2015 10:38 PM
  • Thank you. I have downloaded that but just have not yet had time to get back to that project to test it, but it does look as though it does exactly what I need.
    Wednesday, February 4, 2015 1:27 AM
  • Sergiy: I got the file downloaded. Thank you very much.
    Wednesday, February 4, 2015 1:29 AM
  • Brian,

    Were you able to obtain the desired out come? I am also search for a solution to this type of problem. I have a Live Schedule that re queries every 90 seconds from timer. When the days schedule is longer and users scroll down to see the rest of the schedule after 90 seconds it pops up to the first record. So I want what you are asking of keeping the vertical on the page as well not popping to a record. I downloaded Sergiy's file but I'm unable to see figure out how to incorporate it into my db. I also looked at the answer of lebans, but that hasn't worked either, maybe I'm looking at this wrong. I would appreciate any help you could provide. 

    Thank you

    Rob


    • Edited by Rob Hattala Tuesday, October 20, 2015 10:05 PM
    Tuesday, October 20, 2015 10:03 PM
  • Hi Rob,

    Regarding my method.

    All you need is a RequeryPro function. Copy its code to your appliction. Use it instead of a built-in Requery method.

    The key moment of this function is:

        If strSQL <> "" Then
            frm.RecordSource = strSQL
        Else
            frm.Requery
        End If

    It's up to you to decide what to use: frm.Requery or frm.RecordSource = strSQL


    Sergiy Vakshul



    Tuesday, October 20, 2015 11:25 PM
  • The Lebans example will not open at all with the newer MS Access. Can someone post the content of the code that enables a form to keeps its position after a requery? I am running sql in the BE and am using a pass through query so my form works better with remote users and having the form jump after an update is beyond aggravating to the users. Thanks! Refresh does not work with a record source that is a pass through query.
    Monday, February 18, 2019 10:52 PM
  • The Lebans example will not open at all with the newer MS Access. Can someone post the content of the code that enables a form to keeps its position after a requery? I am running sql in the BE and am using a pass through query so my form works better with remote users and having the form jump after an update is beyond aggravating to the users. Thanks! Refresh does not work with a record source that is a pass through query.

    I just opened his sample database in Access 2016, and it worked fine.  That was 32-bit Access 2016 under 64-bit Windows 10.  Are you running 64-bit Access?  I don't know if that would break it.

    I have not tried it yet with a form based on a pass-through query.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Monday, February 18, 2019 11:05 PM
  • Yes and sorry for the late reply only just came across this and just looking out for new devs happening by this issue

    Stop trying to Requery your Form and Requery the underlying RecordSet.

    The Form's data is what you want to Requery so just to that:

      Forms!frm_Name.Form.RecordSet.Requery

    Or if you are in the form or a continuous subform and updating with a query afterupdate for example.

      Me.RecordSet.Requery 

    Lebans get record scrollbar controller is a sledgehammer approach to a misinterpretation of what developers are trying to do I mean it doesn't even work 100% right and batters loading times.

    You are looking at the recordset to update not the form so do that.

    Me.RecordSet.Requery

    The form will auto refresh as that is the property of a requery but the form will not change scroll location.

    Note that if someone adds 50 records (unlikely) it won't be perfectly aligned but the user will still have focus on the current record so it's not a problem they type away and the record snaps to view.





    • Edited by Xyloz_Quin Sunday, December 13, 2020 6:06 AM
    • Proposed as answer by Dirk Goldgar Sunday, December 13, 2020 5:05 PM
    • Marked as answer by Brian D. Hart Sunday, December 13, 2020 7:28 PM
    Sunday, December 13, 2020 5:59 AM
  • Hi

    You need to use a PIVOT. You can use either a STATIC PIVOT where you know the values of the columns to transform or a DYNAMIC PIVOT where the columns are unknown until execution time.

    ----Static Pivot----

    select *
    from 
    (
        select memid, Condition_id, Condition_Result
        from t
    ) x
    pivot
    (
        sum(condition_result)
        for condition_id in ([C1], [C2], [C3], [C4])
    ) p

    Best regards.

    Please click the Mark as answer button and vote as helpful if this reply solves your problem.

    Sunday, December 13, 2020 6:15 AM
  • I simply use https://www.devhut.net/2012/10/19/ms-access-vba-requery-a-form-while-remaining-on-the-same-record/

    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Sunday, December 13, 2020 1:32 PM
  • Stop trying to Requery your Form and Requery the underlying RecordSet.

    The Form's data is what you want to Requery so just to that:

      Forms!frm_Name.Form.RecordSet.Requery

    Or if you are in the form or a continuous subform and updating with a query afterupdate for example.

      Me.RecordSet.Requery 

    This is a terrific answer!  It's a technique that never occurred to me, and it's embarrassingly simple. It should definitely replace my accepted answer.  Thank you.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Sunday, December 13, 2020 5:04 PM
  • OP here. Yes, indeed, this is a simple answer. In my 20 years of working with Access, it never occurred to me to requery the recordset, or even that it was possible. I remember encountering the original problem but have long since forgotten which application this was in, and I cannot recall what solution I ended up using. However, I just tested this approach in one of my DB's and it works beautifully.


    It is never too late to learn something new!


    Sunday, December 13, 2020 7:45 PM
  • There is a much simple solution.

    In the click event of the button

    Private Sub Command_Click()

    Dim CurrentPosition as Variant

    CurrentPosition = Me.BookMark

    'Your Code here : e.g. Me.Requery

    Me.BookMark = CurrentPosition

    End Sub


    Monday, December 14, 2020 7:56 AM
  • No, that is incorrect. OP here.

    Read my initial post at the top of this thread, where I explain that I excluded that as a solution before I posted. While that correctly returns one to the same record, it does not keep the current record in the same screen position; it always moves the current record (Bookmark) after the requery to the top of the list. The user may be down in the middle of the form, 20 records from the top record on the list, when making the change, and I need to keep it that way. From my original post:

    "So I set a variable to the value of the current PK in the header table before my requery, then use Bookmark to return to the record that was current before the update & requery....using Bookmark returns me to that record, but it automatically places that current record at the top of the visible portion of the record list (form's detail section) after the requery. This is quite confusing for the users, and they can easily lose their places."

    The goal was not simply to find the record again, which, as you point out, is a simple matter. The goal was to ensure that this current record was not automatically scrolled to the top of the list. And that is what Me.Recordset.Requery does that Me.Requery cannot. It leaves the active record in the same screen position.


    Monday, December 14, 2020 8:09 AM
  • No, that is incorrect. OP here.

    Read my initial post at the top of this thread, where I explain that I excluded that as a solution before I posted. While that correctly returns one to the same record, it does not keep the current record in the same screen position; it always moves the current record (Bookmark) after the requery to the top of the list. The user may be down in the middle of the form, 20 records from the top record on the list, when making the change, and I need to keep it that way. From my original post:

    "So I set a variable to the value of the current PK in the header table before my requery, then use Bookmark to return to the record that was current before the update & requery....using Bookmark returns me to that record, but it automatically places that current record at the top of the visible portion of the record list (form's detail section) after the requery. This is quite confusing for the users, and they can easily lose their places."

    The goal was not simply to find the record again, which, as you point out, is a simple matter. The goal was to ensure that this current record was not automatically scrolled to the top of the list. And that is what Me.Recordset.Requery does that Me.Requery cannot. It leaves the active record in the same screen position.


    Interesting
    Tuesday, December 15, 2020 11:35 AM