Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Auto-Numbers in Query Column Version-2

Introduction

During January 2010 I have published a Function: QrySeq() - Auto-Numbering in Query Column  on this website and 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 it’s performance by using a better approach, other than Variant Array.

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

If the Query have 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 on Collection Object in 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 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 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 it’s 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 Object Variable C.

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

  1. Unique Key Value either Numeric or String as first Parameter.
  2. The Key Value Field’s Name in String Format.
  3. The Query Name in String Format.

The returned Auto-Number is in Long Integer format.

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

All the three Variables controls the Code execution paths and determines when to initialize Collection Object 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 in Variable fld then it assumes that the function call is from a new Query Record.  If the field name is 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, the Collection Object with existing Items are cleared from memory.  The new Key field name received in KeyFldName variable is saved in 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 on Standard Module’s  Global area is instantiated in memory, with the statement Set C = New Collection.

The Query recordset is opened to read records one-by-one. The local variable J to create Auto-Numbers and Add it 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 it’s Key Value.

If the varKey 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 it’s Item, Key pair:

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 them into the Collection Object, one after the other.  All this work is done during 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 recordset within the While . . . Wend Loop to add them to the Collection Object.  After adding Auto-Numbers for all records the recordset and Database Objects are closed.

Remember, we are still at 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 second record onwards will take the ELSE path of the If K=1 Then statement and retrieves 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 control variable K = Y. We have already taken the count of records of the Query, in Variable Y in 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 on 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

You can use the Query as Source for Report or Form. 

A sample demo database, with all the Objects and VBA Code, is attached for Downloading and for trying out the Code.


  1. Auto-Numbering in Query Column
  2. Product Group Sequence with Auto-Numbers.
  3. Preparing Rank List.
  4. Auto-Number with Date and Sequence Number.
  5. Auto-Number with Date and Sequence Number-2.
Share:

MS-Access Tips on your Finger-Tip

  • Download Android App 'MSA Guru' Version of LEARN MS-ACCESS TIPS AND TRICKS from Google Play Store.

Want to Post Free Ads on the Web


Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

Search

Popular Posts

Blog Archive

Powered by Blogger.

Follow by Email

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object Graph Charts Query VBA msaccessQuery Calculation Event List Boxes Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference TreeView Control ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button ImageList Control Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join ListView Control Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code