LEARN MS-ACCESS TIPS AND TRICKS: Data Type

Showing posts with label Data Type. Show all posts

MS-Access Recordset and Class Module

a.p.r. pillaiNovember 25, 2018Class Module, Data Type, Field Type, Recordset No comments

MS Access Recordset and Class Module.

Sorting and Updating the Table Records.

In this section, we will create a Class Module designed for data processing tasks. An DAO.Recordset object will be passed to the custom class. Since we are passing an object to our custom class, we need to implement a pair of Property Set and Property Get procedures to assign the object to the class and to retrieve the object or its property values when required.

We have a small Table (Table 1) with a few records. Here is the image of Table 1.

Right-click to open Large Image in New Tab/Window.

The table above has only four fields: Description, Quantity, Unit Price, and Total Price. The Total Price field is empty.

One of the tasks in this tutorial is to update the TotalPrice field with the product of Qty * UnitPrice in our Class Module.
The Class Module includes a subroutine that sorts the records on a user-specified column, and the output is listed in the Debug Window.
Another subroutine creates a copy of the Table with a new name for the sorted data.

ClsRecUpdate Class Module.

Open your Access Database and open the VBA Window.
Insert a Class Module.
Change the Name Property Value to ClsRecUpdate.

Copy and Paste the following Code into the Class Module and save the Module:

Option Compare Database
Option Explicit

Private rstB As DAO.Recordset

Public Property Get REC() As DAO.Recordset
   Set REC = rstB
End Property

Public Property Set REC(ByRef oNewValue As DAO.Recordset)
If Not oNewValue Is Nothing Then
   Set rstB = oNewValue
End If
End Property

Public Sub Update(ByVal Source1Col As Integer, ByVal Source2Col As Integer, ByVal updtcol As Integer)
'Updates a Column with the product of two other columns
Dim col As Integer

col = rstB.Fields.Count

'Validate Column Parameters
If Source1Col > col Or Source2Col > col Or updtcol > col Then
    MsgBox "One or more Column Number(s) out of bound!", vbExclamation, "Update()"
    Exit Sub
End If

'Update Field
On Error GoTo Update_Err
rstB.MoveFirst
Do While Not rstB.EOF
   rstB.Edit
     With rstB
      .Fields(updtcol).Value = .Fields(Source1Col).Value * .Fields(Source2Col).Value
      .Update
      .MoveNext
     End With
Loop

Update_Exit:
rstB.MoveFirst
Exit Sub

Update_Err:
MsgBox Err & " : " & Err.Description, vbExclamation, "Update()"
Resume Update_Exit
End Sub

Public Sub DataSort(ByVal intCol As Integer)
Dim cols As Long, colType
Dim colnames() As String
Dim k As Long, colmLimit As Integer
Dim strTable As String, strSortCol As String
Dim strSQL As String
Dim db As Database, rst2 As DAO.Recordset

On Error GoTo DataSort_Err

cols = rstB.Fields.Count - 1
strTable = rstB.Name
strSortCol = rstB.Fields(intCol).Name

'Validate Sort Column Data Type
colType = rstB.Fields(intCol).Type
Select Case colType
    Case 3 To 7, 10
        strSQL = "SELECT " & strTable & ".* FROM " & strTable & " ORDER BY " & strTable & ".[" & strSortCol & "];"
        Debug.Print "Sorted on " & rstB.Fields(intCol).Name & " Ascending Order"

    Case Else
        strSQL = "SELECT " & strTable & ".* FROM " & strTable & ";"

        Debug.Print "// SORT: COLUMN: <<" & strSortCol & " Data Type Invalid>> Valid Type: String,Number & Currency //"
        Debug.Print "Data Output in Unsorted Order"
End Select

Set db = CurrentDb
Set rst2 = db.OpenRecordset(strSQL)

ReDim colnames(0 To cols) As String

'Save Field Names in Array to Print Heading
For k = 0 To cols
   colnames(k) = rst2.Fields(k).Name
Next

'Print Section
Debug.Print String(52, "-")

'Print Column Names as heading
If cols > 4 Then
   colmLimit = 4
Else
   colmLimit = cols
End If
For k = 0 To colmLimit
    Debug.Print colnames(k),
Next: Debug.Print
Debug.Print String(52, "-")

'Print records in Debug window
rst2.MoveFirst
Do While Not rst2.EOF
  For k = 0 To colmLimit 'Listing limited to 5 columns only
     Debug.Print rst2.Fields(k),
  Next k: Debug.Print
rst2.MoveNext
Loop

rst2.Close
Set rst2 = Nothing
Set db = Nothing

DataSort_Exit:
Exit Sub

DataSort_Err:
MsgBox Err & " : " & Err.Description, vbExclamation, "DataSort()"
Resume DataSort_Exit

End Sub

Public Sub TblCreate(Optional SortCol As Integer = 0)
Dim dba As DAO.Database, tmp() As Variant
Dim tbldef As DAO.TableDef
Dim fld As DAO.Field, idx As DAO.Index
Dim rst2 As DAO.Recordset, i As Integer, fldcount As Integer
Dim strTable As String, rows As Long, cols As Long

On Error Resume Next

strTable = rstB.Name & "_2"
Set dba = CurrentDb

On Error Resume Next
TryAgain:
Set rst2 = dba.OpenRecordset(strTable)
If Err > 0 Then
  Set tbldef = dba.CreateTableDef(strTable)
  Resume Continue
Else
  rst2.Close
  dba.TableDefs.Delete strTable
  dba.TableDefs.Refresh
  GoTo TryAgain
End If
Continue:
On Error GoTo TblCreate_Err

fldcount = rstB.Fields.Count - 1
ReDim tmp(0 To fldcount, 0 To 1) As Variant

'Save Source File Field Names and Data Type
For i = 0 To fldcount
    tmp(i, 0) = rstB.Fields(i).Name: tmp(i, 1) = rstB.Fields(i).Type
Next
'Create Fields and Index for new table
For i = 0 To fldcount
   tbldef.Fields.Append tbldef.CreateField(tmp(i, 0), tmp(i, 1))
Next
'Create index to sort data
Set idx = tbldef.CreateIndex("NewIndex")
With idx
   .Fields.Append .CreateField(tmp(SortCol, 0))
End With
'Add Tabledef and index to database
tbldef.Indexes.Append idx
dba.TableDefs.Append tbldef
dba.TableDefs.Refresh

'Add records to the new table
Set rst2 = dba.OpenRecordset(strTable, dbOpenTable)
rstB.MoveFirst 'reset to the first record
Do While Not rstB.EOF
   rst2.AddNew 'create record in new table
    For i = 0 To fldcount
        rst2.Fields(i).Value = rstB.Fields(i).Value
    Next
   rst2.Update
rstB.MoveNext 'move to next record
Loop
rstB.MoveFirst 'reset record pointer to the first record
rst2.Close

Set rst2 = Nothing
Set tbldef = Nothing
Set dba = Nothing

MsgBox "Sorted Data Saved in " & strTable

TblCreate_Exit:
Exit Sub

TblCreate_Err:
MsgBox Err & " : " & Err.Description, vbExclamation, "TblCreate()"
Resume TblCreate_Exit

End Sub

The 'rstB' Property is declared as a 'DAO.Recordset' Object.

Through the Set Property Procedure, a Recordset object can be passed to the ClsRecUpdate Class Object.

The Update() Subroutine accepts three column numbers (0-based column indexes) as parameters to calculate and update the third column with the product of the 'first column' x 'second column'.

The DataSort() subroutine sorts the records in ascending order based on the Column Number passed as a parameter.

The Sorting Column data type must be either Number, Currency, or String. Other data types are ignored. The Recordset column numbers are 0-based, meaning the first column number is 0, the second column 1, and so on.

A listing of the records will be displayed in the Debug Window. The output will be limited to the first five fields; if the record source contains more than five fields, the remaining fields will be ignored.

The TblCreate() subroutine sorts the data based on the column number specified as a parameter and creates a new data table with a modified name. The parameter is optional; if no column number is specified, the data will be sorted by the first column (provided its data type is valid). The new table will retain the original table name with “_2” appended. For example, if the source table is namedTable1 The newly created table will be named Table1_2.

The Test Program for ClsUpdate.

Let us test the ClsRecUpdate Class Object with a small Program.

The test program code is given below:

Public Sub DataProcess()
Dim db As DAO.Database
Dim rstA As DAO.Recordset

Dim R_Set As ClsRecUpdate
Set R_Set = New ClsRecUpdate

Set db = CurrentDb
Set rstA = db.OpenRecordset("Table1", dbOpenTable)

'send Recordset Object to Class Object
Set R_Set.REC = rstA

'Update Total Price Field
Call R_Set.Update(1, 2, 3) 'col3=col1 * col2

'Sort Ascending Order on UnitPrice column & Print in Debug Window
Call R_Set.DataSort(2)

'Create New Table Sorted on UnitPrice in Ascending Order
Call R_Set.TblCreate(2) 
Set rstA = Nothing
Set db = Nothing
xyz:
End Sub

You may pass any Recordset to test the Class Object.

You can specify any column numbers when updating a particular column; they do not need to be consecutive. The third column number parameter identifies the target column to be updated. The result will be Column3 = Column1 * Column2. You may modify the Class Module code to perform any other operation on the table as needed.

List of All the Links on this Topic.

MS-Access Base Class and Derived Objects

a.p.r. pillaiOctober 30, 2018Class Module, Data Type, Expression, Form Instances, Object Reference, Objects, Property No comments

MS Access Base Class and Derived Objects

Object Encapsulation.

If you have not seen the earlier Posts on the Microsoft Access Class Module, please go through them before continuing. The links are given below.

ClsArea A Class can serve as a base class for other Class objects, allowing its calculations to be reused for more advanced operations. For instance, it could be integrated into a class designed to calculate the volume of a cube, where area is only one step in the overall computation.

The dbl prefix in the dblLength and dblWidth Property procedure names simply indicate that these properties expect double-precision numeric values. Similarly, if we were to rename the property procedures to Quantity and UnitPrice Multiplying one by the other would yield some item's Total Price.

This demonstrates the flexibility of the ClsArea class. Wherever the result of multiplying two values is required—such as TotalPrice * TaxRate to compute tax, or TotalPrice * DiscountRate to determine a discount—it can be adapted as a base class to fit the scenario.

Although we started with a simple class module, it can be part of many other derived classes. The possibilities are limited only by your imagination and creativity.

Currently, our ClsArea Class calculates the area of materials, rooms, or similar objects using only the Length and Width properties. It does not yet support shapes like triangles or circles. However, it can be extended into a new class object that calculates the volume of rooms or warehouses to determine storage capacity. To achieve this, we would simply introduce an additional property, such as Height, into the design.

The Volume Class: ClsVolume.

Let’s now create a new class module named ClsVolume, using ClsArea as its base class.

Insert a new Class Module.
In the Properties Window, change the Name property to ClsVolume.
Type or copy and paste the following code into the class module:

Option Compare Database
Option Explicit

Private p_Area As ClsArea
Private p_Height As Double

Private Sub Class_Initialize()
    Set p_Area = New ClsArea
End Sub

Private Sub Class_Terminate()
    Set p_Area = Nothing
End Sub

Public Property Get dblHeight() As Double
    dblHeight = p_Height
End Property 

Public Property Let dblHeight(ByVal dblNewValue As Double)
   Do While Val(Nz(dblNewValue, 0)) <= 0
      dblNewValue = InputBox("Negative/0 Values Invalid:", "dblHeight()", 0)
    Loop
    p_Height = dblNewValue
End Property

Public Function Volume() As Double

If (p_Area.Area() > 0) And (p_Height > 0) Then
    Volume = p_Area.Area * p_Height
Else
    MsgBox "Enter Valid Values for Length,Width and Height.", , "ClsVolume"
End If

End Function

Let’s examine the code line by line. On the third line, we declare a private member p_Area of type ClsArea — a reference to an ClsArea instance that this class will use internally. The next line declares a private field p_Height As Double to store the height value. Both members use the p_ prefix to indicate private scope; they will be accessed and validated through property procedures rather than directly from outside the class.

The Class_Initialize() and Class_Terminate() Sub-Routines.

The next two subroutines—Class_Initialize() and Class_Terminate()—play a crucial role in the ClsVolume class.

Class_Initialize() runs automatically when an ClsVolume object is created in a standard module. Within this routine, we instantiate the ClsArea object in memory, ensuring that all of its functionality is available to the new class.
Class_Terminate() is triggered when we explicitly clear the ClsVolume object with a statement such as Set ClsVolume = Nothing. At this point, the subroutine ensures that the memory allocated to the ClsArea object is also released properly.

Property Get dblHeight The procedure simply returns the current value of the private field p_Height to the calling program.

Property Let dblHeight The procedure validates the value passed into the NewValue parameter before assigning it to the private property p_Height. This safeguard prevents invalid values (such as zero or negative numbers) from being stored.

The Public Function Volume() calculates the volume by calling the p_Area.Area() function. The returned area value is then multiplied by p_Height using the expression:


Volume = p_Area.Area * p_Height

Before executing this calculation, a validation check ensures that p_Area.Area() returns a value greater than zero (which confirms that both p_Area.dblLength and p_Area.dblWidth contain valid values) and that the p_Height Property is also greater than zero. Only when all three properties hold valid values is the volume calculation performed.

Note: Since the p_Area object of the ClsArea Class is defined as a private member of the ClsVolume class, we must expose its properties (strDesc, dblLength, dblWidth) and its Area() function to the outside world. This is done by creating corresponding Get/Let property procedures in the ClsVolume class module, effectively making these members accessible for use while maintaining encapsulation. The Let/Get Property Procedures.

