Indexing and Sorting with VBA

Indexing and Sorting with VBA.

A table is usually created with a Primary Key or an Index to organize its records in a specific order for viewing or processing. A Primary Key or Index can include one or more fields to ensure that each record has a unique key value, especially when a single field alone cannot guarantee uniqueness.

For example, if you open the Employees table in the Northwind.mdb sample database (located in *C:\Program Files\Microsoft Office\Office11\Samples*), and switch to Design View, you’ll see that the EmployeeID field is defined as the Primary Key.

To create an Index manually and define it as a Primary Key:

1. Open the Table in Design View.

2. Click on the left side of the Field Name to select it.

3. Click on the Indexes Toolbar Button.

4. You may give any suitable name in the Index Name Field, replacing the PrimaryKey text, if you would like to do so.

If the values in the selected field are not unique, you can include additional fields—up to a maximum of ten—to create a composite key that ensures uniqueness for the Primary Key.

To do this, click and drag over the adjoining fields to select them, or hold down the Ctrl key and click individual fields to select non-adjacent ones.

This process creates a Primary Key Index for the table. You can define multiple indexes in a table, but only one Primary Key can be active at any given time.

Creating an Index with VBA.

We can activate an existing index in a table or create a new one through VBA and use it for data processing.

In this exercise, we’ll learn how to:

1. Create a new index named myIndex for a table through VBA.

2. Activate the required index for data processing.

3. Delete the index once processing is complete.

Before creating a new index, we’ll first check whether it myIndex already exists in the table’s Indexes collection.

• If it exists, we’ll activate it.

• If not, we’ll create it, activate it, and proceed with processing.

For this example, we’ll use the Orders and Order Details tables from the Northwind.mdb sample database. The Order Details table will be organized in Order-Number Sequence. The total value of all items for each order is calculated and updated in the corresponding record of the Orders table.

The Data Processing Steps

The following are the data processing steps, which we follow in the VBA Routine to update the Orders Table with order-wise Total Value from the Order Details Table:

1. Open the Orders Table for Update Mode.

2. Open Orders Details Table for Input.

3. Check for the Index name myIndex in the Order Details Table. If found, then activate it; otherwise, create myIndex and activate it as the current Index.

4. Initialize the Total to Zero.

5. Read the first record from the Order Details Table.

6. Calculate the Total Value of the item using the Expression: Quantity * ((1-Discount%)*UnitPrice).

7. Add the Value to the Total.

8. Read the next record and compare it with the earlier Order Number. If the same, then repeat steps 6 and 7 until the Order Number changes or there are no more records to process from the Order Details Table.

9. Find the record with the Order Number in the Orders Table.

10. If found, then edit and update the Total to the TotalValue field in the Orders Table.

11. Check for the End Of File (EOF) condition of the Order Details Table.

12. If False, then repeat the Process from Step 4 onwards; otherwise, Close files and stop running.

Prepare for a Trial Run.

1. To try the above method, Import Orders and Order Details Tables from 'C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb' (Access 2003) or 'C:\Users\User\My Documents\Northwind 2007.accdb' (Access 2007; if not available, then you must create from Local Templates)

2. Open the Orders Table in Design View.

3. Add a new Field named Total Value with a Numeric (Double) data Type in the Orders Table.

   You may display the Index List of this Table to view its Primary Key Index on the Order ID field.

4. Save the Orders Table.

5. Open the VBA Editing Window (Alt+F11).

6. Create a new Standard Module from the Insert Menu.

