LEARN MS-ACCESS TIPS AND TRICKS

MS-Access Recordset and Class Module

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

Introduction.

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 object. 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 on it. 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 of our Class Module is updating the TotalPrice field with the product of Qty * UnitPrice.
The Class Module includes a subroutine that sorts the data based on a user-specified field and outputs the sorted listing to the Debug Window.
Another subroutine creates a copy of the Table with a new name, after sorting the data based on the column number provided as a parameter.

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 numbers) as parameters to calculate and update the third parameter column with the product of the first column and * 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, which means the first column number is 0, the second column is 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 provided as a parameter and creates a new 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 to it. 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 values from the first column parameter are multiplied by the values from the second column parameter, and the resulting value is written to the target column. 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.

Base Class and Derived Object Variants

a.p.r. pillaiNovember 14, 2018Class Module, Methods, Objects, Property, VBA No comments

Introduction.

Last week, we explored an example where a Base Class Object was passed through a Set Property Procedure, allowing it to become part of another object in memory. The passed object essentially became an extension or child object of the main object. In that earlier program, we passed the child object to the target object during the instantiation phase of our test program and then assigned values to the child object’s properties later in the code.

In the next example, we will take a slightly different approach.

For those who would like to go through the earlier Articles on the MS-Access Class Module, the links are given below:

This time, we will open both objects—ClsArea (the base class) and ClsVolume2 (the target class)—separately in our test program. We will assign values to the ClsArea base class properties before passing it to the ClsVolume2 target class object. Remember, the ClsVolume2 class has only one property, p_Height, and its Volume() method requires the Length and Width values from the base class ClsArea to calculate the volume.

Copy and paste the following sample Test Code into a Standard Module.

The SetNewVol2_2 Procedure.
```
Public Sub SetNewVol2_2()
'Method 2/2
Dim CA As ClsArea
Dim Vol As ClsVolume2

Set CA = New ClsArea
Set Vol = New ClsVolume2

CA.strDesc = "Bed Room"
CA.dblLength = 90
CA.dblWidth = 10
Stop


'Here ClsArea class Object CA is passed to the 
‘Property procedure Set CArea of ClsVolume2 object Vol
Set Vol.CArea = CA 'Pass ClsArea obj to ClsVolume2

Vol.dblHeight = 10 'assign height to ClsVolume2


Debug.Print "Description", "Length", "Width", "Area", "Height", "Volume"
With Vol.CArea
  Debug.Print .strDesc, .dblLength, .dblWidth, .Area(), Vol.dblHeight, Vol.Volume()
End With
Stop

Set CA = Nothing
Set Vol = Nothing

End Sub
```
VBA Code Review.
In the first Dim statement, CA is declared as a ClsArea object and Vol as a ClsVolume2 object. The next two statements instantiate these objects in memory.
The following three statements assign values to the properties of the ClsArea object.
A Stop statement is then used to pause code execution, allowing us to inspect the property values of the object in the Locals window.
Next, the statement Set Vol.CArea = CA assigns the ClsArea object (CA) as a child object of the ClsVolume2 object (Vol).
After that, the dblHeight property of the ClsVolume2 object is assigned the value 10.
The subsequent statements, placed before the next Stop statement, print the property values from memory to the Debug window.

Finally, the last two Set statements release both objects from memory before the program ends.
Display the Locals Window.
Inspecting the Locals Window
1. Open the Locals Window
  From the View menu in the VBA Editor, select Locals Window.
2. Run the Code
  - Click anywhere in the middle of the code window.
  - Press F5 to run the program until it pauses at the Stop statement.
  - Alternatively, press F8 to run the code step by step, which lets you observe the changes in the Locals Window at each step.
3. Expand the Objects
  Click the [+] symbol next to the object names in the Locals Window to expand and display their properties and current values.
4. Observe Object References
  - Check the CArea and p_Area object references under the Vol object.
  - At this point, their values will show as Nothing because we have not yet passed the CA object to the Vol object.