Here’s the refined code you can add to your ClsVolume class module. These procedures expose the strDesc, dblLength, dblWidth, and Area() members of the private p_Area object to the outside world:

Public Property Get strDesc() As String
   strDesc = p_Area.strDesc
End Property

Public Property Let strDesc(ByVal NewValue As String)
   p_Area.strDesc = NewValue
End Property

Public Property Get dblLength() As Double
   dblLength = p_Area.dblLength
End Property

Public Property Let dblLength(ByVal NewValue As Double)
   p_Area.dblLength = NewValue
End Property

Public Property Get dblWidth() As Double
   dblWidth = p_Area.dblWidth
End Property

Public Property Let dblWidth(ByVal NewValue As Double)
   p_Area.dblWidth = NewValue
End Property

Public Function Area() As Double
    Area = p_Area.Area()
End Function

Check the strDesc property procedures (Get/Let) carefully. The choice of the procedure name strDesc is arbitrary—you could use a different name if you prefer. However, since the original property in the ClsArea class is also named strDesc Reusing the same name here helps to maintain a clear connection with the base class and makes the relationship between the two classes more intuitive.

In the Get dblLength() property procedure, the expression to the right of the equals sign p_Area.dblLength retrieves the stored length value from the ClsArea object and returns it to the calling program.

The corresponding Let procedure assigns the incoming parameter value (NewValue) to the p_Area.dblLength property of the ClsArea object. Notice that we do not run a separate validation check here—the validation is already enforced within the ClsArea class itself when the value is assigned.

The same logic applies to the dblWidth property. Its Get and Let procedures expose the corresponding property of the p_Area object while delegating validation to the base class.

Finally, the p_Area.Area() function is surfaced through the ClsVolume class, making it directly accessible to the calling program. This ensures that the Area method defined in the base class can be reused seamlessly in the derived class.

The ClsVolume Derived Class Module Code.

The completed code of the ClsVolume Class Module is given below.

Option Compare Database
Option Explicit

Private p_Area As ClsArea
Private p_Height As Double

Private Sub Class_Initialize()

‘Open ClsArea Object in Memory with the name p_Area
    Set p_Area = New ClsArea 

End Sub

Private Sub Class_Terminate()

‘Removes the Object p_Area from Memory
    Set p_Area = Nothing ‘
End Sub

Public Property Get dblHeight() As Double
    dblHeight = p_Height
End Property

Public Property Let dblHeight(ByVal dblNewValue As Double)

Do While Val(Nz(dblNewValue, 0)) <= 0
      dblNewValue = InputBox("Negative/0 Values Invalid:", "dblHeight()", 0)
    Loop
     p_Height = dblNewValue

End Property

Public Function Volume() As Double

If (p_Area.Area() > 0) And (Me.dblHeight > 0) Then
    Volume = p_Area.Area * Me.dblHeight
Else    

MsgBox "Enter Valid Values for Length,Width and Height.",vbExclamation , "ClsVolume"
End If

End Function

‘ClsArea Class Property Procedures and Method are exposed here

Public Property Get strDesc() As String
   strDesc = p_Area.strDesc
End Property 

Public Property Let strDesc(ByVal NewValue As String)
   p_Area.strDesc = NewValue
End Property

Public Property Get dblLength() As Double
   dblLength = p_Area.dblLength
End Property

Public Property Let dblLength(ByVal NewValue As Double)
   p_Area.dblLength = NewValue
End Property

Public Property Get dblWidth() As Double
   dblWidth = p_Area.dblWidth
End Property

Public Property Let dblWidth(ByVal NewValue As Double)
   p_Area.dblWidth = NewValue
End Property

Public Function Area() As Double
    Area = p_Area.Area()
End Function

At this point, you might be thinking: “This feels like double work—wouldn’t it be better if we could somehow skip the repeated property procedures in the ClsVolume class?” Or perhaps, “Why not just add the dblHeight property directly into ClsArea and calculate both Area() and Volume() from there?”

That’s a fair question. But the purpose here is to demonstrate how a base class object becomes part of the design of another Class Object. This pattern shows the power of reusability and encapsulation in VBA class design.

The key benefit of building reusable class modules is that your main programs remain simple, while all the complexity stays hidden inside the class object. This separation makes your code easier to maintain, reuse, and extend.

Of course, there are compact approaches that could reduce code repetition—we’ll explore those later. For now, let’s continue with our original plan so you can fully understand the step-by-step process.

The Main Program that Uses the ClsVolume Class.

Let us test our new ClsVolume Class in the main Program in the Standard Module. The sample code is given below.

Public Sub TestVolume()
Dim vol As ClsVolume

Set vol = New ClsVolume

vol.strDesc = "Warehouse"
vol.dblLength = 25
vol.dblWidth = 30
vol.dblHeight = 10

Debug.Print "Description", "Length", "Width", "Height", "Area", "Volume"

With vol
    Debug.Print .strDesc, .dblLength, .dblWidth, .dblHeight, .Area(), .Volume()
End With
End Sub

You can see how simple the main program is, without the printing lines.

Copy and paste the code into a Standard Module. If the Immediate (Debug) Window is not already open, press Ctrl+G to display it. Next, click anywhere inside the code and press F5 to run the procedure.

The sample output displayed in the Debug Window should look similar to the example shown below: Description Length Width Height Area Volume

Warehouse      25            30            10            750           7500

Description Length Width Height Area Volume
Warehouse 25 30 10 750 7500

Validation Checks Performance Tests.

Next, we will run a few tests to confirm that the validation checks built into the base class (ClsArea) still work when values are passed through the ClsVolume class. Recall that we also added additional validation in the Area() and Volume() functions.

Let’s test them one at a time.

Test 1: Pass a negative value to the ClsArea.dblLength property through the ClsVolume class. This should immediately trigger the error message and invoke the InputBox() function within the Do While…Loop, prompting you to enter a valid (positive) value.

1. Replace the Value 25 with -5 in the line Vol.dblLength = 25 and press the F5 Key to run the Code.

The validation check will trigger the error and will ask for a value greater than zero. Enter a value greater than 0. After that, restore the value to 25.

2. Disable the line Vol.dblHeight = 10 by inserting a comment symbol at the beginning of the line as shown: Vol.dblHeight = 10. After the change, press the F5 Key to run the Code.

If no input values are provided for the properties, the Volume() Function will generate an error, indicating that all three properties—dblLength, dblWidth, and dblHeight—must contain valid values before the calculation can proceed.

In the same way, you can also test the behavior of the Area() function to verify that it responds correctly to missing or invalid inputs.

To make testing easier, we can create a data-printing function that accepts an ClsVolume object as a parameter and prints its property values and calculated results in the Debug Window.

The Changed Code of Main Programs.

The changed Code for both Programs is given below:

Public Sub TestVolume()
Dim Vol As ClsVolume

Set Vol = New ClsVolume

Vol.strDesc = "Warehouse"
Vol.dblLength = 25
Vol.dblWidth = 30
Vol.dblHeight = 10

Call CVolPrint(Vol)

Set Vol = Nothing

End Sub

Public Sub CVolPrint(volm As ClsVolume)

   Debug.Print "Description", "Length", "Width", "Height", "Area", "Volume"  
With volm
    Debug.Print .strDesc, .dblLength, .dblWidth, .dblHeight, .Area, .Volume
End With

End Sub

Next week, we will build the Volume Class Object.

The Links of All Pages in this Series.

User-Defined Data Type-3

a.p.r. pillaiSeptember 16, 2018Array, Data, Data Type, How Tos 1 comment

User-Defined Data Type 3.

Last week, we learned how to define a User-Defined Data Type (UDT) and how to use it in programs. If you’ve just landed on this page, it’s recommended that you first examine the earlier post “User-Defined Data Type–2” before proceeding.

A user-defined type is declared within the [Private/Public] Type… End Type structure. Immediately after the Type keyword, you assign a name to the data type. The actual data-holding variables are defined as individual elements inside the structure, typically using built-in variable types such as String, Integer, Long, Double, or Variant.

In addition to built-in variable types, you can also use other user-defined types as elements. These act as child elements within a user-defined type — and that is what we explore here.

Combining Different Sets of User-Defined Types.

First, we will declare several User-Defined Types (UDTs) to represent different categories of information for employee records — such as Home Address, Qualification, and Experience.

Each of these groups will be defined separately, with its own related data elements. Later, we will combine these groups, along with other individual fields, under a single UDT named Employee.

The following layout shows how these individual groups of employee information are structured separately, before being organized together under the common Employee user-defined type.

Qualification

Desc1

Desc2

Experience

Desc1

Desc2

Address and Date of Birth

Address1

Address2

City

State

BirthDate

Employee Details

Employee Name

Designation

Join Date

Monthly Performance Score(1 to 12)

Salary

Declaring the Data Types

First, let us declare the Data Types:

Option Compare Database

Public Type Qualification
    Q_Desc1 As String
    Q_Desc2 As String
End Type

Public Type Experience
     X_Desc1 As String
     X_Desc2 As String
End Type

Public Type BioData
    Address1 As String
    Address2 As String
    City As String
    State As String
    PIN As String
    BirthDate As Date
End Type

Public Type Employee
        E_Name As String * 45
        E_Desig As String * 25
        E_JoinDate As Date
        E_PerfScore(1 To 12) As Double
        E_Salary As Double
End Type

BioData Redefined with Qualification and Experience.

The Qualification and Experience user-defined types are now incorporated as child elements within the BioData type.

After this modification, the structure of the BioData type is similar to the code shown below.

Public Type BioData Address1 As String Address2 As String City As String State As String PIN As String BirthDate As Date Q_Desc As Qualification X_Exp As Experience End Type

The Qualification user-defined type contains two String data types. Similarly, the Experience type also includes two String elements.

The BioData type consists of address details and date of birth as its own elements. In addition, the Qualification and Experience types are embedded as child elements within the BioData type.

Employee Data Type combined with modified BioData Type.

We will now define the BioData data type as a child element of the Employee data type. The BioData element will contain two sub-elements: Qualification and Experience.

The Employee Data Type with BioData Type as a child element.

Public Type Employee E_Name As String * 45 E_Desig As String * 25 E_JoinDate As Date E_PerfScore(1 To 12) As Double E_Salary As Double B_BioData As BioData

End Type

Now you can see how all these elements fit together to form a complete Employee record.

The Employee data type consists of four elements:

E_Name – A String type, with a maximum length of 45 characters.
Designation – A String type, with a maximum length of 25 characters.
JoinDate – A Date type field to store the joining date.
PerformanceScore – An array of 12 elements (numeric values) to store the employee’s monthly performance evaluation scores, recorded by the management on a scale of 10.

Note: The string length specifications (like 45 or 25) are arbitrary. You may keep them as they are or simply declare the fields as String without specifying a length.

The Qualification and Experience data types were declared first, and then inserted as child elements of the BioData data type.

The BioData data type itself was declared above the Employee type, before being inserted into it as a child element.

Caution.

Ensure that you are not placing a Type declaration within itself, as this would create a circular reference and is not allowed.

Now that we are ready to work with this complex data structure, the first question that naturally comes to mind is:

How do we reference each element to assign values to it?

Sample Test Program for Employee Data Type.

We will now write a small program to test the Employee data type and assign values to each element of this nested complex data structure.

However, it is not as complicated as it may sound. If you find it difficult to follow, try creating simpler examples on your own based on your current level of understanding.

The program code is given below.

Observe carefully how each element is referenced when assigning values to it.

Public Function EmplTypeTest()
Dim Emp As Employee

Emp.E_Name = "John"
Emp.E_Desig = "Manager"
Emp.E_JoinDate = #01/01/2018#
Emp.E_PerfScore(Month(Date) - 1) = 4.5
Emp.E_Salary = 40000
        'BioData
        Emp.B_BioData.Address1 = "115/8"
        Emp.B_BioData.Address2 = "Olencrest,"
        Emp.B_BioData.City = "Columbus"
        Emp.B_BioData.State = "Ohio"
        Emp.B_BioData.PIN = "43536"
        Emp.B_BioData.BirthDate = #9/29/1979#
       
       'Qualifications
            Emp.B_BioData.Q_Desc.Q_Desc1 = "Degree in Computer Science"
            Emp.B_BioData.Q_Desc.Q_Desc2 = "PG Degree in Computer Science"
    
        'Experience
            Emp.B_BioData.X_Exp.X_Desc1 = "From Jan-2010 onwards Working as Project Manager, with XYZ Company."
            Emp.B_BioData.X_Exp.X_Desc2 = "From Mar-2005 to Dec-2009 worked as Team Leader with ABC Enterprise."
Call ListEmp(Emp)
    
End Function

As you can see in the code above, each element is referenced using its fully qualified name, clearly indicating its hierarchical position within the Employee data structure.

The same code is shown again below, but this time using full object references enclosed within With ... End With statements, where the XXXX part represents the hierarchical object names.

Public Function EmplTypeTest0()
Dim Emp As Employee

With Emp
  .E_Name = "John"
  .E_Desig = "Manager"
  .E_JoinDate = #01/01/2018#
  .E_PerfScore(Month(Date) - 1) = 4.5
  .E_Salary = 40000
End With

        'BioData
With Emp.B_BioData
        .Address1 = "115/8"
        .Address2 = "Olencrest,"
        .City = "Columbus"
        .State = "Ohio"
        .PIN = "43536"
        .BirthDate = #9/29/1979#
End With

       'Qualifications
With Emp.B_BioData.Q_Desc
            .Q_Desc1 = "Degree in Computer Science"
            .Q_Desc2 = "PG Degree in Computer Science"
End With

        'Experience
