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:
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
myIndexfor a table through VBA. -
Activate the required index 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 the Orders Table for Update Mode.
Open the Order Details Table for Input.
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.
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 it is the same, then repeat steps 6 and 7 until the Order Number changes, or there are no more records to process in 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 the Orders Table in Design View.
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.
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 FunctionClick 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:
