How do I used Stephen Lebans / Peter Schoeders serialize function (1 Viewer)

I am trying to assign line numbers to records in my query. I have found the http://www.lebans.com/rownumber.htm Serialize Function as follows:

Function Serialize(qryname As String, keyname As String, keyvalue) As Long
Dim rs As Recordset

On Error GoTo Err_Serialize
Set rs = CurrentDb.OpenRecordset(qryname, dbOpenDynaset, dbReadOnly)
rs.FindFirst Application.BuildCriteria(keyname, rs.Fields(keyname).Type, keyvalue)
Serialize = Nz(rs.AbsolutePosition, -1) + 1

Err_Serialize:
rs.Close
Set rs = Nothing
End Function

but I don't know how to implement this. I'm sure I need to change some of the code to match tables/fields in my database but what part of the code do I change. And where/how do I reference this in my query to give me the line numbers I'm looking for?

Most of the time I can figure this out but this is eluding me. Any help is greatly appreciated!

You shouldn't need to modify any of this. Just place this in a public module, then you would call it from your query as an additional field like this:

RowNum: =Serialize("qryWhatever", "ID", [ID])

This would be assuming your query is named qryWhatever and your primary key field is named ID.

Thank you! That makes sense.

Hi,

I'm trying to get this to work in access 2016, and it goes straight to error.

Run time error 91: Object variable or With block variable not set.

I've tried with Option Compare Database, and Option Explicit, and get same error.

I think it may be this line..

Serialize = Nz(rs.AbsolutePosition, -1) + 1

Why? When I hover over it in VBA editor, it says

"rs.AbsolutePosition = <Object variable or With block variable not set>"

and same for..

rs.Fields(keyname).Type

How do I fix/adapt this?

See my thread in the repository complete with an example app https://www.access-programmers.co.uk/forums/showthread.php?t=297922

If you need more help, please start a new thread of your own.