Looking to take your VBA skills further?...

Discover twinBASIC — a powerful new development platform that expands on VBA and VB6 with advanced features, modern tools, and enhanced compatibility. Perfect for those ready to elevate their projects or transition from VBA, twinBASIC lets you build on what you already know and take your applications to the next level!

Try out twinBASIC Community Edition - it's free!

Numbering Entries in a Report or Form

        4 votes: *****     5,998 views      No comments
by Allen Browne, 20 April 2005    (for Access v2+)

Microsoft Access Tips for Casual Users

Provided by Allen Browne, allenbrowne.com. Updated Feb 2008.


Numbering Entries in a Report or Form

Report

There is a very simple way to number records sequentially on a report. It always works regardless how the report is sorted or filtered.

With your report open in Design View:

  1. From the Toolbox (Access 1 - 2003) or the Controls group of the Design ribbon (Access 2007), add a text box for displaying the number.
  2. Select the text box, and in the Properties Window, set these properties:
  Control Source =1
         Running Sum Over Group

That's it! This text box will automatically increment with each record.

Form

Casual users sometimes want to number records in a form as well, e.g. to save the number of a record so as to return there later. Don't do it! Although Access does show "Record xx of yy" in the lower left ofthe form, this number can change for any number of reasons, such as:

  • The user clicks the "A-Z" button to change the sort order;
  • The user applies a filter;
  • A new record is inserted;
  • An old record is deleted.

In relational database theory, the records in a table cannot have any physical order, so record numbers represent faulty thinking. In place of record numbers, Access uses the Primary Key of the table, or the Bookmark of a recordset. If you are accustomed from another database and find it difficult to conceive of life without record numbers, check out What, no record numbers?

You still want to refer to the number of a record in a form as currently filtered and sorted? There are ways to do so. In Access 97 or later, use the form's CurrentRecord property, by adding a text box with this expression in the ControlSource property:

    =[Form].[CurrentRecord]

In Access 2, open your form in Design View in design view and follow these steps:

  1. From the Toolbox, add a text box for displaying the number.
  2. Select the text box, and in the Properties Window, set its Name to txtPosition. Be sure to leave the Control Source property blank.
  3. Select the form, and in the Properties Window set the On Current property to [Event Procedure] .
  4. Click the "..." button beside this. Access opens the Code window.
  5. Between the lines Sub Form_Current() and End Sub, paste these lines:
    On Error GoTo Err_Form_Current
        Dim rst As Recordset

        Set rst = Me.RecordsetClone
        rst.Bookmark = Me.Bookmark
        Me.txtPosition = rst.AbsolutePosition + 1

    Exit_Form_Current:
        Set rst = Nothing
        Exit Sub

    Err_Form_Current:
        If Err = 3021 Then   'No current record
            Me.txtPosition = rst.RecordCount + 1
        Else
            MsgBox Error$, 16, "Error in Form_Current()"
        End If
        Resume Exit_Form_Current

The text box will now show a number matching the one between the NavigationButtons on your form.

Query

For details of how to rank records in a query, see Ranking in a Query


Home Index of tips Top

Rate this article:  Your rating: PoorYour rating: Not so goodYour rating: AverageYour rating: GoodYour rating: Excellent


This is a cached tutorial, reproduced with permission.

Have your say - comment on this article.

What did you think of 'Numbering Entries in a Report or Form'?

No comments yet.

Why not be the first to comment on this article?!

Have your say...

Name
E-mail (e-mail address will be kept private)
Comments


Comments require approval before being displayed on this page (allow 24 hours).