5. Continue Running the Code
  - After reviewing the Locals Window, run the code until it pauses at the next Stop statement.
  - Now, the CArea Set Property procedure assigns the p_Area object reference to the ClsArea object, linking it into the ClsVolume2 object.
Next, we will try another variation of this example using the same two classes — ClsArea and ClsVolume2 — to demonstrate a slightly different approach.

New Class Module ClsVolume3.

1. Insert a new Class Module and change its name Property Value to ClsVolume3.

2. Copy and Paste the following VBA Code into the ClsVolume3 Class Module:

Option Compare Database
Option Explicit
'Method three 
Private p_Height As Double
Public p_Area As ClsArea

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

Public Property Let dblHeight(ByVal dblNewValue As Double)
    p_Height = dblNewValue
End Property

Public Function Volume() As Double
    Volume = p_Area.dblLength * p_Area.dblWidth * Me.dblHeight
End Function

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

Private Sub Class_Terminate()
    Set p_Area = Nothing
End Sub

In the code, p_Height is declared as private property, while p_Area is declared as a public ClsArea object within the ClsVolume3 class. This means p_Area appears as a property of the ClsVolume3 class, with its own accessible properties and methods for direct Get/Let operations in the user program (from a standard module). Although the ClsArea object is exposed as a public property of ClsVolume3, its internal properties and methods remain encapsulated within the ClsArea class itself.

It is important to ensure that the ClsArea class is fully developed and free of errors before using it inside other classes.

The Class_Initialize() and Class_Terminate() routines handle the lifecycle of the embedded object: The ClsArea object is instantiated in Class_Initialize() when a ClsVolume3 object is created, and released from memory in Class_Terminate() When the user program ends.

The Testing Program.

The sample Test VBA Code is given below.

Copy and paste the code into the Standard Module.

Public Sub SNewVol3()
'Here ClsArea class is declared as a Public Property of ClsVolume3
Dim volm As ClsVolume3

Set volm = New ClsVolume3

volm.p_Area.strDesc = "Bed Room"
volm.p_Area.dblLength = 15 'assign length
volm.p_Area.dblWidth = 10 'assign width in clsArea
volm.dblHeight = 10 'assign height to ClsVolume2

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

End Sub

Display the Locals Window (View -> Locals Window), if it is not already open.

Click somewhere in the middle of the code and press F8 to execute the VBA Code one line at a time and watch the Local Window to track what happens at each step.

All the above variants of the ClsVolume Class have been written with less Code, except the first example of the ClsVolume Class.

Working with the Recordset Object.

Next week, we will work with a built-in Object 'DAO.Recordset' and build a Class Module to:

Calculate and update a Field,
Sort the Data,
Print the sorted data in the Debug Window,
And create a Clone of the Table with sorted data.

That is a lot of action next week.

List of All the Links on this Topic.

Earlier Post Link References:

VBA Base Class and Derived Object-2

a.p.r. pillaiNovember 08, 2018Class Module, Object Reference, Objects, Property, VBA 3 comments

Introduction.

Last week, we created a derived Class, 'lsVolume', using ClsArea as the base class. In that approach, we added property procedures in the derived class to expose the base class’s properties and functions to the user programs. This method, however, requires repeating all the base class property procedures in the derived class.

In this section, we will explore how to create the same derived ClsVolume class without duplicating the property procedures of the ClsArea base class.

So far, we have learned about Get and Let property procedures in classes. There is also a third type: the Set Property Procedure, which is used to directly assign an object to a class object of the same type.

Before proceeding, you may want to revisit the earlier pages on this topic if you haven’t already. Links are provided below:

ClsVolume Class, the Makeover.

We shall create a different variant of the same ClsVolume Class Module we created last week, using ClsArea as Base Class, with a different approach, and with less Code.

