January 2020 ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction

In January 2010 I published a Function: QrySeq() - Auto-Numbering in Query Column on this website and was well-received by the readers all through these years. While going through it again, I thought it can be written with less Code and improve its performance by using a better approach, other than a variant Array.

When the function QrySeq() is called from a query record the program searches through the Array of Unique Keys and looks for the matching key, passed from the record as a parameter, finds it, and returns the sequence number, from the Array element to the calling record.

If the Query has a large volume of records this process may take more time because every time the program looks for the key value from the beginning of the Array.

The New Version is with the Name: QryAutoNum()

Using Collection Object instead of Array.

You can find a detailed discussion of Collection Object, on Ms-Access and Collection Object Basics Page.

Here we will have a brief introduction to know what it is and how it is used in VBA. The Collection Object is a versatile Object that can hold, in general terms, any Values, Numeric or String Values, Class Module Objects, or a collection of other Objects. The Collection Object is instantiated in VBA programs in the following manner:

'declare a Collection Object. Dim ABC as Collection 'create an instance of Collection Object in Memory Set ABC = New Collection 'We can Add built-in data types: Numeric, Strings etc ‘or Objects like Class Module Objects, ‘or other Collection Object as Items to the Collection Object.

'Use the Add method to add a Collection Item to the Object. ABC.Add 25 ABC.Add "ms-accesstips" 'When Collection Object Items added this way, ‘it can be retrieved only in the added order. For j = 1 to ABC.Count 'gets the count of Items Debug.Print ABC(J)’ retrieve in Item index Order. Next 'When ADDed an Item with a String Key 'we can use the Key value to retrieve the Items Randomly. 'But, usage of Key is optional. ABC.Add 25, "1" ABC.Add "ms-Accesstips", "2" x = "2" Debug.Print ABC(x) Result: ms-accesstips

So, we will use the Collection Object to add the Query Auto-Numbers with the Unique Key Values as Collection Object Key. With this approach, we can retrieve the Auto-Numbers directly, rather than struggling with Arrays and complicated storing/retrieving steps.

The QryAutoNum() Function Code.

Option Compare Database
Option Explicit

Dim C As Collection

Public Function QryAutoNum(ByVal KeyValue As Variant, ByVal KeyfldName As String, ByVal QryName As String) As Long
'-------------------------------------------------------------------
'Purpose: Create Sequence Numbers in Query Column Ver.-2
'Author : a.p.r. pillai
'Date : Dec. 2019
'All Rights Reserved by www.msaccesstips.com
'-------------------------------------------------------------------
'Parameter values
'-------------------------------------------------------------------
'1 : Column Value - must be UNIQUE Numeric/String Type Values from Query Column
'2 : Column Name  - the Field Name in Quotes from where Unique Values taken
'3 : Query Name   - Name of the Query this Function is Called from
'-------------------------------------------------------------------
'Limitations - Function must be called with Unique Field Values
'            - as First Parameter
'            - Need to Save the Query, if changes made, before opening
'            - in normal View.
'-------------------------------------------------------------------
Static K As Long, Y As Long, fld As String
On Error GoTo QryAutoNum_Err

Y = DCount("*", QryName) ' get count of records for control purpose

'If KeyfldName Param is different from saved name in variable: fld
'or Value in K more than count of records in Variable: Y
'then it assumes that the QryAutoNum() is called from a different Query
'or a repeat run of the same Query. In either case the Control Variable
'and Collection Object needs re-initializing.
If KeyfldName <> fld Or K > Y Then
'initialize Control Variable
'and Collection Object
    K = 0
    Set C = Nothing
    'save incoming KeyfldName
    fld = KeyfldName
End If

'if KeyValue parameter is Numeric Type then convert
'it to string type, Collection Object needs it's Key as String Type.
If IsNumeric(KeyValue) Then
    KeyValue = CStr(KeyValue)
End If

K = K + 1
If K = 1 Then
Dim j As Long, db As Database, rst As Recordset
Dim varKey As Variant

Set C = New Collection

Set db = CurrentDb
Set rst = db.OpenRecordset(QryName, dbOpenDynaset)

'Add recordlevel AutoNumber with Unique KeyValue
'to Collection Object, in AutoNumber, KeyValue Pair
While Not rst.BOF And Not rst.EOF
    j = j + 1 ' increment Auto Number
    
    'Get key value from record
    varKey = rst.Fields(KeyfldName).Value
    
    'if numeric key convert it to string
    If IsNumeric(varKey) Then
      varKey = CStr(varKey)
    End If
    
    'Add AutoNumber, KeyValue pair to Collection Object
    C.Add j, varKey
    
    rst.MoveNext
Wend
    rst.Close
    Set rst = Nothing
    Set db = Nothing

'Retrieve AutoNumber from Collection Object
'using the KeyValue.  Works like Primary Key of Table
    QryAutoNum = C(KeyValue)
Else
    QryAutoNum = C(KeyValue)
End If

If K = Y Then 'All record level AutoNumbers are Returned
    K = K + 1 ' increment control variable
End If

QryAutoNum_Exit:
Exit Function

QryAutoNum_Err:
MsgBox Err & " : " & Err.Description, , "QryAutoNum"
Resume QryAutoNum_Exit

End Function

Sample Source Query SQL.

With the Northwind Products Table.

SELECT Products.ID, 
Products.Category, 
Mid([Product Name],18) AS PName, 
Sum(Products.[Standard Cost]) AS StandardCost, 
QryAutoNum([ID],"ID",
"Product_AutoNumQ") AS QrySeq
FROM Products
GROUP BY Products.ID, Products.Category, Mid([Product Name],18)
ORDER BY Products.Category, Mid([Product Name],18);

Review of VBA Code Line-By-Line.

On the Global Area of the Module, we have declared a Collection Object with the Object Variable C.

The QryAutoNum() Function declaration is the same as our earlier QrySeq() with three parameters.

Unique Key-Value either Numeric or String as the first Parameter.
The Key-Value Field’s Name in String Format.
The Query Name in String Format.

The returned Auto-Number is in a Long Integer format.

Three Static Variables, K and Y declared as Long Integers, and fld was declared as String Variable.

All three Variables control the Code execution paths and determine when to initialize Collection objects and control variables.

The DCount() Function takes a count of records in the Query in Variable Y.

If the KeyFldName is different from the saved name fld, then it assumes that the function call is from a new Query Record. If the field name is the same, but the value in variable K is greater than Y then the earlier Query is calling the function QryAutoNum() for a repeat of the earlier run. In either case, the control variable K is reset to zero, and the Collection Object with existing Items is cleared from memory. The new Key field name received in the KeyFldName variable is saved in the fld variable for later validation check.

Next, if the KeyValue parameter value is numeric then it is converted to String format in the statement: KeyValue = Cstr(KeyValue). The Collection Object Item Key must be in string format.

Next, the variable K is incremented by one. When the value in K=1 it assumes that this is the first call of this function, from the first record of a Query. When this is the case the main process of this function starts.

The local temporary Variables are declared here and their values are not preserved between calls of this function from different records of the query.

The Collection Object declared in Standard Module’s Global area is instantiated in memory, with the statement Set C = New Collection.

The Query record set is opened to read records one by one. The local variable J is for creating Auto-numbers and adding to the Collection Object for each record. The Unique Key-Value, read from the recordset, into variable varKey, is added to the Collection Object as its Key Value.

If the varKey variable value is Numeric Type then it is converted to String format.

The Auto-Number Value in Variable J and the string value in variable varKey are added to the Collection Object in the following statement, as its Item value, Key pairs:

C.Add J, varKey

This process is repeated for all the records in the Query. The Auto-Numbers are generated for all the records and added to the Collection Object, one after the other. All this work is done during the first call of the function from the first record of the query.

Did you notice that we are reading the Unique Key value of each record directly from the record set within the While . . . Wend Loop to add them to the Collection Object? After adding the Auto-Numbers for all records, the record set and Database Objects are closed.

Remember, we are still on the first call of the function from the first record of the query and the first parameter variable KeyValue still holds the first record Key Value.

The next statement QryAutoNum = C(KeyValue) retrieves Collection Object’s first Item Auto-Number Value 1, using the Unique Key Value in parameter variable KeyValue, and returns it to the function calling record. This will happen only once because the variable K will be greater than one on subsequent calls of this function.

So, the Function calls from the second record onwards will take the ELSE path of the If K=1 Then statement and retrieve the Auto-Numbers from Collection Object, using the KeyValue passed as Parameter, and returns it to respective records in the Query.

It works very fast because we can directly pick the item value, using the Collection Object Key, rather than searching for the Key, through the Array from the beginning to find the one we want.

When all the record Auto-Number values are returned, the value in the control variable K = Y. We have already taken the count of records of the Query, in Variable Y at the beginning of the program. At this point we increment the value in variable K, by 1 to make it more than the value in Variable Y. Since, K and Y are Static Variables their values are not lost after the last record call is over and remains in memory. If the same Query is run a second time the test for these variable values can determine whether we need to reset the variable values and clear the earlier Collection Object from memory for a fresh start of the process all over again.

If the QryAutoNum() function is called from the same Query again the Static Variables and Collection Object is cleared from memory, preparing for a fresh run of the Function for the same Query or for a different Query.

The sample Report Image using the above Query as Source is given below for reference.