Ms-Access Advanced VBA Programming Techniques, Tips and Tricks.


Auto Numbering In Query Column

We know how to create an Auto-number Field in a Table to generate Unique Sequence numbers automatically for the records added to the Table. We know how to create Sequence Numbers for data lines on Reports.

On Reports, create a Text Box in Detail Section of the Report, write the expression =1 in the Control Source Property and change the Running Sum Property Value to Over All or Over Group. If you need sequence numbers for each Group separately, depending on the Sorting and Grouping settings on the Report, then the Over Group option must be set in the Property otherwise Over All for continuous numbers from start of the Report to the End.

If you want to create Running Sum value of a Field, like Quantity or Total Price, then set the Running Sum Property value as explained above. For more details on Running Sum as well as creating Page-wise Totals on Access Reports visit the Page with the Title: MS-Access Report and Page Totals.

But, Auto-numbering in Queries looks somewhat strange to ask for unless you want to use the Query result for display purposes or the output created from that should have sequence numbers for some reason. Any way this requirement was raised by a participant in a MS-Access Forum on the Net and nobody (including me) could give a clear cut solution except some alternatives. I chipped in with a solution of my own, even though I was not happy with that either.

The Access User who has raised the question in the Forum made direct contact by sending an E-mail to me asking for a solution.

This made me thinking again on that topic and did few trial runs of few simple methods. Finally I could come up with a Function that can do the trick and I am presenting it here so that you can also use it, if you really need it.

It is important to know the usage of the QrySeq() Function in Queries in a new Column to create Sequence Numbers. The Function must be called with few Parameter Values using the values from the Query itself. So, before presenting the VBA Code of the Function I will give some details of the Parameters.

Usage of the Function in the Query Column is as shown below:


The QrySeq() Function need three Parameters.

  1. The First Parameter must be Unique Values available from any Field in the Query.
  2. Second Parameter is the Column Name of the first parameter in Quotes.
  3. Third Parameter is the Name of the Query from where you call the Function.

