Auto-Numbers in Query Column Version 2.
In January 2010, I published an article, “Function: QrySeq() – Auto-Numbering in Query Column,” on this website. Over the years, it has been well received by readers. While reviewing the article again, I thought the Function could be rewritten with less code and improved performance than a variant Array.
When the QrySeq() function is called for a record in the query, the program searches the Array of Unique Keys for the key value passed from the record as a Parameter. Once it finds a matching key, it retrieves the corresponding sequence number from the Array element and returns it to the calling record.
If the query contains a large number of records, this process can take considerable time because the program begins the search from the start of the Array each time it looks for a key value.
The New Version is Named: QryAutoNum()
Using a Collection Object instead of an Array.
You can find a detailed discussion of the Collection Object on the MS Access and Collection Object Basics Page.
Here, we will have a brief introduction to the Collection Object, including what it is and how it is used in VBA. The Collection Object is a versatile Object that can generally hold any type of value, including numeric or string values, Class Module Objects, or a collection of other Objects. The Collection Object is instantiated in VBA programs as follows:
'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
We will use the Collection Object to store the Query Auto-numbers, with the corresponding Unique Key Values assigned as the Collection Object Keys. With this approach, we can retrieve the Auto-numbers directly, eliminating the need to work with Arrays and their more complicated data storage and retrieval 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.
In the Global Area of the Module, we have declared a Collection Object, 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 AutoNumber is in a Long Integer format.
Three Static Variables, K and Y, are declared as Long Integers, and fld was declared as a 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 differs from the previously saved field name in the fld variable, the function assumes that the call originates from a new Query Record. If the field name is the same, but the value of the variable K is greater than Y, the function assumes that the earlier Query is calling QryAutoNum() again as part of a repeated run. In either case, the control variable K is reset to zero, and the Collection Object containing the existing Items is cleared from memory. The new Key field name from the KeyFldName variable is then saved in the fld variable for subsequent validation.
Next, if the KeyValue parameter is numeric, it is converted to String format using the statement KeyValue = CStr(KeyValue). The Item Key of the Collection Object must be in String format.
The variable K is then incremented by one. When K = 1, the function assumes that this is the first call to the function, originating from the first record of the Query. In this case, the main processing of the function begins.
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 the Standard Module’s Global area is instantiated in memory with the statement Set C = New Collection.
The Query recordset is opened to read records. The local variable J will create Auto-numbers and add them 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 records and added to the Collection Object. 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 the 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.
Function calls from the second record onward take the Else path of the If K = 1 Then statement. They retrieve the AutoNumber from the Collection Object using the KeyValue passed as a parameter and return it to the corresponding record in the Query.
This process is very fast because the required Item can be retrieved directly using the Collection Object Key, rather than searching through the Array from the beginning each time to locate the required Key.
When the Auto-number values for all records have been returned, the value of the control variable K equals Y. The record count of the Query was obtained and stored in the variable Y at the beginning of the program. At this point, K is incremented by 1, making its value greater than Y. Because K and Y are Static Variables, their values are retained in memory after the last record call has completed. If the same Query is run a second time, these variable values can be tested to determine whether the variables need to be reset and the existing Collection Object cleared from memory, allowing the entire process to start afresh.
If the QryAutoNum() function is called from the same Query again, the Static Variables and Collection Object are cleared from memory, preparing for a fresh run of the Function for the same Query or for a different Query.
The sample Report image, generated using the above Query, is shown below for reference.
You can use the Query as the source for a Report or Form.
A sample demo database is attached.
Download AutoNumQuery2007.zip
