Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Apr 2004
    Posts
    41

    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.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You could store the id of the current record in a variable, then go to that id after the requery?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I spent years assuming the same thing, but about a year ago I stumbled across a better way. Whenever I requeried a subform I always did it this way:

    Me.subForm.Requery

    But if you change it to:

    Me.subForm.Form.Requery

    I think it should work the way you want it to.

  4. #4
    Join Date
    Apr 2004
    Posts
    41

    Adding "Form" doesn't change anything

    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!!

  5. #5
    Join Date
    Apr 2004
    Posts
    41

    DoCmd.GotoRecord does the trick,

    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"?

  6. #6
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    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:

    Me.subForm.Requery
    Set rstSubFormClone=MesubForm.RecordsetClone
    With rstSubFormClone
    .FindFirst "ID=" & lngID
    If Not .NoMatch Then
    Me.subForm.BookMark=.BookMark
    end if
    .close
    end with

    set rstSubFormClone = Nothing

  7. #7
    Join Date
    Apr 2004
    Posts
    41

    Smile Don't mean to be hardheaded but

    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.

    Me.subfrmICShipTop.Form.Requery
    Set rstSubFormClone = Me.subfrmICShipTop.Form.RecordsetClone
    With rstSubFormClone
    .FindFirst "MPID=" & RecNum
    If Not .NoMatch Then
    Me.subfrmICShipTop.Form.Bookmark = .Bookmark
    End If
    .Close
    End With

    Set rstSubFormClone = Nothing

    It seems RecordsetClone and Bookmark require the .Form.


    Thanks again!!

  8. #8
    Join Date
    Sep 2007
    Location
    BC Canada
    Posts
    8

    Newbie here but might understand what your looking for

    Are you looking to reposition the scroll bar amidst the continuous form at the last place it was ie last record? If so check this Scroll Bar Mapping if not sorry Ive misunderstood you

  9. #9
    Join Date
    Sep 2013
    Posts
    4
    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
    Please advise

  10. #10
    Join Date
    Nov 2011
    Posts
    413
    Option Explicit
    Dim lngrecordnum As Long

    lngrecordnum = Me.CurrentRecord
    DoCmd.GoToRecord acActiveDataObject, , acGoTo, lngrecordnum
    Or, an Old Indian Trick:

    DoCmd.GoToRecord , "YourFormName", acNext
    DoCmd.GoToRecord , "YourFormName", acPrevious

    HTH

  11. #11
    Join Date
    Sep 2013
    Posts
    4
    Hi Burrina thanks for the reply but i received run time error 2105 " You can't go to specified record." can you help

    thanks anyway

  12. #12
    Join Date
    Nov 2011
    Posts
    413
    When you used what code did you receive this error and under what circumstances? That means that you are already on the first or last record. Explain further.

  13. #13
    Join Date
    Sep 2013
    Posts
    4
    Hi Burrina,
    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.

    cheers

  14. #14
    Join Date
    Nov 2011
    Posts
    413
    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.

  15. #15
    Join Date
    Sep 2013
    Posts
    4
    Hi Burrina,

    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.

    thanks for your help.

    cheers,
    Herry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •