Indexing and Sorting with VBA ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction

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:

Open the Table in Design View.
Click on the left side of the Field Name to select it.
Click on the Indexes Toolbar Button.
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:

Create a new index named myIndex for a table through VBA.
Activate the index and use it for data processing.
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:

Open Orders Table for Update Mode.
Open Orders Details Table for Input.
Check for the presence of myIndex in the Order Details Table. If found, then activate it; otherwise, create myIndex and activate it as the current Index.
Initialize the Total to Zero.
Read the first record from the Order details Table.
Calculate the Total Value of the item using the Expression: Quantity * ((1-Discount%)*UnitPrice).
Add the Value to the Total.
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 no more records to process from the Order Details Table.
Find the record with the Order Number in the Orders Table.
If found, then edit and update the Total to the totalvalue Field in the Orders Table.
Check for the End Of File (EOF) condition of the Order Details Table.
If False, then repeat the Process from Step 4 onwards; otherwise, Close files, and stop running.

Prepare for a Trial Run.

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)
Open Orders Table in Design View.
Add a new Field with the name 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.
Save the Orders Table.
Open the VBA Editing Window (Alt+F11).
Create a new Standard Module from the Insert Menu.

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

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.