Continuous form requery return scroll position?? (1 Viewer)

[burnfly]

OK this is one which I'm sure has bugged loads of people, when you requery a continuous form, the scroll bar position goes back to the top after the requery.

Does anyone know a way to return to the same scroll position from before the requery was performed?

TIA!

 

[Beetle]

Flag the current record's ID value before the requery, then after the requery find the bookmark in the recordsetclone and set your form's bookmark back to where it was. Something like (air code);

Dim vFlag

vFlag = Me![PKField]

Me.Requery

With Me.RecordsetClone
    .FindFirst ![PKField] = vFlag
    Me.Bookmark = .Bookmark
End With

 

[burnfly]

Thanks for your reply. I'm afraid I'm a bit of a novice regarding code. Would this code be placed somewhere in the "events" properties tab of the form?

 

[Beetle]

What is currently triggering the requery?

 

[burnfly]

I have two places that can trigger the requery. The main trigger is a 'close/save' button on a subform that opens from the continuous form. The other trigger is a 'requery' button on the continuous form (I could live without this button).

So, I have a continuous form displaying registrations and scores (hundreds). I have a subform open button for each registration, which opens the subform to input scores. Upon closing that subform, it requeries the main continuous form (and resets the scroll position back to the top of the form - ugh! )

 

[Beetle]

In the click event of the 'Requery' button on your main (continuous) form, put code like the following. Important - make sure you declare this as a Public sub (also, my previous air code did have a syntax error, which is corrected here). This assumes your 'requery' button is named cmdRequery. Adjust as necessary;

Public Sub cmdRequery_Click()

Dim vFlag

vFlag = Me![RecordID]

Me.Requery

With Me.RecordsetClone
    .FindFirst "[RecordID] = " & vFlag
    Me.Bookmark = .Bookmark
End With

End Sub

Note: If your PK field (whatever it's named - RecordID in the above example) is text then you will need to add string delimiters in the FindFirst line;

.FindFirst "[RecordID] = """ & vFlag & """"


Then, in the Close event of your sub form, put;

Private Sub Form_Close()

Forms("ContinuousFormNameHere").cmdRequery_Click

End Sub

Edit: Forgot the .Requery in the main form code. Corrected.

 

[burnfly]

Sean - thank you so much. This is working. ! Wooo hoooo!

Is "vFlag" creating a bookmark, and then the code finding that bookmark each time?

 

[Beetle]

vFlag is a variable that stores the value of the current record's PK value before the requery. Then you requery. Then, open a recordset clone and find the record with the PK value that was stored in vFlag. Then, set the form's recordset bookmark equal to the recordset clone bookmark.

Hope that makes sense.

 

[pbaldy]

In case you or somebody searching later wanted to actually mimic the scroll position, I use this:

http://www.lebans.com/SelectRow.htm

In other words, if the record selected before the requery was in the middle of the page, it will return there rather than the top of the page.

 

[Xyloz]

Wow 7 years and nobody thought to requery the recordset instead of the form...

Me.RecordSet.requery.

Hell you can even pick the form you want to requery the subsequent recordset on... because it turns out refreshing the data is what you need to be doing not the form because of course it is...

When you requery the form you are going to.. Spoilers here... refresh the form.

When you requery the Dataset you refresh the dataset within the form but not the form itself so the scroll bar does not move but the data updates.

Forms!frm_Name.Form.Recordset.requery

The reason Microsoft didn't create a solution to the forms requery issue is because there isn't a problem with it, there is a problem with you doing things bassakwards.

Wait... someone created a whole set of scroll bar logging positions to solve this... what muppetry is this!? It's like building a bicycle from scratch every time you to go to the shops when you own a car but then still driving to work.

Hold up, you use RecordSet in your form position logging algorithms!!! Really... nobody stopped to think oh yeah RecordSet exists... requery the RecordSet.

So it's like you built the bycycle to go to the shops while still driving to work and then lubed the bicycle chain with the oil from your car.

Straight up lunacy. Just stop and think about what you are doing before going down a rabbit hole for 7 years...

I am sorry I didn't happen across this sooner I signed up just to help people out who happen across this issue and let this be a lesson to you coders out there rabbit holes are bad mmmkay.

Always trouble shoot what are you doing, then stop and think ok what am I really doing...
Is there a more efficient way before creating a scroll bar logging algorithm and relative position index for NO REASON...