7. Copy and Paste the following VBA Routine and save the Module.

   The CreateIndex() Function.

   Public Function CreateIndex()
   Dim db As Database, fld As Field, tbldef As TableDef
   Dim idx As Index, rst As Recordset, PreviousOrderID As Long
   Dim CurrentOrderID As LongDim xQuantity As Long, xUnitPrice As Double
   Dim xDiscount As Double, Total As Double, rst2 As Recordset
   
   On Error Resume Next
   
   Set db = CurrentDb
   Set rst = db.OpenRecordset("Order Details", dbOpenTable)
   'Check for presence of myIndex, if found set as current
   rst.Index = "myIndex"
   If Err = 3800 Then
   'myIndex not found
       Err.Clear
       GoSub myNewIndex
   End If
   
   On Error GoTo CreateIndex_Err
   
   Set rst2 = db.OpenRecordset("Orders", dbOpenTable)
   rst2.Index = "PrimaryKey"
   PreviousOrderID = rst![Order ID]
   CurrentOrderID = PreviousOrderID
   Do Until rst.EOF
       Total = 0
       Do While CurrentOrderID = PreviousOrderID
           xQuantity = rst![quantity]
           xUnitPrice = rst![unit price]
           xDiscount = rst![discount]
   
           Total = Total + (xQuantity * ((1 - xDiscount) * xUnitPrice))
           rst.MoveNext
           PreviousOrderID = CurrentOrderID
           If Not rst.EOF Then
               CurrentOrderID = rst![Order ID]
           Else
               Exit Do
           End If
       Loop
       rst2.Seek "=", PreviousOrderID
       If Not rst2.NoMatch Then
           rst2.Edit
           rst2![totalvalue] = Total
           rst2.Update
       End If
       PreviousOrderID = CurrentOrderID
   Loop
   
   rst.Close
   rst2.Close
   
   'Delete temporary Index
   Set tbldef = db.TableDefs("Order details")
   tbldef.Indexes.Delete "myIndex"
   
   CreateIndex_Exit:
   Exit Function
   
   myNewIndex:
   rst.Close
   Set tbldef = db.TableDefs("Order Details")
   Set idx = tbldef.CreateIndex("myIndex")
   
   Set fld = tbldef.CreateField("Order ID", dbLong)
   idx.Fields.Append fld
   Set fld = tbldef.CreateField("Product ID", dbLong)
   idx.Fields.Append fld
   tbldef.Indexes.Append idx
   tbldef.Indexes.Refresh
   Set rst = db.OpenRecordset("Order Details", dbOpenTable)
   rst.Index = "myIndex"
   Return
   
   CreateIndex_Err:
   MsgBox Err.Description, , "CreateIndex()"
   Resume CreateIndex_Exit
   
   End Function

8. Click somewhere in the middle of the VBA Routine and press F5 or click the Run Command Button to execute the Code and update the Orders Table.

At the beginning of the code, we attempt to activate one of the indexes (myIndex) in the Order Details table. Since myIndex has not yet been created, this action triggers an error. The error is trapped, and control is passed to a subroutine that creates myIndex and adds it to the table’s Indexes collection. The new index is then activated in preparation for data processing. 

The next steps calculate Order-wise Total Values and update them in the Orders Table.

At the end of the process, myIndex is deleted from the Indexes Collection of the Order Details Table.

Earlier Post Link References:

• Creating Using Form Custom Property
• MS-Access and Data Processing-2
• MS-Access and Data Processing
• Form Bookmarks and Data Editing-3
• Form Bookmarks and Data Editing-2

Data Upload Controls

Data Upload Controls.

In some projects, we need to regularly import data from external sources such as dBase, Excel, or flat files like CSV and text. These external files can remain linked to the project, allowing their data to be added to a local Microsoft Access table for reporting purposes.

For example, assume that we have an MS Access application that generates monthly business profitability reports. To prepare these reports, we need to upload new data each month from a LAN location, where the file is updated and replaced from a remote source in one of the supported formats.

Our Application has a mechanism to identify when the existing linked file on the LAN Server is replaced with a new file having fresh data. When a new data file is introduced on the Server and replaces the old one, the System detects the new data and enables the CommandButton. This CommandButton is disabled after uploading the data and remains disabled till new external data file overwrites the old one. 

When the MS Access application opens, it compares the previously stored file parameters with those of the current file on the server. If the parameters match, the application assumes that the data has already been uploaded and keeps the DataUpload Command Button disabled. If the parameters do not match, it assumes that new data is available and enables the DataUpload Command Button to allow the upload process.

So, how do we detect new data in the attached file? Depending on the file type, different approaches can be used. One common method is to check the continuity of a control value, such as an Invoice Number, Last Receipt Date, or any other unique identifier that reliably distinguishes new records. These control values from the last uploaded data can be compared with the corresponding values in the attached file. If the values match, we can assume that the data has already been uploaded; otherwise, we can proceed to upload the new records.

To perform this verification, you can design a few queries to extract and filter these key values from both sources, then use a VBA routine to compare them and control the next steps in the upload process.

However, I use a simpler method to check for the new data in the attached file. Before explaining that, we need to consider a few important factors.

The Access Application's back-end Database and the upload data source both must be on the LAN Server. Only one authorized Front-End Database user is permitted to execute the upload process.

Taking these considerations into account, we must design a reliable, controlled method to ensure that data is uploaded correctly and prevent duplication.

External Data Source Files.

I have several applications that upload data from various sources — including IBM AS400 systems, dBase, Excel, and even AS400 report spool files. Over time, I’ve experimented with different methods to detect new information in these files, using queries that compare control data from internal database tables and linked tables.

