Microsoft KB Archive/325236

= How to display line numbers on subform records in Access 2002 =

Article ID: 325236

Article Last Modified on 6/23/2005

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q325236





For a Microsoft Access 97 version of this article, see 120913.

For a Microsoft Access 2000 version of this article, see 210340.

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

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

IN THIS TASK
SUMMARY
 * How to Create the GetLineNumber Function
 * How to Use the GetLineNumber Function



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

back to the top

How to Create the GetLineNumber Function
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers 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 requirements. The following examples demonstrate how to create and how to use the sample function, GetLineNumber.

NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

 Open the sample database Northwind.mdb.  Create a module and then 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 &quot;[&quot; & KeyName & &quot;] = &quot; & KeyValue ' Find using date data type key value. Case dbDate RS.FindFirst &quot;[&quot; & KeyName & &quot;] = #&quot; & KeyValue & &quot;#&quot; ' Find using text data type key value. Case dbText RS.FindFirst &quot;[&quot; & KeyName & &quot;] = '&quot; & KeyValue & &quot;'&quot; Case Else MsgBox &quot;ERROR: Invalid key field data type!&quot; 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 put line numbers.
 * The name of the unique key field in the underlying table of the subform. 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 can use the following sample expression as the ControlSource property setting of a text box on a subform, if the underlying table of the subform has a field that is called ID as its unique key field: =GetLineNumber(Form,&quot;ID&quot;,[ID]) back to the top

How to Use the GetLineNumber Function
CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow 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 serves 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.</li>  Open the Orders Subform form in Design view and then add the following text box to the Detail section of the form: <pre class="fixed_text">  Name: LineNum ControlSource: =GetLineNumber([Form], &quot;ID&quot;, [ID]) </li> 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.</li> Save and then close the Orders Subform.</li> 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.</li></ol>

back to the top

Additional query words: acc2002

Keywords: kbhowto kbprogramming kbusage KB325236

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.