Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Form Refresh (2000)

    Does anybody know a way of preventing (or appearing to prevent) a continuous forms form from moving back to the first record after a requery?

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

    Re: Form Refresh (2000)

    AFAIK, there is no way to prevent it - a requery means that the record source is closed and reopened. But you can make it seem as if the subform stays at the same record, if you have a unique key field (or combination of fields). Store the value of the unique key in a variable immediately before requerying, then use the customary technique to go to the record with the stored key.

    Here is sample code. It is On Click code for a command button cmdRequery on the main form; the subform is named sbfTest, and the unique key is named ID. Of course, you'll have to substitute the appropriate names.

    ' Variable to hold
    Dim lngID As Long

    Private Sub cmdRequery_Click()
    ' Manipulate subform
    With Me.sbfTest.Form
    ' Store key
    lngID = .txtID
    ' Requery
    .Requery
    ' Find key in recordset
    .RecordsetClone.FindFirst "ID=" & lngID
    ' Move to this record
    .Bookmark = .RecordsetClone.Bookmark
    ' Optional: set focus to a control in the subform
    sbfTest.SetFocus
    .txtID.SetFocus
    End With
    End Sub

  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Refresh (2000)

    Could I refine the question slightly then? If the form is displaying several records and the seletced record is in the middle of the display, is there any way to make sure that after the requery, and after relocating the selected record, it can be returned to the same position on the form/page. I suspect the code will place the selected record at the top of the displayed page and so the user will see it jump from the middle of the form to the top of the form.

    Ian

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

    Re: Form Refresh (2000)

    I don't know of any way to control the pagination of a continuous form. If the record you bookmarked in within the number of records that will display on the first page, then you should see it in its current location. If the page has to scroll, then I believe you'll see it at the top.
    Charlotte

  5. #5
    Star Lounger
    Join Date
    Mar 2001
    Location
    Dudley, Midlands, England
    Posts
    55
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Form Refresh (2000)

    Could you work out the position of the current record on the screen using a combination of CurrentSectionTop and the height of the Detail section and any header section?

    Of course do bear in mind that after a requery, the record you want to position on may be in a different position in the recordset.

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

    Re: Form Refresh (2000)

    Perhaps you could, but it's a <img src=/w3timages/censored.gif alt=censored border=0> nuisance, unless you know how to scroll a form using Windows API calls, and even then... I tried to do something like that using built-in Access methods only (no API), and found it to be more work than it's worth.

  7. #7
    Star Lounger
    Join Date
    Mar 2001
    Location
    Dudley, Midlands, England
    Posts
    55
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Form Refresh (2000)

    What I was thinking is very messy, but it doesn't involve the API. Something along these lines.

    Using a combination of CurrentSectionTop and the height of the detail section and any form header by simple subtraction and division you can work out the relative position of the current record (providing it's visible, anyway). From this you can back-up the recordset and find the record that must be at the top of the screen. Having found the record that was previously at the top of the form, use the bookmark properties of the recordset clone to position the recordset there - that gets you the form in the right place, well, most of the time. The only way I can think of accomplishing the next bit so the form doesn't just reposition itself on the new record is to do the last bit outside of the click event (to use an earlier example). Horrible though it is, the only way I've come up with is to use the form timer event. Within the form Timer event code, reposition the form to the correct current record, again using bookmarks. As one of the last things In the Click event code, set the Timer event to runa few hundredths of a second's time. You may even be able to make it look less messy by including a DoCmd.Echo False in the click event and a DoCmd.Echo True at the end of the Timer event code.

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

    Re: Form Refresh (2000)

    Hi Simon,

    This is much like what I ended up with. It works more or less, but not 100%, and it *is* very messy. I decided not to implement it in production work.

    But thanks for providing the details.

  9. #9
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Refresh (2000)

    Is there a way of to just requery the currenty record, rather than the whole form?

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

    Re: Form Refresh (2000)

    Not that I know of, but you could try Recalc instead of Requery. This forces an immediate recalculation of all calculated controls; it doesn't change which record is current. Whether Recalc is sufficient depends on your form/subform.

  11. #11
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Refresh (2000)

    It seems that the crucial thing to determine is the first displayed record on the screen. Is there any way of doing this? I guess that SimonC's answer points towards this but his method looks pretty complex. Is there an easier way (or a way of presenting SimonC's method that does not appear so daunting)?

    Ian

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

    Re: Form Refresh (2000)

    Not really, and that's the reason most of us don't bother to try this. It is a question of the value of doing it compared to the effort it takes to make it work and the resources required for all that manipulation.
    Charlotte

Posting Permissions

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