ACC: How to Display Line Numbers on Subform Records

ID: Q120913

The information in this article applies to:

Microsoft Access versions 2.0, 7.0, 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article shows you how to create and use a procedure to display the current line or row number in a subform.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.

NOTE: This article explains a technique demonstrated in the sample files, RptSampl.exe (for Microsoft Access for Windows 95 version 7.0) and RptSmp97.exe (for Microsoft Access 97). For information about how to obtain these sample files, please see the following articles in the Microsoft Knowledge Base:

   ARTICLE-ID: Q145777
   TITLE     : ACC95: Microsoft Access Sample Reports Available on MSL

   ARTICLE-ID: Q175072
   TITLE     : ACC97: Microsoft Access 97 Sample Reports Available on MSL

MORE INFORMATION

The following examples demonstrate how to create and use the sample function GetLineNumber().

How to Create the GetLineNumber() Function

1. Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0).

2. Create a new module and type the following line in the Declarations

   section if it isn't already there:

      Option Explicit

3. Type the following procedure:

   NOTE: In the following sample code, an underscore (_) at the end of a
   line is used as a line-continuation character. Remove the underscore
   from the end of the line when re-creating this code in Access Basic.

      Function GetLineNumber (F As Form, KeyName As String, KeyValue)
         Dim RS As Recordset
         Dim CountLines

         On Error GoTo Err_GetLineNumber

         Set RS = F.RecordsetClone

         ' Find the current record.
         Select Case RS.Fields(KeyName).Type
            ' Find using numeric data type key value.
            Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
            DB_DOUBLE, DB_BYTE
               RS.FindFirst "[" & KeyName & "] = " & KeyValue
            ' Find using date data type key value.
            Case DB_DATE
               RS.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
            ' Find using text data type key value.
            Case DB_TEXT
               RS.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
            Case Else
               MsgBox "ERROR: Invalid key field data type!"
               Exit Function
         End Select

         ' Loop backward, counting the lines.
         Do Until RS.BOF
            CountLines = CountLines + 1
            RS.MovePrevious
         Loop

      Bye_GetLineNumber:
         ' Return the result.
         GetLineNumber = CountLines

         Exit Function

      Err_GetLineNumber:
         CountLines = 0
         Resume Bye_GetLineNumber

      End Function

The GetLineNumber() function requires the following three parameters:

The form object on which to place line numbers.
The name of the unique key field in the subform's underlying table. If the record source does not have a single unique key field, add a field with an AutoNumber data type (or Counter in version 2.0) to the underlying table for this purpose.
The current key field value.

You could use the following sample expression as the ControlSource property setting of a text box on a subform, given that the subform's underlying table has a field called ID as its unique key field:

   =GetLineNumber(Form,"ID",[ID])

How to Use the GetLineNumber() Function

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb (or NWIND.MDB in version 2.0). You may want to back up the Northwind.mdb file, or perform these steps on a copy of the Northwind database.

1. Open the Order Details table in Design view, add the following

   field to the table, and then save the table:

      Field Name: ID
      Data Type:  AutoNumber

   NOTE: AutoNumber is called Counter in version 2.0.

   This field will serve as the required single unique field for the
   table.

2. Open the Order Details Extended query in Design view, add the

   ID field from the Order Details table to the query grid, and then
   save the query.

3. Open the Orders Subform form in Design view and add the following text

   box to the form:

      Name: LineNum
      ControlSource: =GetLineNumber([Form], "ID", [ID])

4. On the View menu (or Edit menu in version 2.0), click Tab Order. Drag

   the LineNum field from the bottom of the Custom Order list to the top,
   and then click OK.

5. Save and then close the Orders Subform.

6. Open the Orders form in Form view and move to a record with multiple

   order line items. Note that the LineNum text box displays the record
   number for each product in the order.

Additional query words:

Keywords          : kbusage FmsSubf 
Version           : 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto

Last Reviewed: November 21, 1998