Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Return to Same Record (2003 SP3)

    On a subform, I have page x of y counter in a text box as follows: "="Record " & [CurrentRecord] & " of " & Count(*)". When I add a new record and move to the next the page counter doesn't refresh so I've set Me.Requery on the form's after update property. However, the record pointer moves to the first record. I've tried using the following code to move the record pointer to the new record position but it doesn't work. Where am I going wrong?

    Private Sub Form_AfterUpdate()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim bmkReturnHere As Variant

    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblInspectionsRemote", dbOpenDynaset)
    bmkReturnHere = rs.Bookmark
    Me.Requery
    rs.Bookmark = bmkReturnHere
    rs.movenext
    End Sub

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Return to Same Record (2003 SP3)

    Try <post#=705027>post 705027</post#>

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

    Re: Return to Same Record (2003 SP3)

    The After Update event of the form is not suitable for this, because it's extremely annoying for the user to jump back to the record he just left.

    By far the easiest solution is to use the built-in navigation buttons; these always display an accurate "Record ... of ...".
    If you don't want to use them, try setting the control source of the text box to

    ="Record " & [CurrentRecord] & " of " & [RecordsetClone].[RecordCount]

    This will be accurate without using code except when you're on a new record, then it will display something like "Record 12 of 11" because the RecordCount doesn't include the as yet unsaved new record.

    If you want to emulate the navigation buttons display exactly, make the text box unbound (i.e. clear its control source) and use the On Current event of the form to fill it. For a text box named txtCount:
    <code>
    Private Sub Form_Current()
    Dim lngCnt As Long
    lngCnt = Me.RecordsetClone.RecordCount
    If Me.NewRecord Then
    lngCnt = lngCnt + 1
    End If
    Me.txtCount = "Record " & Me.CurrentRecord & " of " & lngCnt
    End Sub</code>

Posting Permissions

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