Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Indexing and Sorting with VBA

Introduction

A Table is normally created with a Primary Key or Index to arrange the records in a certain order to view or process. Primary Key or Index can have one or more fields, in order to make the Key values Unique, if this is not possible with a single field value.

If you open the Employees Table from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb sample Database in the design view you can 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 Record Values in the selected field are not unique then you can select more data fields (up to a maximum of ten Fields) to form Unique Key for the Primary Key.

You may click and drag over the Fields to select them (if they are adjoining fields) or click on each field by holding the Ctrl Key to select fields randomly.

The above procedure is creating a Primary Key Index for the Table. We can create more than one Index for a Table. But, only one Index can be active at one time.

Creating Index with VBA.

We can activate an existing Index of a Table or create a new Index through VBA and use it for data processing. We will learn here how to create a new Index with the name myIndex for a Table through VBA, activate it, use it for data processing and delete it at the end of the process.

We must validate the presence of myIndex in the Indexes collection of the Table if found, then activate it, otherwise create myIndex and activate it for data processing.

We will use the Orders and Order details Table from the Northwind.mdb sample database. We will organize the Order Details Table in the Order Number sequence so that the Order-wise Total Value of all items can be calculated and updated on the same Order record in Orders Table.

The Data Processing Steps

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

  1. Open Orders Table for Update Mode.

  2. Open Orders Details Table, for Input.

  3. 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.

  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 same then repeat step 6 and 7 until the Order Number changes or no more records to process from the Order Details Table.

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

  10. If found, then edit and update the total into the total value Field in Orders Table.

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

  12. If False then repeats the Process from Step 4 onwards, otherwise Close files, and stop Run.

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 Orders Table in Design View.

  3. Add a new Field with the name Total Value with Numeric (Double) data Type in 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 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 Run Command Button to execute the Code and update the Orders Table.

At the beginning part of the Code, we are attempting to make one of the Indexes (myIndex) of the Order Details Table active. Since myIndex is not yet created in the Table this action runs into an Error condition. We are trapping this Error Code and passing control to the Sub-Routine to create myIndex and to add it to the Indexes collection. The new Index is activated in preparation for data processing.

The next steps calculate Order-wise Total Values and updates on Orders Table.

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

Earlier Post Link References:

Share:

No comments:

Post a Comment

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

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