ACC2000: How to Display Line Numbers on Subform Records

How to Create the GetLineNumber() Function

1. Open the sample database Northwind.mdb.
2. Create a module and type the following line in the Declarations section:

   Option Explicit
   					

3. Type the following procedure:

   Function GetLineNumber (F As Form, KeyName As String, KeyValue)
   
      Dim RS As DAO.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 dbInteger, dbLong, dbCurrency, dbSingle, dbDouble, dbByte
            RS.FindFirst "[" & KeyName & "] = " & KeyValue
            ' Find using date data type key value.
         Case dbDate
            RS.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
            ' Find using text data type key value.
         Case dbText
            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 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 to the underlying table for this purpose.
• The current key field value.

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

How to Use the GetLineNumber() Function

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
   						

   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, 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 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.