Unanswered: Keep focus on same record after requery
I have a form with a subform in continuous form view. The main form contains a command button which changes the value of a field which is displayed on the subform. I want to update the displayed value of that field, but maintain the focus on the same record after the requery.
Presently the requery causes the focus to go back to the top of the recordset.
I am guessing that there is no way to do this and thus I must somehow save the state of the subform and then recreate that state after the requery. Does anyone know of a "simple" way to do this.
Maybe I'm not giving enough info. The user navigates (scrolls) through the sorted by size subform until he finds the line he wants. He then clicks a command button which does some stuff which changes some values found in the subform. What I want to do is to update the subform (requery) and return the cursor to where it was before the command button was clicked.
Each line in the subform has a unique identifier which I can easilly save prior to requery but I'm blind or stupid but sure can't figure out how to position the cursor on a specific record. FindFirst, FindNext, Seek, etc are all recordset methods and I'm not using a DAO or ADO Recordset, but simply a query of an local Access table. Bookmarks don't work after a requery and record numbers don't seem to exist. Help me PLEASE!!
all I need to do now is determine the "record number" before the requery. By the way changes made by the command button might eliminate the row in question but that can easily be tested in advance, so the question is how do I determine the current "record number"?
I understand what you are trying to do. If you find where the requery happens and inser the .Form. in my previous post it should do exactly what you want.
But if you still want to do it by saving the record number then all you have to do is save the Unique Identifier of the record you are on. Then requery the form/subform, then create a recordsetclone of the subform, find the record with the unique identifier then match the subform with the recordsetclone using the Bookmark.
Assuming the unique identifier is in lngID:
.FindFirst "ID=" & lngID
If Not .NoMatch Then
The addition of .Form, as in Me.subfrmShipTop.Form.Requery has no effect. The cursor still moves to the top record upon Requery. However, your routine, with the addition of a few ".Form"s works just fine. Thanks loads.
Set rstSubFormClone = Me.subfrmICShipTop.Form.RecordsetClone
.FindFirst "MPID=" & RecNum
If Not .NoMatch Then
Me.subfrmICShipTop.Form.Bookmark = .Bookmark
Set rstSubFormClone = Nothing
It seems RecordsetClone and Bookmark require the .Form.
Hi There, I also have the same problem with my program with the subform after requery and i've been reading the problem as well as the solution under "Keep focus on same record after requery" topic in this forum. I've tried the routine "DCKunkle" provided to "Hughhorton" using the recordsetclone, but the i kept on getting error message 3070 " Database does not recognise my ".Findfirst.." result. Actually my subfrom is based on a query without any unique identifier, it is a payroll program with multiple pay point which is accessed by multiple user as per their accesibility. The subform is linked to a Mainform by a control which is actually the user login name
I used the old indian trick one, the one with error message is the "acNext" one.
Subform is a continous form, which i use as pre payroll process indicator. The subform is based on a query from a 2 tables, "Paypoint table" and "Access to paypoint". the access to paypoint consist of login names for those who can access the paypoint, so in the query view you'll find that the paypoint ( which is unique in the paypoint table ) more than once depend on how many user have access to it. the subform is linked to mainform by a control of "Current User" and filtered by "PIC". The subform is locked but enable. This is to prevent user from updating the subform manually.
Did you try the other code I posted also? if so, with what result? My form that I use the Old Indian Trick on is a continuous subform and the code is behind a command button and works fine. Difference, my subform is not locked. You could try adding the code to a command button and see if it works for you.
sorry for the late reply, i happen to be a payroll accountant so end mont like this is hectic, anyway I have not tried the other code yet, but i got this line that solve my problem. "Me.subform.form.refresh", so instead of using requery i use refresh and it just does the trick.