With Emp.B_BioData.X_Exp
            .X_Desc1 = "From Jan-2010 onwards Working as Project Manager, with XYZ Company."
            .X_Desc2 = "From Mar-2005 to Dec-2009 worked as Team Leader with ABC Enterprise."
End With

    Call ListEmp(Emp)

End Function

Referencing individual Element Variables of Employee Data Type.

Check how the With... End With statements are structured, and how the object references are given in the correct hierarchical order to reach each element variable.

In the revised version of the code shown below, the With... End With blocks are nested, and each block uses only the nearest parent object name to access its data elements.

Public Function EmplTypeTestA() Dim Emp As Employee With Emp .E_Name = "John" .E_Desig = "Manager" .E_JoinDate = #01/01/2018# .E_PerfScore(Month(Date) - 1) = 4.5 .E_Salary = 40000 'B_BioData With Emp.B_BioData .Address1 = "115/8" .Address2 = "Olencrest," .City = "Columbus" .State = "Ohio" .PIN = "43536" .BirthDate = #9/29/1979# 'Qualifications With .Q_Desc .Q_Desc1 = "Degree in Computer Science" .Q_Desc2 = "PG Degree in Computer Science" End With 'Experience With .X_Exp .X_Desc1 = "From Jan-2010 onwards Working as Project Manager, with XYZ Company." .X_Desc2 = "From Mar-2005 to Dec-2009 worked as Team Leader with ABC Enterprise." End With

End With ‘ Emp.B_BioData End With ‘ Emp Call ListEmp(Emp) End Function

You can use any of the three programs shown above—or run all of them one by one—before executing the following printing program, which will display the data in the Debug Window.

Public Function ListEmp(ByRef EmpList As Employee)
With EmpList
    Debug.Print "Name: ", , .E_Name
    Debug.Print "Designation: ", , .E_Desig
    Debug.Print "Join Date: ", , .E_JoinDate
    Debug.Print "Performance Score July: ", .E_PerfScore(8)
    Debug.Print "Salary: ", , .E_Salary
    
    Debug.Print "Address1: ", , .B_BioData.Address1
    Debug.Print "Address2: ", , .B_BioData.Address2
    Debug.Print "City: ", , .B_BioData.City
    Debug.Print "State: ", , .B_BioData.State
    Debug.Print "PIN: ", , .B_BioData.PIN
    
    Debug.Print "Qualification1: ", .B_BioData.Q_Desc.Q_Desc1
    Debug.Print "Qualification2: ", .B_BioData.Q_Desc.Q_Desc2
    
    Debug.Print "Experience1: ", , .B_BioData.X_Exp.X_Desc1
    Debug.Print "Experience2: ", , .B_BioData.X_Exp.X_Desc2
    
End With

End Function

Sample Output on the Debug Window.

Sample Output displayed on the Debug Window is given below:

Name:                       John                                         
Designation:                Manager                  
Join Date:                  01-01-2018 
Performance Score August:    4.5 
Salary:                      40000
Address1:                   115/8
Address2:                   Olencrest,
City:                       Columbus
State:                      Ohio
PIN:                        43536
Qualification1:             Degree in Computer Science
Qualification2:             PG Degree in Computer Science
Experience1:                From Jan-2010 onwards Working as Project Manager, with XYZ Company.
Experience2:                From Mar-2005 to Dec-2009 worked as Team Leader with ABC Enterprise.

Input Values through the Keyboard.

If you would like to try an array example, then copy and paste the following two programs into a Standard Module, then run the first code.

Public Function EmplTypeTestB()
Dim Emp(1 To 3) As Employee
Dim j As Integer, strlabel As String

For j = 1 To 3

With Emp(j)
strlabel = "( " & j & " )"
    .E_Name = InputBox(strlabel & "Name:")
    .E_Desig = InputBox(strlabel & "Designation:")
    .E_JoinDate = InputBox(strlabel & "Join Date:")
    .E_PerfScore(Month(Date) - 1) = InputBox(strlabel & "Performance Score:")
    .E_Salary = InputBox(strlabel & "Salary:")
    
   'B_BioData
    With Emp(j).B_BioData
        .Address1 = InputBox(strlabel & "Address1:")
        .Address2 = InputBox(strlabel & "Address2:")
        .City = InputBox(strlabel & "City:")
        .State = InputBox(strlabel & "State:")
        .PIN = InputBox(strlabel & "PIN:")
        .BirthDate = InputBox(strlabel & "Birth Date:")
       
       'Qualifications
        With .Q_Desc
            .Q_Desc1 = InputBox(strlabel & "Qualification-1:")
            .Q_Desc2 = InputBox(strlabel & "Qualification-2:")
        End With
    
        'Experience
        With .X_Exp
            .X_Desc1 = InputBox(strlabel & "Experience-1:")
            .X_Desc2 = InputBox(strlabel & "Experience-2:")
        End With
    End With
End With
Next

    
    Call ListEmp2(Emp)

End Function

The InputBox() function asks you to enter the details of three employees directly from the keyboard when the prompt appears. In the final statement, Call ListEmp2(Emp) runs the following code using the employee records array and prints the output in the Debug Window. Make sure the Debug Window is open (Ctrl+G).

Public Function typeTest()
Dim mySales As Sales

   mySales.Desc = "iPhone 8 Plus"
   mySales.Quantity = 1
   mySales.UnitPrice = 75000#
   mySales.TotalPrice = mySales.Quantity * mySales.UnitPrice

Debug.Print mySales.Desc, mySales.Quantity, mySales.UnitPrice, mySales.TotalPrice

End Function

Printing Program.

Public Function ListEmp2(ByRef EmpList() As Employee)
Dim j As Integer, strlabel As String
Dim lower As Integer
Dim upper As Integer

lower = LBound(EmpList)
upper = UBound(EmpList)

For j = lower To upper
With EmpList(j)
Debug.Print
    Debug.Print "=== Employee: " & .E_Name & "  Listing ==="
    Debug.Print "Name: ", , .E_Name
    Debug.Print "Designation: ", , .E_Desig
    Debug.Print "Join Date: ", , .E_JoinDate
    Debug.Print "Performance Score " & MonthName(Month(Date) - 1) & ": ", .E_PerfScore(8)
    Debug.Print "Salary: ", , .E_Salary
    
    Debug.Print "Address1: ", , .B_BioData.Address1
    Debug.Print "Address2: ", , .B_BioData.Address2
    Debug.Print "City: ", , .B_BioData.City
    Debug.Print "State: ", , .B_BioData.State
    Debug.Print "PIN: ", , .B_BioData.PIN
    
    Debug.Print "Qualification1: ", .B_BioData.Q_Desc.Q_Desc1
    Debug.Print "Qualification2: ", .B_BioData.Q_Desc.Q_Desc2
    
    Debug.Print "Experience1: ", , .B_BioData.X_Exp.X_Desc1
    Debug.Print "Experience2: ", , .B_BioData.X_Exp.X_Desc2
    
End With

Next
End Function

I hope you will experiment with User-Defined Types (UDTs) in your projects and explore their strengths and limitations.