Create a new Class Module and change its Name Property Value to ClsVolume2.

Copy and Paste the following Code into the Class Module ClsVolume2 and Save the Module:

Option Compare Database
Option Explicit
'Method two-1

Private p_Height As Double
Private p_Area As ClsArea

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

Public Property Let dblHeight(ByVal dblNewValue As Double)
    p_Height = dblNewValue
End Property

Public Function Volume() As Double
    Volume = p_Area.dblLength * p_Area.dblWidth * p_Height
End Function

The new Get and Set Property Procedure for the ClsArea Object.

Public Property Get CArea() As ClsArea
   Set CArea = p_Area
End Property

Public Property Set CArea(ByRef AreaValue As ClsArea)
  Set p_Area = AreaValue
End Property

From the Debug menu, select Compile [Project Name] to compile all the VBA code in your database and ensure it is error-free. If any errors are found in other VBA programs, locate and correct them, then recompile the project.

While unresolved errors will not prevent you from assigning or retrieving values from object properties, the VBA IntelliSense—which displays a list of an object’s properties and functions—will not work properly until the project compiles successfully.

Seeing the property list appear in IntelliSense is an invaluable aid during coding, especially while learning and experimenting with class objects.

**Get / Set instead of the Get / Let Property Procedure.**

In this version of the ClsVolume class, we have omitted all the property procedures ClsArea that were present in the previous version. Instead, we use Get and Set property procedures rather than the traditional Get/Let pair.

Take a look at the declaration: the private member p_Area is declared as a ClsArea class object. Normally, when an object is declared this way, we would create an instance of it in the Class_Initialize() procedure.

However, in this approach, we have not instantiated it within the class. The plan is to create and populate the ClsArea object in the user program, set its properties with appropriate values, and then pass it to the ClsVolume2 class. The class will then use these values during the final calculation phase.

Take note of the Set CArea() procedure. Its ByRef parameter, AreaValue, is declared as a ClsArea object. When an ClsArea instance is passed to this property procedure, the object variable AreaValue receives it and assigns it to the private p_Area property of the ClsVolume2 object.

This mechanism allows the ClsVolume2 class to use an externally created and populated ClsArea object without having to instantiate it internally, maintaining flexibility and reusability.

The Get CArea() Property procedure returns the Object to the calling program.

In our earlier programs, we wrote property procedures for individual elements of an object, such as Length, Width, and Height—to assign or retrieve values. In this version, the difference is that we are passing an entire object as a parameter to a Set property procedure.

To access a property of this passed object—for example, dblLength—we use the syntax CArea.dblLength. Here, the Get/Set property procedure name CArea essentially becomes a child object of the main object when declared in the main program. Its individual properties can then be accessed directly using the object address, such as:


Vol.CArea.dblLength

This approach allows the main object to interact with the entire child object and its properties as a single unit, simplifying property management and enhancing reusability.

A Test Program in Standard Module.

We will now create a small test program in a Standard Module to verify the functionality of our newly derived class object. ClsVolume2.

Insert a new Standard Module into your project.
Copy and paste the following code into the module.
Save the module before running the program.

Public Sub SetNewVol2_1()
'Method 1/2
Dim Vol As New ClsVolume2

'ClsArea Object instantiated and passed to the
'Property Procedure Set CArea in ClsVolume2.

Set Vol.CArea = New ClsArea 'declare and instantiate the object in one statement

Stop

Vol.CArea.strDesc = "Bed Room"
Vol.CArea.dblLength = 90
Vol.CArea.dblWidth = 10

Vol.dblHeight = 10 'assign height to ClsVolume2

Stop

Debug.Print "Description", "Length", "Width", "Area", "Height", "Volume"
Debug.Print Vol.CArea.strDesc, Vol.CArea.dblLength, Vol.CArea.dblWidth, Vol.CArea.Area, Vol.dblHeight, Vol.Volume

