How to keep the focus in the same position after re-querying a list (1 Viewer)

[shoji]

I have a continuous form, CustofmerList, which displays the customer ID, name, phone number, and their status. When I click the name (or ID), it opens a new form, CustomerMain, which shows the details of the customer. When I edit the customer status and close the form, it takes me back to the list form. At that point, I use Requery to update the status on the list. However, this Requery command takes me all the way to the top of the list, not where I was when I clicked the name. I use DoCmd.GotoRecord to move back to the original position, but this seems to be a little bit cumbersome as the original line still moves to the top of the window. Is there any better way to stay where I was on the list without disturbing the list window?

 

[Pat Hartman]

Don't requery. Requery runs the whole query again and that is why it repositions. Refresh might stay in the same place but if it doesn't, then either use the code you have to go back to the record you were on or don't update the form. Do you really need to see the changes you just made?

 

[arnelgp]

maybe use Recalc instead of Requery.

 

[GinaWhipp]

Maybe this will help...

Maintain Record Position After Update « Access... entials

access-diva.com

 

[Eugene-LS]

Is there any better way to stay where I was on the list without disturbing the list window?

Try:

Forms("CustofmerList").Recordset.Requery

 

[MarkK]

To maintain your position in a recordset after requery--if you must requery--you can do something like this...

Sub SoftRequery(rst as Recordset)
    Dim pos as Integer
    With rst
        pos = .AbsolutePosition
        .Requery
        .AbsolutePosition = pos
    End with
End Sub

 

[Pat Hartman]

Looks like Gina's link confirms my suggestion to use Refresh rather than Requery.

 

[shoji]

Thank you all. Refresh worked in one form, but somehow it didn't work in another form. I will do some more tests to find out why.
Deleting a record is also one situation, but since it does not happen too often, I can live with running DoCmd.Goto command for that.
Thanks again.