The main drawback of UDTs is the lack of a built-in mechanism to validate the data assigned to their elements. For example, if a future date is entered into the Date of Birth element, there is no built-in validation to alert the user that the value is invalid. Similarly, if a negative value is entered into a Salary field, it will be accepted without any error. Wherever data validation is required, you must write separate validation code each time you use a UDT.

A better approach is to use Class Modules. In a Class Module, you can define each data element as a property and include validation logic within its Set property procedures to ensure data integrity before accepting the values. You can also create Functions or Subroutines within the Class Object to handle common operations on the data, and simply call them from your programs. All of this logic remains encapsulated within the Class Module itself, so you don’t need to rewrite it separately every time.

In the next section, we will learn how to use Class Modules to define structured data and use them effectively in programs.

Passing Two Dimensional Array to Function

a.p.r. pillaiAugust 02, 2018Array, Calculation, Data Type, Field Type, Fields, Functions 8 comments

Passing a Two-Dimensional Array Function.

First of all, I have some good news for you: our website, LEARN MS-ACCESS TIPS AND TRICKS, has been selected by the https://blog.feedspot.com panel as one of the Top 5 Microsoft Access Blogs on the Web and awarded the Badge given below.

You can find some of the top-ranked blogs on a variety of subjects, RSS feeds, YouTube channels, top news websites, and other sources. Subscribe to blogs, news feeds, or topics in any other area of interest, and receive regular updates from www.blog.feedspot.com in your Inbox as they become available by providing your email address.

Returning to our VBA lessons, last week we briefly discussed passing arrays as parameters to a Function using the ByRef method. This allowed us to work directly with the original array inside the called function and sort its values in descending order.

In our example, we used array values for only five elements, but in practice, an array can hold many rows and columns of data.

The Re-Dimension (ReDim) Statement.

An array can be re-dimensioned multiple times during program execution to increase or decrease the number of rows if its size cannot be determined in advance. In such cases, you should omit the element count in the initial Dim statement.

Example:

'Cannot Re-dimension pre-defined Arrays
.
Dim Products(1 to 5) as String
.
or
.
Dim Products(5) as String'The number of elements are predefined

'Re-dimension this Array later for required  
'Number of elements, not known in advance. 
.
Dim Products() as String
.
'Re-Dimension the Array for required number of elements
'Remember the array index numbers will be 0 to 4, total 5 elements
ReDim Products(5) As String
'
'OR
'In this case Array Index Number Range 1 to 5
ReDim Product(1 to 5) As String

'later on in the program
'all the values assigned to first 5 elements will be lost.
.
ReDim Products(7) As String 
.
or
.
ReDim Products(Ubound(Products)+2) As String
.
'To preserve the values already assigned to first 5 elements
.
ReDim Preserve Products(7) As String

Note: The important point is that the array re-dimensioning should be done in the calling procedure itself—before passing it to the called function if a size change is required. Although arrays are passed by reference and the called function can work directly with the passed variable, it cannot re-dimension the array to increase or decrease its number of elements from within the called function.

Two-Dimensional Array as Function Argument.

Now that we are aware of the limitations of passing arrays to a called function, let’s try passing a two-dimensional array containing Sales data to a function and printing its values in the Debug Window. Each record in the Sales data array will contain the following values:

Product Name - Text
Quantity - Integer
Unit Price – Double
Total Value - Double (will be calculated in the called function)

The sales records contain fields with different data types. Normally, to pass these values individually to a called function, we would need four separate array variables with different data types — for example:

String for Product Name
Integer for Quantity
Double for Unit Price
Double for Total Price

Each field’s values would be stored in its own array variable and passed separately to the function.

However, we will do this differently. Instead of using four separate arrays, we will use a single variable: a two-dimensional array of the Variant data type, with four rows (each row representing one sales record) and four columns (each column representing a field). This way, we can pass all four fields of each record together as a single variable to the called function.

The Variant Data Type.

We are not storing the column names anywhere within the array. It is simply assumed that:

The first column holds the Product Name
The second column holds the Quantity
The third column holds the Unit Price
The fourth column holds the Total Price

Since we are using a Variant data type for the array, each array element can hold different data types. A Variant variable automatically adapts its data type to match the type of value assigned to it. This flexibility allows us to store text, integers, and floating-point numbers together in the same array.

Before writing the complete function, let’s first examine how the Dim statement defines the two-dimensional array and how the sales values are assigned to each array element.

' variant Variable can hold different data types in each element 
Dim Product(4,4) as Variant 
'
Product(0, 0) = "Hard Disk": Product(0, 1) = 5: Product(0, 2) = 125.5: Product(0, 3) = 0

Product(1, 0) = "Keyboard": Product(1, 1) = 2: Product(1, 2) = 25.25: Product(1, 3) = 0

Product(2, 0) = "Mouse": Product(2, 1) = 3: Product(2, 2) = 13.75: Product(2, 3) = 0

Product(3, 0) = "DVD Writer": Product(3, 1) = 10: Product(3, 2) = 30: Product(3, 3) = 0

In the above example, we have only four records (rows) in our table, and each record contains four fields (columns). Every cell in this two-dimensional array is identified by a pair of numbers—the row index and the column index—separated by a comma. The index number on the left represents the row index, and the number on the right represents the column index. Since our array has 4 rows and 4 columns, both index ranges go from 0 to 3.

Here is how the columns are arranged:

Column 0: Product Name
Column 1: Quantity
Column 2: Unit Price
Column 3: Total Value (to be calculated and assigned later)

We can pass the entire array to a function as a ByRef parameter, allowing the function to work directly with the original data.

If you are new to two-dimensional arrays, it can feel a bit confusing at first to understand how the values are arranged and how to refer to each cell. This becomes even trickier when performing calculations across multiple cells within the same row.

Fortunately, there is a better way to handle this complexity—by using User-Defined Variables. Yes, you can actually define your own variable type, in addition to the built-in types provided by VBA.

We will explore this concept in detail next week. Once you get familiar with it, you’ll find it much easier and more intuitive than juggling rows and columns. The best part is that this method scales effortlessly—you can work with 5 rows, 500 rows, or even 5000 rows using the same statements in your function.

Create the Product List Data.

Public Function ProdList()
Dim Products(4, 4) As Variant
Dim j As Integer, k As Integer, stridx As String
' 0 = Description
' 1 = Quantity
' 2 = Unit Price
' 3 = Total Price to be calculated
'Array elements index numbers are 0 to 3
For j = 0 To 3
 For k = 0 To 3
    stridx = "(" & j & "," & k & ")"
    Select Case k
        Case 0
          Products(j, k) = InputBox("Product Name" & stridx)
        Case 1
          Products(j, k) = InputBox("Quantity" & stridx)
        Case 2
          Products(j, k) = InputBox("Unit Price" & stridx)
        Case 3
          Products(j, k) = 0 'total value will be calculated
    End Select
    Next k
Next j

Call ProdPrint(Products)