Set Vol.CArea = Nothing
Set Vol = Nothing

End Sub

Code Review Line by Line.

Let’s quickly review the VBA code above.

The first line instantiates the ClsVolume2 class with the object name Vol.
After the comment lines, the Set Vol.CArea statement calls the property procedure and passes the newly instantiated ClsArea object as its parameter.

I included a Stop statement on the next line to pause the program so you can observe how the object is assigned to the CArea property. We’ll explore how to inspect this in memory shortly.

The following four lines assign values to the ClsArea object properties (strDesc, dblLength, dblWidth) and to the dblHeight property of the ClsVolume2 object.
The next Stop statement pauses the program again, allowing you to inspect how these values are stored in memory.
The subsequent line prints the headings in the Debug Window for clarity.
Finally, the last line prints the values of the object properties retrieved from memory, displaying them in the Debug Window.

Run the Code to the Next Stop Statement

Let’s run the code and inspect the memory to see what happens at each stage where the Stop statements are placed.
1. Click anywhere inside the code and press F5 to run the program. The execution will pause at the first Stop statement.
2. From the View menu, select Locals Window. This opens a window below the code editor that displays the ClsArea and ClsVolume2 objects, along with their properties and member procedures, as they are stored in memory.
3. Observe the structure of the objects and how the property values are held. A sample image of the Locals Window is shown below for reference.
The Locals Window View.

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

To give more space for the Locals Window, drag the sizing handles of other windows upward to reduce their height. Alternatively, you can close the Debug Window temporarily and press Ctrl+G to bring it back when needed.

The Locals Window provides a graphical view of all objects and their properties in memory:
1. The first item with a plus [+] symbol shows the name of the Standard Module from which the program is running.
2. The next plus [+] symbol represents the Vol object, which is the instantiated ClsVolume2 object in memory.
Click the plus [+] symbols to expand each item and display detailed information about the object’s properties and member procedures.
Right-click to open Large Image in New Tab/Window.
You will find the next level of Objects and Properties.
The [+]CArea indicates that this Object has the next level of Properties and their Values.
The dblHeight Get property Procedure comes directly under the Vol Object.
The [+]p_Area is the Private Property declared ClsArea Class in the ClsVolume2 Class.
The p_Height is also the Private Property declared in the ClsVolume2.

Click on the plus [+] symbols to expand the objects to show their Properties and Values.

Right-click to open Large Image in New Tab/Window.
Expanding the [+] CArea node displays the ClsArea object that was passed to the Set CArea() property procedure.
The expansion of [+]p_Area gives the view of the ClsArea Property declared as Private.
Note that the p_Area Private Property, of the ClsVolume2 Class Object, and all its elements are accessible only through the CArea Object Property Get/Set Procedures to the outside world.
The second column in the Locals Window displays the values assigned to the object’s properties. At this stage, no values have been assigned, so the fields are currently empty.
The Third Column shows the Data Type or Object Class Module Names.
Press F5 to run the program further, till it is paused at the next Stop statement, to assign some values to the Object Properties. The program will pause at the next Stop statement. Check the Locals Window for changes in Values.

Right-click to open Large Image in New Tab/Window.
Inside the CArea Object, the first two lines with values 90, 10, and the last strDesc Variable with value "Bedroom" are assigned through the Get Property Procedures, respectively. The p_Desc, p_Length, and p_width are values assigned through Set Property Procedures to the p_Area Property of the ClsVolume2 Class Object as well.
The p_Area Object of ClsArea Class declared as Private Property of ClsVolume2 is seen with its Get/Set Property Procedures and assigned values.
Check the Type Column of [-]CArea and [-]p_Area; both Objects are derived from the ClsArea Base Class.

Usage of ClsArea and ClsVolume2 Class Objects Differently.
Next week, we will explore another approach using the same two objects.

