Question for Gustav

Question

Hi Gustav,

I'm using some code you wrote (which is perfect for what I want do) but I just can't work out how or if I can reset after adding records.

if I run the query after adding data I get:

If I then do a compact and repair (which seems to reset it) I get:

which is how it should look with the sort done on columnA then columnB and then row number added.

Is it possible to force a reset/requery without doing compact and repair to force the same?

The function I'm using is:

 'Builds consecutive row numbers in a select, append, or create query

' with the option of a initial automatic reset.

' Optionally, a grouping key can be passed to reset the row count

' for every group key.

'

' 2018-08-23. Gustav Brock, Cactus Data ApS, CPH.

'

Public Function RowNumber( _

    ByVal Key As String, _

    Optional ByVal GroupKey As String, _

    Optional ByVal Reset As Boolean) _

    As Long

    ' Uncommon character string to assemble GroupKey and Key as a compound key.

    Const KeySeparator      As String = "¤§¤"

    ' Expected error codes to accept.

    Const CannotAddKey      As Long = 457

    Const CannotRemoveKey   As Long = 5

    Static Keys             As New Collection

    Static GroupKeys        As New Collection

    Dim Count               As Long

    Dim CompoundKey         As String

    On Error GoTo Err_RowNumber

    If Reset = True Then

        ' Erase the collection of keys and group key counts.

        Set Keys = Nothing

        Set GroupKeys = Nothing

    Else

        ' Create a compound key to uniquely identify GroupKey and its Key.

        ' Note: If GroupKey is not used, only one element will be added.

        CompoundKey = GroupKey & KeySeparator & Key

        Count = Keys(CompoundKey)

        If Count = 0 Then

            ' This record has not been enumerated.

            '

            ' Will either fail if the group key is new, leaving Count as zero,

            ' or retrieve the count of already enumerated records with this group key.

            Count = GroupKeys(GroupKey) + 1

            If Count > 0 Then

                ' The group key has been recorded.

                ' Remove it to allow it to be recreated holding the new count.

                GroupKeys.Remove (GroupKey)

            Else

                ' This record is the first having this group key.

                ' Thus, the count is 1.

                Count = 1

            End If

            ' (Re)create the group key item with the value of the count of keys.

            GroupKeys.Add Count, GroupKey

        End If

        ' Add the key and its enumeration.

        ' This will be:

        '   Using no group key: Relative to the full recordset.

        '   Using a group key:  Relative to the group key.

        ' Will fail if the key already has been created.

        Keys.Add Count, CompoundKey

    End If

    ' Return the key value as this is the row counter.

    RowNumber = Count

Exit_RowNumber:

    Exit Function

Err_RowNumber:

    Select Case Err

        Case CannotAddKey

            ' Key is present, thus cannot be added again.

            Resume Next

        Case CannotRemoveKey

            ' GroupKey is not present, thus cannot be removed.

            Resume Next

        Case Else

            ' Some other error. Ignore.

            Resume Exit_RowNumber

    End Select

End Function

Answer 1

That was a good question.

Problem is that your data has no index, thus Access retrieves them in no order and assigns the rownumbers to whatever that sequence may be - and does this BEFORE ordering.

I managed to get it to run as expected by adjusting the reset clause to a FIXED value (0) as this will be evaluated before any record is listed. Thus, the reset happens before the - now ordered - records are listed, and then the rownumbers get assigned as to the order:

SELECT

    ID, ColumnA, ColumnB, RowNumber(CStr([ID])) AS IDS

FROM

    tblLink

WHERE

    RowNumber("", "", True) = 0

ORDER BY

    ColumnA, ColumnB;

Thank you for pointing this out. I'll update the code, though not today.

Answer 2

Hi Gustav, I don't know if you noticed the question Ken Sheridan has been assisting with recently (Find Previous Record field data, or better suggestion) ...or if you are even interested... but this code makes it easy to achieve the same result without code. It took me a couple of select queries, a temp table and an append query but someone with your, Ken, Duane, Scott etc skills could do something much more elegant. I'm just mentioning it because i haven't seen anything simple to achieve that format but your code goes a long way towards simplifying it i.e. no other code is required.

Answer 3

Hi Gustav, Thank you for your assistance once again. It works perfectly.

Answer 4

That's because someone cut out the examples from the header.

It reads in full:

' Builds consecutive row numbers in a select, append, or create query

' with the option of a initial automatic reset.

' Optionally, a grouping key can be passed to reset the row count

' for every group key.

'

' Usage (typical select query):

'   SELECT RowNumber(CStr([ID])) AS RowID, *

'   FROM SomeTable

'   WHERE (RowNumber(CStr([ID])) <> RowNumber("","",True));

'

' Usage (with group key):

'   SELECT RowNumber(CStr([ID]), CStr[GroupID])) AS RowID, *

'   FROM SomeTable

'   WHERE (RowNumber(CStr([ID])) <> RowNumber("","",True));

'

' The Where statement resets the counter when the query is run

' and is needed for browsing a select query.

'

' Usage (typical append query, manual reset):

' 1. Reset counter manually:

'   Call RowNumber(vbNullString, True)

' 2. Run query:

'   INSERT INTO TempTable ( [RowID] )

'   SELECT RowNumber(CStr([ID])) AS RowID, *

'   FROM SomeTable;

'

' Usage (typical append query, automatic reset):

'   INSERT INTO TempTable ( [RowID] )

'   SELECT RowNumber(CStr([ID])) AS RowID, *

'   FROM SomeTable

'   WHERE (RowNumber("","",True)=0);

'

' 2018-08-23. Gustav Brock, Cactus Data ApS, CPH.

'

Public Function RowNumber( _

    ByVal Key As String, _

    Optional ByVal GroupKey As String, _

    Optional ByVal Reset As Boolean) _

    As Long

Of course, you can also call a manual reset of the collection somewhere in your code:

RowNumber "", "", True