Programming & Security

Posted: 04 Jan 2010 08:37 AM PST

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:

SRLNO: QrySeq([ORDERID],"[ORDERID]","QUERY4")

The QrySeq() Function need three Parameters.

The First Parameter must be Unique Values available from any Field in the Query.

Second Parameter is the Column Name of the first parameter in Quotes.

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.

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 www.msaccesstips.com
'-------------------------------------------------------------------
'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
restart:
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
    rst.MoveNext
Next
rst.Close
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
Next

QrySeq_Exit:
Exit Function

QrySeq_Err:
MsgBox Err & " : " & Err.Description, , "QrySeqQ"
Resume QrySeq_Exit

End Function

Import the Orders Table from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb sample database.

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;

Select Save from File Menu or click on the Save Toolbar Button.

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.

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;

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.

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));

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.

Duplicate Serial Numbers in Query Column

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, 
QrySeq([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.