Indexing and Sorting with VBA

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:

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 index and use it 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 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 the same, then repeat steps 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 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 Orders Table in Design View.

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

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

Read More

Data Upload Controls

Introduction

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.

If our Application is designed in such a way that when the existing linked file is replaced with a new one on a LAN, with the same name, then the Project must be able to identify the presence of new data in the attached file and allow the user to upload the new records into the master file. But, before this action is implemented, it becomes our responsibility as developers to see that the data once uploaded is not duplicated in the System.

To accomplish this, we need to implement a procedure that checks for the presence of new data in the attached file. If new data is detected, the procedure should enable a command button that allows the user to upload the data into the system. If no new data is found, the command button should remain disabled until fresh data becomes available in the linked file.

So, how do we detect the presence of 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 presence of 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, so that it can be better managed and kept secure.

Taking these considerations into account, we must design a reliable and controlled method to ensure that data is uploaded accurately into the database.

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 the presence of 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 contain 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 like Header, Footer, 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 accordingly.

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 perform 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 upload action, these values can be updated in the table above to cross-check with the values of the external file to determine the presence of new data. If needed, we can set the attached file's Read-Only attribute ON with 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 open and cross-check the file size and the File Date/Time recorded in the table with the attached file's attributes. If found different, then we can be sure that new data has arrived and enabled the Upload Command Button, so that the user can upload new data.

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 (at a 1-hour interval) controlled function 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

Read More

Auto Numbering In Query Column

Introduction

We know how to create an Auto-number Field in a Table to generate Unique Sequence numbers automatically for the records added to the Table. We know how to give 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 MS Access Users Forum online. No one, including myself, was able to provide 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 made direct contact by sending an email to me asking for a solution.

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 use it if needed.

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 columns in the query. 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 normal View to check whether the Serial Numbers were created correctly or not.

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 created for the QrySeq() function are not truly 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 only 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 generated will be correct.

Found a Different Method, share it with me.

If you know of 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.

Read More