You might be wondering how I handle the AS400 report spool files, which often have hundreds of pages saved directly to the LAN by the EDP department. These files cannot be linked directly to the database because they don’t follow a proper table structure — except for the detail lines that contain the actual data.

I have developed VBA procedures that will read the spool file line by line and discard unwanted lines, headers, footers, underlines, blank lines, etc., and take only the data lines, cut into text fields in a Table initially before converting each field value into its respective data types and writing it out into a new table.

But the question remains: how can we track the presence of a new report spool file that cannot be directly attached to the database? The solution is quite simple. At the end of each upload operation, I create a control file by copying the first 50 lines of the current spool file. Whenever the application is opened, a small routine compares the first 50 lines of both the current spool file and the control file. If no differences are found, it means the data has already been uploaded into the system. If any variation is detected, the system recognizes it as a new file and prepares to upload the fresh data.

A Common Simple Method is suitable for all Types of Files.

After experimenting with several methods for different file types, I realized the need for a simple, universal approach that could work for all kinds of files—whether attached to the system or not. I eventually developed such a method, which I’m sharing below for your use, should you find it helpful.

We need a small table with the following Fields:

Field Name	Data Type	Field Size
FileLength	Long Integer
FileDateTime	Date/Time
UserName	Text	25
UploadDate	Date/Time
FilePath	Text	255

The sample table in Datasheet View:

Right-click to open Large Image in New Tab/Window.

When the file contents are uploaded, we record some basic information about the attached file—such as its size (in bytes) and its last modified date and time. In addition, we store the name of the user (if Microsoft Access security is implemented) authorized to run the upload process, along with the date and time of the last upload event.

We can read the attached file size in bytes using the Function: FileLen(PathName), and the File's last modified Date and Time can be obtained with the Function FileDateTime(PathName). After the data upload, these values can be updated in the table above to cross-check the external file to check the presence of new data. If needed, we can set the attached file's Read-Only attribute ON using the Function SetAttr(PathName, vbReadOnly), so that the file can be protected from inadvertent changes. It can be reset to Normal with SetAttr(PathName, vbNormal).

A program must be run immediately after the Main Switchboard Form is opened, and cross-check the file size and the File Date/Time recorded in the table with the attached file's attributes. If they are different, then new data has arrived and enabled the Upload CommandButton; the new data can be uploaded.

However, when the application is in open state, and the attached file is replaced by the provider with a new one, the Upload button will remain disabled because the status-checking routine runs only when the main switchboard is opened. Instead of requiring the user to close and reopen the application, a standard but inconvenient procedure, we can add another command button labeled Refresh. When the user clicks this button, the program can recheck the file attributes and enable the Upload button if a new file is detected.

A sample VBA Routine is given below that reads the information from the table and cross-checks with the attributes of the attached file, enabling/disabling the Upload Command Button.

Alternatively, we can run a Timer-Interval Subroutine (at a 1-hour or longer interval) to check the presence of a new source file when the Access Application is active.

The UploadControl() Function Code.

Public Function UploadControl(ByVal frmName As String)
'------------------------------------------------------
'Author   : a.p.r. pillai
'Date     : January-2010
'Remarks  : Data Upload control Routine
'         : All Rights Reserved by www.msaccesstips.com
'------------------------------------------------------
Dim frm As Form, lnglastFileSize, dtlastModified, txtFilePath
Dim lngExternalFileSize, dtExternalModified, authUser
Dim tblControl As String, cmdCtrl As CommandButton

tblControl = "UploadCtrl"
authUser = "LizzaMinnelli"
Set frm = Forms(frmName)
Set cmdCtrl = frm.Controls("cmdUpload")

'Read last recorded information from the Control Table
lnglastFileSize = DLookup("FileLen", tblControl)
dtlastModified = DLookup("FileDateTime", tblControl)
txtFilePath = DLookup("FilePath", tblControl)

'Get the External File information
lngExternalFileSize = FileLen(txtFilePath)
dtExternalModified = FileDateTime(txtFilePath)

If (lngExternalFileSize <> lnglastFileSize) And (dtlastModified <> dtExternalModified) Then
    If CurrentUser = authUser Then
        cmdCtrl.Enabled = True
    Else
        cmdCtrl.Enabled = False
    End If
End If

End Function

The Main Switch Board, which has a Command Button with the name cmdUpload, should call the above Program through the Form_Current() Event Procedure of the Form, passing the Form Name as Parameter, like the following example:

