Display record/row number in a query

This has been asked a few times and I've seen no generic solutions so here is a function to retrieve and display the row number in a query:

Function GetRowNum(strQueryName As String, strIDField As String, varID As Variant) As Long
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("SELECT [" & strIDField & "]" & " FROM " & strQueryName, dbOpenSnapshot)
Select Case rst(strIDField).Type
    Case dbBigInt, dbBinary, dbBoolean, dbByte, dbCurrency, dbDecimal, dbDouble, dbFloat, dbInteger, dbLong, dbSingle, dbVarBinary:
        rst.FindFirst "[" & strIDField & "]=" & varID
    Case dbChar, dbText, dbMemo:
        rst.FindFirst "[" & strIDField & "]='" & varID & "'"
    Case dbDate, dbTime:
        rst.FindFirst "[" & strIDField & "]=#" & Format(varID, "mm/dd/yyyy hh:nn:ss") & "#"
End Select

GetRowNum = rst.AbsolutePosition + 1

rst.Close
Set rst = Nothing
End Function

If anyone wishes to use it, paste the above code into a module (you will also require a reference to the DAO library if you are using A2K or later - open a code module and click Tools->References and make sure DAO is ticked), and put a field like this in your query:
GetRowNum("MyQuery","MyIDField",[MyIDField])

Where MyQuery is the name of the query you are calling the function from and MyIDField is the name of the primary key field.

It will work correctly regardless of any sorting performed in the query, but will only work in saved querydefs (ie you can't use it for example, in the SQL of a combo box). If the query is manually sorted or filtered by clicking on the Sort/Filter icons or menu options, the function will return the wrong row numbers.Start Free Trial