shanesuebsahakarn
asked on
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("S ELECT [" & 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","MyIDF ield",[MyI DField])
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.
Function GetRowNum(strQueryName As String, strIDField As String, varID As Variant) As Long
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("S
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","MyIDF
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
GetRowNum("winter direct mail","PledgeID",[PledgeID
thanks!