End Function

VBA Code Line by Line

We have defined the Products variable as a Variant data type with 4 rows and 4 columns, so it can hold values of different data types in each cell.

In the next line, we declare three more variables:

j and k as control variables for the For…Next loops
strIdx as a String variable for building text to display the index numbers of each cell in the InputBox() prompt.

We then set up two nested 'For … Next' loops to control the index row and column numbers. The outer loop (j) controls the row index, while the inner loop (k) controls the column index.

Inside the inner loop, we use a Select Case…End Select structure to determine which field is being processed based on the current value of k:

When k = 0, the InputBox() function prompts for the Product Name and assigns it to Products(j, 0).
When k = 1, it prompts for the Quantity and assigns the value to Products(j, 1).
When k = 2, it prompts for the Unit Price and assigns it to Products(j, 2).
When k = 3, it initializes Products(j, 3) = 0 (this will later hold the calculated Total Price).

The outer loop repeats this process four times (for j = 0 to 3). For each row, the inner loop repeats four times (for k = 0 to 3), collecting input for each element.

The Output ProductPrint () Function.

When the control exits the loop, the ProductPrint() function is called, passing the Products variable as a parameter.

Public Function ProdPrint(List As Variant)
Dim j As Integer, k As Integer

'Ubound() function will get the
'total rows in the array - first value in the Dim statement
For j = 0 To UBound(List, 1) - 1
      List(j, 3) = List(j, 1) * List(j, 2)
    For k = 0 To UBound(List, 2) - 1 'get second value in Dim statement
        Debug.Print List(j, k),
    Next k: Debug.Print
Next j

End Function

The ProductPrint() function receives the Products Array reference (address) through the ByRef method. If the ByVal or ByRef keyword is not explicitly specified before the parameter variable, then ByRef is the default.

As in the earlier program, two integer variables, j and k, are declared as control variables for the outer and inner For…Next loops. These loops are required to navigate the array using its row and column index numbers. The loop starts at 0, and to determine the end value dynamically, we use the Ubound() (Upper Bound) function on the array dimension. In the previous example, we used values 0 to 3, but here we use Ubound() to make the program flexible. This ensures that if the array size changes later through ReDim statements, the loop still calculates the correct number of rows and columns.

Usage: UBound() Function to get Two-Dimensional Index Numbers.

UBound(Array,1).

The Ubound(List, 1) function returns the number of rows in the array, which is 4. However, array row indexes start from 0 in memory, so the valid index numbers are 0 to 3. The second argument (1) in the Ubound() function specifies that we want the upper bound of the first dimension (rows) of the array. Because the row index starts at 0, we subtract 1 from the total number of rows (4 − 1) when using these indexes in a For…Next loop.

UBound(Array,2).

The UBound(List, 2) function returns the number of columns in the array. The second parameter is optional—if omitted, UBound will return only the upper bound of the first dimension (rows). For single-dimensional arrays, this second parameter is never used.

The statement immediately following the first For…Next loop — List(j, 3) = List(j, 1) * List(j, 2) — calculates the total price of each item and stores it in the rightmost cell. These updated values are then printed in the Debug window during the next 'For… Next' loop, which outputs the complete sales record for each item.

Controlling the Print-Head

Placing a comma at the end of a 'Debug.Print' statement aligns the next item in the 14th column on the same line, following the previously printed item.

An empty 'Debug.Print' statement, placed immediately after the inner Next statement (without a trailing comma), moves the print cursor back to the first column of the next line. This ensures the output for the next sales record begins at the correct position.

If a semicolon (;) is placed at the end of a 'Debug.Print' statement, the print cursor advances to the very next character position, without leaving any space between the printed items.

Next week, we will explore user-defined variables that can hold mixed data types. With them, we can assign meaningful names to each collection element instead of array position, as we did in the examples above. This will make it much easier—and more intuitive—than trying to memorize each array element based solely on its index.

User Defined Data Type

a.p.r. pillaiAugust 20, 2010Data Type, Functions, How Tos No comments

User-Defined Data Type.

VBA (Visual Basic for Applications) provides several predefined data types, such as Integer, String, Date, and others, which are used to store specific types of data. For example, an Integer variable can hold numeric values ranging from -32,768 to +32,767, while a String variable stores alphanumeric values, and so on.

However, programmers can also define their own custom data types combining multiple predefined data types into a single structure, and use them in their programs. Let’s explore this concept with a simple example.

Creating a User-Defined Type.

Open one of your existing databases or create a new one.
Open the VBA Editing Window (Alt+F11 or Tools ->Macro -> VBA Editing)
Access2007:
- Select Modules from the Object drop-down list.
- Double-click on an existing Module or select Create Menu.
- Select the Macro -> Modules toolbar button to create a new Standard Module.

Copy and paste the following Code into the Module.

Public Type WagesRec
    strName As String
    dblGrossPay As Double
    dblTaxRate As Double
    dblNetPay As Double
    booTaxPaid As Boolean
End Type

Public Function WagesCalc()
Dim netWages As WagesRec, strMsg As String
Dim fmt As String

With netWages
.strName = InputBox("Employee Name: ", , "")
.dblGrossPay = InputBox("Enter Gross Pay:", , 0)
.dblTaxRate = InputBox("Enter Taxrate", , 0)

.dblNetPay = .dblGrossPay - (.dblGrossPay * .dblTaxRate)

If .dblTaxRate <> 0 Then
   .booTaxPaid = True
End If

'Display Record
fmt = "#,##0.00"
strMsg = "Name:     " & .strName & vbCr & "Grosspay:     " & Format(.dblGrossPay, fmt) & vbCr
strMsg = strMsg & "Tax Rate:     " & Format(.dblTaxRate * 100, fmt) & "%" & vbCr & "Tax Amt.:     " & Format(.dblGrossPay * .dblTaxRate, fmt) & vbCr
strMsg = strMsg & "Net Pay:      " & Format(.dblNetPay, fmt) & vbCr & "Tax Paid:     " & .booTaxPaid

MsgBox strMsg, , "WagesCalc()"

End With

End Function

Place the insertion point somewhere in the middle of the WagesCalc() Function and press the F5 Key to run the Code.
Key in the Employee name, Gross Pay, and Tax Rate when prompted.

The output display of the program is shown below:

Right-click to open Large Image in New Tab/Window.

The Type Declaration and Properties.

Let us examine the above Code. The User-defined data type declaration is made in the global area of a Standard Module within the Type WagesRec... End Type structure. WagesRec is an arbitrary name; it can be anything that you like, but it should follow the Variable naming conventions. By default, the scope of the data type is Public. When it is declared as Private, like Private Type WagesRec... End Type: The scope of the data Type is within that Module only.

The individual data-member names of the new Data Type should also follow the variable naming conventions.

We have declared a Variable NetWages (you may visualize NetWages as an Object with several properties that can be set with different values) using the new data type WagesRec in our WagesCalc() Function. Individual elements of the NetWages Variable can be addressed as a subset of that object; separating both with a dot (.) like 'Netwages.dblGrossPay' to set its value or retrieve its contents.

