Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Nov 2001
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Record of Record # (Access 2000)

    On a report you can put (="Page " & [Page] & " of " & [Pages]) to create page 1 of 10, but is there a formula to produce record based numbers? Record 1 of 200?

    Joseph

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

    Re: Record of Record # (Access 2000)

    Are you requiring a way to number each line of the report?
    If so, then introduce a text box on the report with the source set =1 and set the running sum property of this field.
    HTH
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: Record of Record # (Access 2000)

    Are you trying to do that in a report or in a form? If the latter, then it's easy. However, keep in mind that records don't really have numbers, so in a report, you have to fake it. And you would have to get the record count when you opened the report in order to get your "records" value. Trying to do that will slow a report down significantly.
    Charlotte

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Record of Record # (Access 2000)

    Example of one way to do this: Declare module level variable in report module:

    Dim lngRCount As Long

    To get a record count for report use Report On Open event procedure:

    Private Sub Report_Open(Cancel As Integer)
    On Error GoTo Err_Handler

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strMsg As String

    Set db = CurrentDb
    Set rst = db.OpenRecordset(Me.RecordSource)

    With rst
    If Not .EOF Then
    .MoveLast
    End If
    lngRCount = .RecordCount
    End With

    Exit_Sub:
    If Not rst Is Nothing Then rst.Close
    Set rst = Nothing
    Set db = Nothing
    Exit Sub
    Err_Handler:
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    MsgBox strMsg, vbExclamation, "OPEN REPORT ERROR MESSAGE"
    Resume Exit_Sub

    End Sub

    Add two unbound textboxes to report Detail section. 1st textbox, set ControlSource to "=1", RunningSum to Over All, Visible to No. First textbox is named txtRunSum, 2nd is named txtRecordCount. Add Detail section On Format event procedure:

    Me.txtRecordCount = "Record " & Me.txtRunSum & " of " & CStr(lngRCount)

    When report opened 2nd textbox will display "Record 1 of X records" record count. This worked OK in quick test with a relatively simple report. If opening report with filter applied the On Open event procedure would have to be modified accordingly. Also, as noted, this technique will result in slight but perceptible delay in opening report. If using A2K or later ensure reference set to DAO 3.6 Object Library.

    HTH

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Record of Record # (Access 2000)

    Revised (simplified) method: Ditch the Report Open event procedure. Instead, place unbound textbox in Report Footer section. Set its Control Source to:

    =Count(*)

    For Detail Format event, use this:

    Me.txtRecordCount = "Record " & Me.txtRunSum & " of " & Me.txtCount

    Where txtRecordCount is unbound textbox used to display record count, txtRunSum is hidden textbox with running sum (see previous post) & txtCount is textbox (also hidden) in report footer. This will be more efficient than previous suggestion, and the record count will reflect any filter applied to report.

    HTH

Posting Permissions

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