If you’d like to experiment on your own beforehand, here’s a clue to get you started:
1. Instantiate ClsVolume2 and ClsArea classes as two different Objects in the Standard Module Program.
2. Assign values to both Object Properties.
3. Assign the ClsArea instantiated Object to the CArea Object in the ClsVolume2 Class Object, before printing the Values to the Debug Window.
In this example, we can achieve the same result as in the previous example without having to repeat the Get/Let property procedures in the ClsVolume class module.

The Links of All the Pages in 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

Introduction.

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 as part of more advanced operations. For instance, it could be integrated into a class designed to calculate the volume of an object, 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 UnitPriceMultiplying one by the other would yield the Total Price of an item.

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 has the potential to be used as 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 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 can be incorporated into 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 more 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 works 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 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 on this Topic.

Earlier Post Link References:

Ms-Access VBA Class Object Arrays

a.p.r. pillaiOctober 20, 2018Array, Calculation, Class Module, Objects 4 comments

Introduction.

Last week, we introduced a simple custom class object with two properties—Length and Width—and a method to calculate the Area of any item using these values. I hope you now have a good understanding of the basics of an MS Access custom class object. If not, please review this page before continuing: MS Access Class Module and VBA.

Class module objects can significantly reduce the amount of repetitive code in your programs. Whenever you find yourself writing the same logic repeatedly, consider whether it could be encapsulated in a class module. (Of course, some repetitive code may also be handled with utility functions, but class objects require a different approach.)

Creating a class object may seem like extra work, as refining its functionality often requires more code initially. However, your main modules remain simpler, while the detailed and more complex code stays hidden inside the class—organized, reusable, and easier to maintain.

For now, we’ll build on our simple class module and extend it to create an array of objects and calculate the area of multiple items.

The ClassArray() Sub-Routine.

The following sample VBA code, placed in a Standard Module, creates an array of five objects from the ClsArea class. It then prints their property values and method results in the Debug Window.

You can copy and paste the code into a standard module, but it’s even better to type it out yourself—this way, you’ll understand each line and what it does more thoroughly.

Public Sub ClassArray()
Dim tmpA As ClsArea
Dim CA() As ClsArea
Dim j As Long, title As String
Dim L As Long, U As Long

title = "ClassArray"
For j = 1 To 5 ‘the Loop is set for 5 items
  'instantiate temporary ClsArea Object
  'to enter input
  Set tmpA = New ClsArea

tmpA.strDesc = InputBox(Str(j) & ") Description:", title, "")
     tmpA.dblLength = InputBox(Str(j) & ") Enter Length:", title, 0)
     tmpA.dblWidth = InputBox(Str(j) & ") Enter Width:", title, 0)
    
  'Redimension Array
  ReDim Preserve CA(1 To j) As ClsArea
  Set CA(j) = tmpA   'Copy Object to Array
  
Set tmpA = Nothing 'Remove temporary object from memory.
Next


‘PRINTING SECTION
'Print the Array contents into the Debug Window
L = LBound(CA)
U = UBound(CA)

Debug.Print "Description", "Length", "Width", "Area"
For j = L To U
  With CA(j)
    Debug.Print .strDesc, .dblLength, .dblWidth, .Area
  End With
Next

’stop

'Clear array objects from memory
  Erase CA


End Sub

Click somewhere in the middle of the Code and press F5 to execute the Code. Input the Values for Description, Length, and Width from the Keyboard for 5 different Items, one after the other.

The Sample Run of the Program is given below:

Description   Length        Width         Area
Door Mat       5             8             40 
Carpet         12            23            276 
Bed Room       21            23            483 
Store Room     15            25            375 
Terrace        40            50            2000

Review of Code Line by Line.

Let’s examine the code line by line. In the declarations area, we first declare an object variable:


Dim tmpA As ClsArea

The next line declares an empty array of the same type:


Dim CA() As ClsArea

The array is left un-sized here — we’ll set its length later in the procedure (typically with ReDim) to hold the required number of ClsArea instances.

