Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Mar 2001
    Location
    Tampa, Florida, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Return to record after re-sorting (2002 (SP2))

    I'm hoping there might be a more elegant way to do this.

    I have a form which looks like a datasheet. I have the "column headings" on the form set so that if you click a column heading, it re-sorts the "datasheet" by that column.

    When I resort the data in the form, I want to return to the same record I started on. And I want that record to be "centered" in the datasheet.

    In other words, I want some records to display above the current record, and some below the current record (unless, of course, the current record is the first or last record in the new sort). I want the current record in the center of the screen, so you can see the records before and after it.

    When the sort is clicked, I save the current RecordID (and current field), so I can return to that same record (and field) after the re-sort.

    And here is what I'm doing after the re-sort, to accomplish what I mentioned above:

    ' Return to starting record
    DoCmd.GoToControl "tbxRecordID"
    DoCmd.FindRecord SavedRecordID, acStart, , acSearchAll, , acCurrent, True

    ' Move up 5 records, if possible (to "center current record on screen")
    DoCmd.GoToRecord , , acPrevious, 5

    ' Return (again) to starting record
    DoCmd.GoToControl "tbxRecordID
    DoCmd.FindRecord SavedRecordID, acStart, , acSearchAll, , acCurrent, True

    ' Return to starting cursor position (field)
    DoCmd.GoToControl strCurrentControl


    Anyone know of a more elegant way to accomplish this?

    Thanks,
    Jim.

    P.S. I've turned the warnings off temporarily, so I do not get an error if it is not possible to move up 5 records.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Return to record after re-sorting (2002 (SP2))

    Perhaps you can use the SelTop property of the form:

    DoCmd.GoToControl "tbxRecordID"
    DoCmd.FindRecord SavedRecordID, acStart, , acSearchAll, , acCurrent, True
    Me.SelTop = Me.SelTop + 5
    DoCmd.GoToControl strCurrentControl

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Return to record after re-sorting (2002 (SP2))

    I don't know why the code with bookmarks wouldn't work for you - I use it all the time. You might use RecordsetClone instead of Recordset.Clone.

    You can also turn off screen updates temporarily:

    DoCmd.Echo False
    ' Code here
    DoCmd.Echo True

  4. #4
    Lounger
    Join Date
    Mar 2001
    Location
    Tampa, Florida, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Return to record after re-sorting (2002 (SP2))

    (Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

    Thanks, Hans. That works too, and I may end up using that.

    But, I'm afraid I didn't ask the right question.The really inefficient part of this procedure is the jumping around from field to field just to do the search for the starting record (which I must do twice). Is there another way I can get back to the starting record without jumping to the RecordID field, searching for the initial RecordID number, then jumping back to the field in which I started? It's unsightly to have the cursor jumping all over the place.

    I tried Bookmarking, which didn't work at all for me.

    I also tried defining a recordset.clone and doing a FindFirst (based on the Method 1 shown here:
    http://support.microsoft.com/default.aspx?...kb;en-us;287658
    That didn't work either, but maybe because the last statement uses the Bookmark function, which doesn't seem to work for me for some reason.
    I'm not sure what I'm doing wrong...

    Maybe I just need a way to hide the cursor during the jumping around and I just continue using the procedure I have??

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Return to record after re-sorting (2002 (SP2))

    I'm guessing your resorting involves requerying the subform. This invalidates any prior bookmarks.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    Lounger
    Join Date
    Mar 2001
    Location
    Tampa, Florida, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Return to record after re-sorting (2002 (SP2))

    I'm just modifying the form's Order By property and Refreshing.

    Would that fall into the category of requerying? Or invalidate the bookmarks?

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Return to record after re-sorting (2002 (SP2))

    Changing the form's OrderBy invalidates the bookmarks.
    Charlotte

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Return to record after re-sorting (2002 (SP2))

    It probabably would. The correct procedure would be to save the current value of the field(s) that uniquely identifies this record. After your new OrderBy and Refresh, use the FindFirst method of the recordsetclone to find the same record. Then you can set: Me.Bookmark = Me.Recordsetclone.Bookmark
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    Lounger
    Join Date
    Mar 2001
    Location
    Tampa, Florida, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Return to record after re-sorting (2002 (SP2))

    Thanks everyone. I finally figured it out!

    Mark tried to spell it out for me, but I still missed one important step. It only took me a couple hours to catch it. :~|

    Here's the code I was using...

    Dim SavedRecordID As Long
    Dim rs As Object

    Set rs=Me.RecordSetClone
    SavedRecordID = RecordID 'Save current record ID
    Me.OrderBy = strFieldToSortBy 'Re-sort the data
    Refresh
    rs.FindFirst "RecordID = " & Str(SavedRecordID) 'Go back to starting record in recordset
    Me.Bookmark = rs.Bookmark 'Go back to starting record in form

    See anything wrong with that code?? :-) Everything worked right up until that last statment. The form just would not move.

    I was using the Locals window in the VB editor to follow the contents of Me and Rs. I could see the Rs.Bookmark change when I did the FindFirst. But the Me.Bookmark would not change when I tried to set it to Rs.Bookmark.

    Then I remembered seeing a difference in the contents of Rs at the critical point when Rs was set to Recordset.Clone vs. when Rs was set to RecordsetClone. In comparing those, if I used Recordset.Clone, Rs still had Bookmarks (for some reason unknown to me), but if I used RecordsetClone, Rs did not have any bookmarks ("Object invalid").

    After seeing that "Object invalid" a few times, it finally dawned on me that I had to re-establish the recordset again after re-doing the OrderBy and refreshing! Doh! Of course!

    So, in the end, the code looks like this:

    Set rs=Me.RecordSetClone
    SavedRecordID = RecordID 'Save current record ID
    Me.OrderBy = strFieldToSortBy 'Re-sort the data
    Refresh
    Set rs=Me.RecordSetClone '<==== The missing link!
    rs.FindFirst "RecordID = " & Str(SavedRecordID)
    Me.Bookmark = rs.Bookmark

    Thanks again,
    Jim.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Return to record after re-sorting (2002 (SP2))

    Glad you found it. You can remove the first line

    Set rs=Me.RecordSetClone

    since it isn't used.

Posting Permissions

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