Indexing and Sorting with VBA

Posted: 28 Jan 2010 05:47 AM PST

A Table is normally created with a Primary Key or Index to arrange the records into 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.

If you open the Employees Table from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb sample Database in design view you can see that the EmployeeID Field is defined as Primary Key.

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

  1. Open the Table in design View.

  2. Click at 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 PrimaryKey, 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 for creating a PrimaryKey Index for the Table. We can create more than one Index for a Table. But, only one Index can be active at one time.

We can activate an existing Index of a Table or create 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 and 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 Orders and Order details Table from Northwind.mdb sample database. We will organize the Order Details Table in Order Number sequence so that Order-wise Total Value of all items can be calculated and updated on the same Order record in Orders Table.

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 current Index.

  4. Initialize Total to Zero.

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

  6. Calculate 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 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 Order Details Table.

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

  10. If found then edit and updateTotal into the TotalValue Field in Orders Table.

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

  12. If False then repeat the Process from Step-4 onwards, otherwise Close files and stop 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 TotalValue with Numeric (Double) data Type in Orders Table.

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

  5. Save the Orders Table.

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

  7. Create a new Standard Module from Insert Menu.

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

  9. 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 Long
    Dim 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
    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))
    PreviousOrderID = CurrentOrderID
    If Not rst.EOF Then
    CurrentOrderID = rst![Order ID]
    Exit Do
    End If
    rst2.Seek "=", PreviousOrderID
    If Not rst2.NoMatch Then
    rst2![totalvalue] = Total
    End If
    PreviousOrderID = CurrentOrderID


    'Delete temporary Index
    Set tbldef = db.TableDefs("Order details")
    tbldef.Indexes.Delete "myIndex"

    Exit Function

    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
    Set rst = db.OpenRecordset("Order Details", dbOpenTable)
    rst.Index = "myIndex"

    MsgBox Err.Description, , "CreateIndex()"
    Resume CreateIndex_Exit

    End Function

  10. 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 Order Details Table active. Since, myIndex is not yet created on the Table this action runs into an Error condition. we are trapping this Error Code and passes 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.

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.

StumbleUpon Toolbar


Post a Comment

Note: Only a member of this blog may post a comment.


©2009 Programming & Security | Template Blue by TNB

Vida de bombeiro Recipes Informatica Humor Jokes Mensagens Curiosity Saude Video Games Diario das Mensagens Eletronica Rei Jesus News Noticias da TV Artesanato Esportes Noticias Atuais Games Pets Career Religion Recreation Business Education Autos Academics Style Television Programming Motosport Humor News The Games Home Downs World News Internet Design Entertaimment Celebrities 1001 Games Doctor Pets Net Downs World Enter Jesus Mensagensr Android Rub Letras Dialogue cosmetics Genexus Só Humor Curiosity Gifs Medical Female American Health Madeira Designer PPS Divertidas Estate Travel Estate Writing Computer Matilde Ocultos futebolcomnoticias girassol lettheworldturn topdigitalnet Bem amado enjohnny produceideas foodasticos cronicasdoimaginario downloadsdegraca compactandoletras newcuriosidades blogdoarmario arrozinhoii sonasol halfbakedtaters make-it-plain amatha lisboaohyeah lasofia thebushrajr wingshock tripedes gorduravegetal dainfamia dejavu-transpersonal jsbenfica republicadasbadanas ruiherbon iranianforaryans eaystcheyl fotosdanadir ojosmasabiertos ceilasantos