In the next two lines, the variable title is declared as a String, and the variables j, L, and U are declared as Long integers.

The title variable is then initialized with the string "ClassArray", which will be used as the prompt text in the InputBox() function.

The For… Next loop is set up with j as the control variable, running five times. After the next two comment lines, the Set statement is used to instantiate (allocate memory for) the ClsArea class object, assigning it to the temporary object variable tmpA.

The following three lines of code assign input values to the strDesc, dblLength, and dblWidth properties of the tmpA object.

After another comment line, the ClsArea object array (CA) is resized with the ReDim statement to hold elements from 1 to j. On the first pass, this creates space for element 1; on subsequent passes, the array is resized to hold elements 1 to 2, 1 to 3, and so on, up to 1 to 5. The Preserve keyword ensures that previously created objects in the array are retained while expanding its size.

Note: The ReDim statement changes the size of the object array, but without the Preserve keyword, it erases any objects already stored in the array. Using Preserve ensures that existing objects remain intact when the array is resized.

The Set statement in the next line copies the tmpA object, along with its data, into the CA(j) object, the newly created j-th element of the array.

Immediately afterward, Set tmpA = Nothing removes the temporary object from memory.

The For…Next loop then repeats this process four more times, allowing input for additional items. Each time, a new tmpA object is created, populated with data, and then stored in the CA array.

After the loop, two lines of code determine the lower and upper bounds of the array (the smallest and largest index values).

Next, a Debug.Print statement prints a heading line in 14-character-wide columns in the Debug window. A second For…Next loop then iterates through the array, using the LBound and UBound values to access each object.

Inside this loop, a With…End With structure is used to simplify referencing the current object (CA(j)). This avoids repeatedly writing CA(j).strDesc, CA(j).dblLength, CA(j).dblWidth, and CA(j).Area. Instead, the property values and the Area() method result are printed more concisely.

Finally, you can remove the comment marker from the Stop statement to pause the program during execution. Run the code again, enter five sets of item details, and experiment with the object array. You can access or modify any property of any object in the array by referencing it with its index number, and you can print the results in the Debug window as shown.

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

The statement Set CA(j) = Nothing within the For ... Next Loop clears the array of objects, one by one, from Memory.

Pass Class Object Array as Function Parameter.

We can also pass the Class Object Array as a parameter to another program, similar to how we worked with a User-Defined Data Type.

To demonstrate this, let’s separate the printing section of the code from the main program and move it into its own subroutine.

Copy the ClassArray() subroutine code.
Paste it into the same Standard Module.
Rename the new procedure to ClassArray2(), as shown below.

This new subroutine will handle the printing logic independently, while still working with the array of class objects that we pass to it.

Public Sub ClassArray2()
Dim tmpA As ClsArea
Dim CA() As ClsArea
Dim j As Long, title As String
Dim L As Long, U As Long

title = "ClassArray"
For j = 1 To 5 ‘the Loop is set for 5 items
  'instantiate temporary ClsArea Object
  'to enter input
  Set tmpA = New ClsArea

     tmpA.strDesc = InputBox(Str(j) & ") Description:", title, "")
     tmpA.dblLength = InputBox(Str(j) & ") Enter Length:", title, 0)
     tmpA.dblWidth = InputBox(Str(j) & ") Enter Width:", title, 0)
    
  'Redimension Array
  ReDim Preserve CA(1 To j) As ClsArea
  Set CA(j) = tmpA   'Copy Object to Array
  
 Set tmpA = Nothing 'Remove temporary object from memory.
Next

‘PRINTING SECTION
'Print the Array contents into the Debug Window
L = LBound(CA)
U = UBound(CA)

Debug.Print "Description", "Length", "Width", "Area"
For j = L To U
  With CA(j)
    Debug.Print .strDesc, .dblLength, .dblWidth, .Area
  End With
Next

’stop