The Query, from where the QrySeq() Function is called should have a column of Unique Values, like Autonumber or Primary Key Field. If this is not readily available then create a Column by joining two or more existing fields (like NewColumn:([OrderlD] & [ShippName] & [RequiredDate] & [Quantity] from the existing column values and ensure that this will form Unique values in all records and pass this Column value ([NewColumn]) as first Parameter.

The first Parameter Column Name must be passed to the Function in Quotes ("[NewColumn]") as second parameter.

The Name of the Query must be passed as third parameter.

NB: Ensure that you save the Query first, after every change to the design of the Query, before opening it in Normal View, with the Function in place, to create the Sequence Numbers correctly.

Now, the simple Rules are in place it is time to try out the Function.

  1. Copy and Paste the following VBA Code into a Standard Module in your Database:
    Option Compare Database
    Option Explicit
    Dim varArray() As Variant, i As Long
    Public Function QrySeq(ByVal fldvalue, ByVal fldName As String, ByVal QryName As String) As Long
    'Purpose: Create Sequence Numbers in Query in a new Column
    'Author : a.p.r. pillai
    'Date : Dec. 2009
    'All Rights Reserved by
    'Parameter values
    '1 : Column Value - must be unique Values from the Query
    '2 : Column Name  - the Field Name from Unique Value Taken
    '3 : Query Name   - Name of the Query this Function is Called from
    'Limitations - Function must be called with a Unique Field Value
    '            - as First Parameter
    '            - Need to Save the Query after change before opening
    '            - in normal View.
    Dim k As Long
    On Error GoTo QrySeq_Err
    If i = 0 Or DCount("*", QryName) <> i Then
    Dim j As Long, db As Database, rst As Recordset
    i = DCount("*", QryName)
    ReDim varArray(1 To i, 1 To 3) As Variant
    Set db = CurrentDb
    Set rst = db.OpenRecordset(QryName, dbOpenDynaset)
    For j = 1 To i
        varArray(j, 1) = rst.Fields(fldName).Value
        varArray(j, 2) = j
        varArray(j, 3) = fldName
    End If
    If varArray(1, 3) & varArray(1, 1) <> (fldName & DLookup(fldName, QryName)) Then
        i = 0
        GoTo restart
    End If
    For k = 1 To i
    If varArray(k, 1) = fldvalue Then
        QrySeq = varArray(k, 2)
        Exit Function
    End If
    Exit Function
    MsgBox Err & " : " & Err.Description, , "QrySeqQ"
    Resume QrySeq_Exit
    End Function
  2. Import the Orders Table from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb sample database.
  3. Copy and Paste the following SQL String into the SQL Editing View of a New Query and save the Query with the Name: AutoNumberQuery:
    SELECT Orders.*, QrySeq([OrderID],"OrderID","AutoNumberQuery") AS SRLNO
    FROM Orders;
  4. Select Save from File Menu or click on the Save Toolbar Button.
  5. Open the Query in normal view.

Check the SRLNO Column for Sequence Numbers.

Here, the OrderID in the Orders Table have unique field values and we could easily get away with the Sequence Numbers correctly in SRLNO Column.

Let us pretend for a moment that we don't have a single field with Unique Values in the Query. We must create a Column with Unique Values by joining two or more Columns available in the Query and pass it to the QrySeq() Function.

Let us try such an example with the Orders Table.

  1. Copy and Paste the following SQL String into a new Query and Save the Query with the name AutoNumberQuery2.
    SELECT Orders.*, [ShipName] & [RequiredDate] AS NewColumn, QrySeq([NewColumn],"NewColumn","AutoNumberQuery2") AS SRLNO
    FROM Orders;
  2. Open the Query in normal View to check whether the Serial Numbers were created correctly or not.

When there are several records it is difficult to check whether the Column Values we have passed to the Function are really unique and the Serial Numbers generated have no duplicates in them by manually checking through the records. Instead, we will take a Count of Serial Numbers appearing more than once in the Records, if any, with the use of a Total Query using AutoNumberQuery2 as Source.

  1. Create a new Query using the following SQL String and name the new Query as DuplicatesCheckQ:
    SELECT AutoNumberQuery2.SRLNO,
     Count(AutoNumberQuery2.SRLNO) AS CountOfSRLNO
    FROM AutoNumberQuery2
    GROUP BY AutoNumberQuery2.SRLNO
    HAVING (((Count(AutoNumberQuery2.SRLNO))>1));
  2. Open DuplicatesCheckQ Query in Normal View.

You will find the following result showing SRLNO Column having the same number appearing more than once in the records indicating that the Unique Column Values we have created for the Function are not really Unique and have duplicates in them.

This can be rectified only by adding more Column Values to the NewColumn expression to eliminate the chance of ending up with duplicates.

This method is only an alternative in the absence of an AutoNumber or Primary Key field Values and not with 100% percent success rate because when you add more records to the Source Table it is likely that it can fail again. In this case the only solution is to join more fields to the expression in NewColumn so that we can reduce the chance of failures.

Now, to correct the above Query add the [Freight] Value Column also to the NewColumn expression. Or Copy and paste the following SQL String into the AutoNumberQuery2 Query overwriting the earlier SQL string in there and save the Query.

SELECT Orders.*,
 [ShipName] & [RequiredDate] & [Freight] AS NewColumn,
"NewColumn";,"AutoNumberQuery2") AS SRLNO
FROM Orders;

Open the DuplicatesCheckQ Query again to check for duplicates. If the result is empty then the Sequence Numbers generated will be correct.

If you have a different solution to this problem, then share it with me too. I don't need a refined version of the above Code or method but a different approach to arrive at the same or better result.



  1. Hello Ramachandran,

    I'm another developer who can use this query info. Thank you for putting this together!

    I copied and pasted your code and got as far as point #5. and tried it. I got an error message at "5. Open the Query in normal view." A parameter box appeared for OrderID, and no matter what value I entered, I got an error message 2471: The expression you entered as a query parameter produced this error: '[OrderID]'

    Can you help me with this and what was wrong? I copied and pasted your code. THANK YOU! Really want to get this to work.


  2. I have copied the Code and SQL String from the Web Page and tried out again. I could not find any problem with it. Are you sure that you have given the Query Name correctly as third Parameter of the Function?

    Anyway, you can download a sample database with the Code and the Queries from the following Link:


  3. Sorry, correction to the Link:


  4. a.p.r. pillai, thanks for the Link. It worked nicely!

  5. [...] need continuous sequencer numbers for the full Query Records then refer the following Blog Post: Autonumbering in Query Column. The above Code is a variant of this blog post and is intended to publish the solution in a [...]

  6. [...] bad; I just used a Report to do it for me since that was where I needed the running sum anyway): If it helps, glad I could [...]

  7. thanks its works fine!!!! but i have some problems when i used some criteria based on text field in a form (my query filter based on that) its wont work the value of "SRL" number turning into zero values do you have any sugest ?

    i need the auto number because its the only way i know to have pagination report(in report view) on ms acces or do you have another suggest to make it?

    "im from indonesia sorry if i had bad english"

  8. It will not work with Queries that references Form controls or Parameter Queries as criteria.

    Create an output Table by filtering the data by using above methods and create a separate query with the output table and call the function QrySeq() in a column in this final Query.

    There is another method for giving Group-wise Sequence numbers. This has to be done on the output table rather than on a Query. The link is given below:

  9. I have been needing this utility for quite some time but with an additional level of grouping. I have to begin re-numbering within a table everytime the PROPNUM changes. Basically the data needs to look like below. Notice the months are being counted sequentially. Can anyone help with providing code for this? MANY THANKS IN ADVANCE!

    45678 01-2010 600.00 1
    45678 02-2010 700.00 2
    45678 05-2010 500.00 3

    1234 01-2010 200.00 1
    1234 03-2010 450.00 2
    1234 07-2010 200.00 3

  10. Take a look at the following link that does exactly what you require:

    Product Group Sequence with AutoNumbers

  11. I have tried to use this function in my own database but I'm getting an error 13: Type Mismatch on QrySeqQ. The field I'm using as the parameter is a text field. Would this cause a problem?

  12. You may use any field (numeric or text) or join several fields and use it as parameter to the function. There is only one condition the parameter value must be unique.


    The first parameter [ORDERID] is the data field reference having unique value (numeric or text) in it. If you don't have a single field with unique values then join one or more fields and create a separate column like: myKey: [EmployeeID] & [FirstName] & [LastName] then give the column name [myKey] as first parameter. Note, here EmployeeID is numeric field and FirstName and LastName are text. When all the three are joined the final result is text.

    Second Parameter is the name of the first parameter column enclosed in Quotes: "myKey". If you left a space between the words my Key then enclose them in square brackets "[my Key]", otherwise the [] are not needed.

    Third Parameter is the Query Name, from which you are calling the function, in quotes: "Query4".

  13. Hi there, im having trouble with your script, that includes the fourth parameter, ctrlField,

    I can setup the code ok, but wheni run, I am getting 0 on every record.

    I have made sure that the parameters are as follows

    1 : Column Value - must be unique Values from the Query

    (setup by doing a calculated field that would render every record unique,)

    2 : Column Name - the Field Name from Unique Value Taken

    Column name from the calculated above

    3 : Query Name - Name of the Query this Function is Called from

    Self explanatory

    4 : Group Column Name - To check & Reset the Serial to Zero

    I used this field to be the field to check, that when it changes, reset the serial to 0. For this, I used a field that is repeated on each record, unti a change.

    I am using customer addresses as an example.

    Custid is non unique
    Shipto is non unique

    The calculated field is custid+shipto and the ctrlfield is custid.

    I expect the following results

    Cust KMART Ship id TOWNA Sequence 0
    Cust KMART Ship id TOWNB Sequence 1
    Cust KMART Ship Id TOWNC Sequence 2
    Cust BIGW Ship Id TOWNA Sequence 0
    Cust BIGW Ship Id TOWNB Sequence 1

    any help would be appreciated

    Scott Lancaster

  14. Sorry, worked out the last question I had, although I have another. How can I start the numbering at 1 instead of 0?

    thanks in advance,




Infolinks Text Ads

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.


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

Featured Post

Activity Dates and Quarterly Reports

There are four Quarters in a Year: Jan - Mar = 1st Quarter Apr - Jun = 2nd Jul - Sep = 3rd Oct - Dec = 4th First three months of the yea...


Blog Archive

Recent Posts