I was ask by a customer to add line numbers to each entry in a table shown in an invoice.

The concept is pretty simple, but really, how do you do that properly in MS-Access?

Microsoft actually has a solution (see link below) and it comes down to writing a function that counts the number of items from that item's current position to the beginning of the table (i.e. going backward). It works, it is just really bad if you have very large invoices since it will tend to slow down as you start getting many items.

There is the function, we can see the counting in the do until near the end (bolded):

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 function is then used in the form with the equal sign (a la Excel spreadsheet):

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

Voilà! But as you can imagine, that GetLineNumber() call becomes slower as the table gets larger.