'Clear array objects from memory
Erase CA

End Sub

The ClassArray2 Code Without Printing Section.

Create a new Sub-Routine as given below:

Public Sub ClassPrint(ByRef clsPrint() As ClsArea)
Dim L As Long, U As Long
Dim j As Long

End Sub

In the next step, we will separate the printing logic into its own subroutine. To do this, highlight the printing section of the ClassArray2() program, up to the Stop statement, cut it, and paste it below the Dim statements in a new subroutine named ClassPrint(). The revised versions of both programs are shown below:

Public Sub ClassArray2()

Dim tmpA As ClsArea
Dim CA() As ClsArea
Dim j As Long, title As String

title = "ClassArray"
For j = 1 To 5 ‘the Loop is set for 5 items
  'instantiate temporary ClsArea Object
  'to enter input
  Set tmpA = New ClsArea
     tmpA.strDesc = InputBox(Str(j) & ") Description:", title, "")
     tmpA.dblLength = InputBox(Str(j) & ") Enter Length:", title, 0)
     tmpA.dblWidth = InputBox(Str(j) & ") Enter Width:", title, 0)
    
  'Redimension Array
  ReDim Preserve CA(1 To j) As ClsArea
  Set CA(j) = tmpA   'Copy Object to Array
  
 Set tmpA = Nothing 'Remove temporary object from memory.
Next

Call ClassPrint(CA) ‘Pass the Object Array to print routine

'Clear array objects from memory

Erase CA

End Sub

The ClassPrint() Program.

Public Sub ClassPrint(ByRef clsPrint() As ClsArea)
Dim L As Long, U As Long
Dim j As Long

'Printing Section
'Print the Array Contents into the Debug Window.
L = LBound(clsPrint)
U = UBound(clsPrint)

Debug.Print "Description", "Length", "Width", "Area"
For j = L To U
  With clsPrint(j)
    Debug.Print .strDesc, .dblLength, .dblWidth, .Area
  End With
Next

End Sub

Review the code changes in both programs carefully and make corrections wherever necessary. Once ready, place the cursor in the middle of the ClassArray2() code and press F5 to run the program.

In this version, the Call statement in the first program passes the CA Class Object Array as a ByRef parameter to the ClassPrint() subroutine. The subroutine then prints the object properties and invokes the Area() function to calculate and display the results.

In the next session, we will extend our ClsArea class module by using it as a base class to build a new VBA Class Object that calculates the volume of a given item.

Introduction.

ClsRecUpdate Class Module.

The Test Program for ClsUpdate.

Introduction.

The SetNewVol2_2 Procedure.

VBA Code Review.

Display the Locals Window.

Inspecting the Locals Window

New Class Module ClsVolume3.

The Testing Program.

Working with the Recordset Object.

List of All the Links on this Topic.

Earlier Post Link References:

Introduction.

ClsVolume Class, the Makeover.

Get / Set instead of the Get / Let Property Procedure.

A Test Program in Standard Module.

Code Review Line by Line.

The Locals Window View.

Usage of ClsArea and ClsVolume2 Class Objects Differently.

The Links of All the Pages in this Topic.

Introduction.

The Volume Class: ClsVolume.

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

The ClsVolume Derived Class Module Code.

The Main Program that Uses the ClsVolume Class.

Validation Checks Performance Tests.

The Changed Code of Main Programs.

The Links of All Pages on this Topic.

Earlier Post Link References:

Introduction.

The ClassArray() Sub-Routine.

The Sample Run of the Program is given below:

Review of Code Line by Line.

Pass Class Object Array as Function Parameter.

The ClassArray2 Code Without Printing Section.

The ClassPrint() Program.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

Post Feed

Get new posts by email:

Search

USEFUL LINKS

Popular Posts

About Me

Blog Archive

Labels

Statcounter

**Get / Set instead of the Get / Let Property Procedure.**

**The Locals Window View.**