Private Sub Form_Current()
    UploadControl Me.Name
End Sub

If the uploading authorisation is assigned to a particular User, then the Current User's User ID (retrieved with the function CurrentUser()) can also be checked with the UserName Field Value before enabling the Command Button cmdUpload.

• Run Slideshow when Form is idle
• Filter Function Output in ListBox-2
• Filter Function Output in ListBox
• Dynamic List Box Contents
• Office Assistant and MsgBox Menus-3

Auto Numbering In Query Column

Auto Numbering In Query Column.

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

On The Reports.

On Reports, create a TextBox in the 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 starting with 1 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, set the Overall All option for continuous numbers from the start of the Report to the End.

If you want to create a 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.

In The Query Column.

However, asking for auto-numbering in a query column might seem unusual—unless the query results are meant for display purposes or the output requires sequence numbers for a specific reason.

Products Category Group-level sequence numbers or for creating a Rank List for students based on their obtained marks, and so on.

Or after filtering the records in the Query, the Auto-number field values are out of sequence.

This requirement was actually raised by a participant in an online MS Access Users Forum. No one, including myself, was able to suggest a definitive solution, only some alternatives. I offered a solution of my own, even though I wasn’t entirely satisfied with it either.

The Access User who raised the question in the Forum asked for a solution via email.

This prompted me to revisit the topic and experiment with a few simple methods. Eventually, I developed a function that accomplishes the task, and I’m sharing it here so that you can try it out too.

Need Trial and Error Runs.

It is important to understand how to use the QrySeq() function in a new query column to generate sequence numbers. The function must be called with specific parameter values, often derived from the query’s own columns. Before presenting the VBA code for the function, the details of its parameters are explained below.

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

Syntax: Target Column Name: QrySeq([Field Value], "Field Name", "Query Name")

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

The QrySeq() Function needs three Parameters.

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

2. The second Parameter is the Column Name of the first parameter in Quotes.

3. The third Parameter is the Name of the Query from which you call the Function.

The query from which the QrySeq() function is called should include a column of unique values, such as an AutoNumber or a Primary Key field. If such a column is not readily available, you can create one by combining two or more existing fields—for example:

NewColumn: [OrderID] & [ShipName] & [RequiredDate] & [Quantity] 

Ensure that this concatenation produces unique values for all records, and then pass this column ([NewColumn]) as the first parameter to the function.

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

The Name of the Query must be passed as the 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, to create the Sequence Numbers correctly.

The QrySeq() Function Code.

The simple rules are in place, and 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 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

   The Sample Trial Run.

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 the File Menu or click the Save Toolbar Button.

5. Open the Query in the normal view.

Check the SRLNO Column for Sequence Numbers.

In this case, the OrderID field in the Orders table already contains unique values, so we can generate sequence numbers directly in the SRLNO column without any additional steps.

However, if the query does not contain a single column with unique values, we must create one by combining two or more existing query columns. This newly created column with unique values can then be passed 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 Datasheet View to check whether the Serial Numbers were created correctly.

Ensuring Accuracy.

When a query contains hundreds or thousands of records, it is impractical to manually verify that the column values passed to the QrySeq() function are truly unique and that the generated serial numbers contain no duplicates. Instead, we can use a Total Query to count serial numbers that appear more than once. For this, we use the AutoNumberQuery2 as the source, which allows us to quickly identify any duplicate serial numbers in the dataset.

1. Create a new Query that uses 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.

The result will show that the SRLNO column contains the same number appearing more than once in the records. This indicates that the column values of the QrySeq() function are not unique and contain duplicates.

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 serves as an alternative when an AutoNumber or Primary Key field is not available, and it does not guarantee 100% accuracy. When additional records are added to the source table, the method may fail again. In such cases, the only solution is to combine more fields in the NewColumn expression to reduce the likelihood of duplicates and ensure uniqueness.

To correct the query above, include the [Freight] column in the NewColumn expression. Alternatively, you can copy and paste the following SQL string into the AutoNumberQuery2 query, overwriting the previous SQL, and then 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 will be correct.

If you know a better solution, please share it with me. I’m not looking for a refinement of the existing code or method, but for a different approach that can achieve the same—or even better—results.

Improved Versions related to this topic:

Find New Auto-Numbers in Query Column Version-2 on this link.

For creating Running Sum Values in the Query Column, visit the following link:

Running Sum in MS-Access Query.

Next:

Autonumber with Date and Sequence Number.

Download

Download Demo QryAutoNum.zip

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.