Fixed Scroll When Requerying Form

How can I get the vertical scroll of the form to go back to where is was before I requeried a form?  By default, focus is set to the first record after a requery.  Also, when I save a reference to the current record and set the focus to the same record after requerying, the current record becomes the first record displayed on the form if that record isn't visible when the form is scrolled all the way up.  Is there a way get a reference to the first *visible* record on the form and then set the focus back to that record after the requery?
LVL 1
Declan_BasileITAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
Are you talking about a continuous form or a datasheet?
0
Jack LeachProprietorCommented:
Try a Refresh instead of a Requery.  If it suits your needs data-wise, it'll keep the current record from being lost.

Otherwise, you may have to track the current record ID before the requery, then use a FindFirst for the recordset after the requery to get the cursor back to that location.  I tend to find this a bit sloppy though: screen flicker and not 100% every time.

As for determining the first visible record, that's quite difficult.  There's code around (check out www.lebans.com, I believe there's a demo there), which will attempt to read the position of the scroll thumb within the track, and thus get a "percentage" of how far in your records you are, then attempt to deduce the specific record (or thereabouts), but using it's AbsolutePosition within the recordset, again based on that percentage.

This was exceedingly difficult back in 2000 or so when UIs didn't vary so highly as they do these days, and I'm inclined to believe that Stephen did it more as a proof of concept rather than a real, working solution.  And Stephen Lebans was pretty much the best, so I'm not sure there's anything better.

Cheers,
-jack
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
You will use the RecordsetClone:

Dim rs As DAO.Recordset
Dim lngID As Long

Set rs = Me.RecordsetClone
' Store the current ID of the form.
lngID = Me!ID.Value

' Run requery code.

' Look up the ID.
rs.FindFirst "ID = " & lngID
If Not rs.NoMatch Then
    ' Move form's current record to the record found.
    Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing

/gustav
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Dale FyeCommented:
@Gustav,

I know you know this, but you have to put the Set rs = me.recordsetclone line after the requery.

Dale
0
Gustav BrockCIOCommented:
Yes, you are right, thanks.

Dim rs As DAO.Recordset
 Dim lngID As Long

 ' Store the current ID of the form.
 lngID = Me!ID.Value

 ' Run requery code.

 Set rs = Me.RecordsetClone

 ' Look up the ID.
 rs.FindFirst "ID = " & lngID
 If Not rs.NoMatch Then
     ' Move form's current record to the record found.
     Me.Bookmark = rs.Bookmark
 End If

 Set rs = Nothing

 /gustav
0
Declan_BasileITAuthor Commented:
It's a continuous form.  Also, I need to requery, not just refresh.  I already tried finding the record after requerying.  It keeps the same record as the current record but doesn't set the scroll to the same position as it originally was in most cases.  I'm sorry I wasn't more clear about that in my original posting.  It looks like the solution (attempting to read the position of the scroll thumb within the track) is complicated and inaccurate, and that the best thing for me to do is to live with the limitation of not being able to keep the scroll position when requerying a form.  Thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.