We have used three InputBox statements to ask the user to input values for the Name, Grosspay, Tax Rate, calculate the Tax Value, Net Payable amount, and set the Tax Paid flag if the Tax Rate is a non-zero value.

In the next part of the program, we have loaded a String Variable strMsg with the output labels and values to display them through a MsgBox.

Array Data Type Elements.

In the Type declaration example, we have used the predefined System data types as elements. Besides that, we can declare Subscripted Elements and other User-Defined Data Types also as elements, like the following example:

Public Type MyRecord
dblIncentives(1 to 100) as double
EmployeeRec as WagesRec
End Type

In our program, let us assume that we have declared a variable with the above data type, like the following:

Dim EmployeeWages as MyRecord

Addressing the individual elements and their sub-elements will be as follows to assign values to them:

EmployeeWages.dblIncentives(1) = 5000

EmployeeWages.EmployeeRec.strName = "John Smith"

Subscripted Variable.

But the whole Data Type can be declared as a Subscripted Variable like:

Dim EmployeeWages(1 to 100) as MyRecord

Then how do we address the individual elements of the Variable?

EmployeeWages(1).dblIncentives(1) = 500
EmployeeWages(1).dblIncentives(2) = 750
EmployeeWages(1).EmployeeRec.strName = "John Smith"
EmployeeWages(1).EmployeeRec.dblGrossPay = 15000
EmployeeWages(2).dblIncentives(1) = 400
EmployeeWages(2).dblIncentives(2) = 450
EmployeeWages(2).EmployeeRec.strName = "George"
EmployeeWages(2).EmployeeRec.dblGrossPay = 17000

Sorting the Array of User-Defined Types.

We will see another example that uses subscripted user-defined data types. In this example, we will declare a new Data Type for the Employees Table from the Northwind.mdb sample database. We will load a few field values of the Employees Table into our User-Defined Subscripted Variable, sort the Names in memory, and print the output in the Debug Window.

1. Import the Employees Table from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb the sample database,

2. Copy and paste the following VBA Code into a new Standard Module and save the Module:

Type PersonalRecord
    strFirstName As String
    strLastName As String
    dtDB As Date
    strAddress As String
    strCity As String
    strPostalCode As String
End Type


Public Function ReadSort()
Dim PRec() As PersonalRecord, PRecX As PersonalRecord
Dim db As Database, rst As Recordset, recCount As Long
Dim J As Long, k As Long, h As Long

Set db = CurrentDb
Set rst = db.OpenRecordset("Employees", dbOpenDynaset)
rst.MoveLast
recCount = rst.RecordCount

ReDim PRec(1 To recCount) As PersonalRecord
rst.MoveFirst
J = 0
'Load Employee Records into Userdefined Variable Array
Do While Not rst.EOF
J = J + 1
With rst
    PRec(J).strFirstName = ![FirstName]
    PRec(J).strLastName = ![LastName]
    PRec(J).dtDB = ![BirthDate]
    PRec(J).strAddress = ![Address]
    PRec(J).strCity = ![City]
    PRec(J).strPostalCode = ![PostalCode]
End With
rst.MoveNext
Loop

rst.Close
Debug.Print "Before Sorting"
Debug.Print "--------------"
DisplayRoutine PRec()

'Bubble Sort on FirstName
For k = 1 To J - 1
   For h = k + 1 To J
       If PRec(h).strFirstName < PRec(k).strFirstName Then
           'Swap the Records
           'move the first record to temporary storage area
           PRecX.strFirstName = PRec(k).strFirstName
           PRecX.strLastName = PRec(k).strLastName
           PRecX.dtDB = PRec(k).dtDB
           PRecX.strAddress = PRec(k).strAddress
           PRecX.strCity = PRec(k).strCity
           PRecX.strPostalCode = PRec(k).strPostalCode
        
           'move the second record to replace the first
           PRec(k).strFirstName = PRec(h).strFirstName
           PRec(k).strLastName = PRec(h).strLastName
           PRec(k).dtDB = PRec(h).dtDB
           PRec(k).strAddress = PRec(h).strAddress
           PRec(k).strCity = PRec(h).strCity
           PRec(k).strPostalCode = PRec(h).strPostalCode
           
           'move the from temporary storage to replace the second record
           PRec(h).strFirstName = PRecX.strFirstName
           PRec(h).strLastName = PRecX.strLastName
           PRec(h).dtDB = PRecX.dtDB
           PRec(h).strAddress = PRecX.strAddress
           PRec(h).strCity = PRecX.strCity
           PRec(h).strPostalCode = PRecX.strPostalCode
        End If
    Next h
Next k

Debug.Print "After Sorting"
Debug.Print "--------------"
DisplayRoutine PRec()

End Function


Public Function DisplayRoutine(ByRef getRecord() As PersonalRecord)
Dim RecordCount As Long, J As Long

RecordCount = UBound(getRecord)
For J = 1 To RecordCount
   Debug.Print getRecord(J).strFirstName, getRecord(J).strLastName, getRecord(J).dtDB
Next
Debug.Print
Debug.Print

End Function

3. Place the insertion point in the middle of the Module and press F5 to run the Code.

4. Press Ctrl+G to display the Debug Window, and you will find the following output printed there:

Before Sorting
--------------
Nancy         Davolio       08/09/1968 
Andrew        Fuller        19/02/1952 
Janet         Leverling     30/08/1963 
Margaret      Peacock       19/09/1958 
Steven        Buchanan      04/03/1955 
Michael       Suyama        02/07/1963 
Robert        King          29/05/1960 
Laura         Callahan      09/01/1958 
Anne          Dodsworth     02/07/1969 


After Sorting
--------------
Andrew        Fuller        19/02/1952 
Anne          Dodsworth     02/07/1969 
Janet         Leverling     30/08/1963 
Laura         Callahan      09/01/1958 
Margaret      Peacock       19/09/1958 
Michael       Suyama        02/07/1963 
Nancy         Davolio       08/09/1968 
Robert        King          29/05/1960 
Steven        Buchanan      04/03/1955

How it Works.

At the beginning of the program, we opened the Employees Table, read the count of records in the Table, and re-dimensioned the user-defined variable PersonalRecord to reserve enough space to hold all the Employees records.
Next, we opened the Employees Table and loaded all the employees' data into the array.
We have sent a list of the unsorted data in the Debug Window.
The data is sorted on FirstName in Ascending Order in memory using the bubble sort method.
The sorted employee records are listed in the Debug Window.

Tip: You can change the sorting order to descending order by changing the logical operator < to > in the following statement:

If PRec(h).strFirstName < PRec(k).strFirstName Then
If PRec(h).strFirstName > PRec(k).strFirstName Then
As you can see, the data printing Routine is a separate Function Display Routine() and we have passed the whole Array of records to this program twice to print its contents into the Debug Window.