ACC2000: How to Display Line Numbers on Subform Records

ID: Q210340

The information in this article applies to:

Microsoft Access 2000

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

This article applies only to a Microsoft Access database (.mdb).

SUMMARY

This article shows you how to create and use a procedure to display the current line or row number in a subform. Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

MORE INFORMATION

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

NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you need to reference the Microsoft DAO 3.6 Object Library.

How to Create the GetLineNumber() Function

Open the sample database Northwind.mdb.

Create a module and type the following line in the Declarations section:


Option Explicit

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 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 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. You may want to back up the Northwind.mdb file and perform these steps on a copy of the database.

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.

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.

Open the Orders Subform form in Design view and add the following text box to the form:


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

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.

Save and close the Orders Subform.

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 kbdta AccCon FmsSubf 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbhowto

Last Reviewed: July 13, 1999