2018 ~ LEARN MS-ACCESS TIPS AND TRICKS

Ms-Access and Collection Object Basics

a.p.r. pillaiDecember 31, 2018Accesstips, Array, Collection Object 3 comments

Introduction.

In VBA, Arrays are more commonly used than Collection objects for storing multiple sets of related values (in rows and columns). We have already used Arrays to store User-Defined Types and Class Module objects. Now, it’s time to explore something new—the use of Collection and Dictionary objects. The Collection object is particularly convenient for grouping related items together. As for the Dictionary object, we will discuss its usage at the appropriate time.

When using Arrays, we must dimension a variable, User-Defined Type, or Class Module object with the required number of elements in advance—or re-dimension it later to increase or decrease its size—before storing values in it. This extra step is not necessary with a Collection object. Once a Collection object is instantiated, we can dynamically add any number of items to it. Its members can be of any data type, including built-in objects, Class Module objects, or even other Collection objects with their own item members.

Collection Demo Program.

Let us write a simple program to demonstrate the usage of a Collection Object.

Public Sub CollTest1()
Dim C As Collection
Dim j As Integer

‘instantiate the Object
Set C = New Collection
                            
C.Add 5
C.Add 15
C.Add "iPhone"
C.Add "Disk 2TB"
C.Add 35.75

'Print the items in debug window
GoSub Listing

C.Remove 3 'Remove 3rd item

GoSub Listing

Set C = Nothing

Exit Sub

Listing:
Debug.Print
For j = 1 To C.Count
    Debug.Print C.Item(j)
Next
Return

End Sub

Code Review Line By Line.

The first two lines declare the Variable C as a Collection Object. The next line declares the Variable j as an Integer type, as a control variable for the For... Next Loop. The third line instantiates the Collection Object C in memory.

The Collection Object has four built-in methods: Add, Count, Item, and Remove, for managing the Collection items in memory.

An image of the Collection Object instance below, displaying its methods list.

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

The Add method inserts a value or object as a member of the Collection object, while the Remove method deletes an item from it.
To access a specific item, we can use the Item method with its index number. The Count property returns the total number of items in the Collection.
In the current example, we are not working with object-type items but with a few simple mixed data types—Integer, String, and Double—as members of the Collection. Using the Add method of the Collection object named C, we have inserted five items: the first two are integers, the next two are strings, and the last one is a double-precision number.
The syntax combines the Collection instance name (C) and the Add method using a dot (.) separator, followed by a space, and then the actual value to be added.
The Add method accepts four optional parameters, as shown in the image below.

Right-click to open Large Image in New Tab/Window.
The parameters: Item, [Key], [Before], [After]. The first parameter, Item, is mandatory; the Value to be added to the Collection.
The next three parameters are optional.
When using any of the optional parameters of the Add method, you must insert comma placeholders to skip over unused parameters—except when you are specifying parameters from the rightmost side in order.
Alternatively, you can explicitly use parameter names along with their values, which allows you to provide the parameters in any order.

We will explore this technique in another VBA example.

Note: Do not get confused with the Item Parameter of the Add Method with the Item() Method of the Collection Object.

We have added five items as members of the Collection Object with the Add method. Two Integer Type values, two String data Type Values, and one double-precision number.

It demonstrates that you can add any data type, except User-Defined Type (UDTs), into the Collection. When you want to add UDTs into a Collection, convert your UDTs into a Class Module Object.

Next, the program calls a printing subroutine that outputs the Collection members to the Debug window. This subroutine uses a For...Next loop that runs from 1 to the total number of items (C.Count) in the Collection. The loop control variable j is used as the index parameter of the Collection’s Item() method to retrieve each value and print it to the Debug window.

The next Line removes the third item (iPhone) from the item members by calling the Remove method.

The printing subroutine is called one more time to print the changed list of items, after the removal of the third item from the earlier list.

The Exit Sub statement prevents the program control from dropping into the internal subroutine lines and stops the program. The listing will appear in the Debug Window as shown below.

The Output in the Debug Window.

5 
15 
iPhone
Disk 2TB
35.75

5 
15 
Disk 2TB
35.75

We can insert a value before a particular item member by specifying the item number with the Before key Name.

C.Add 2, Before:=1 ‘add value 2 Before existing first item

C.Add 2,,1

The above statement will add value 2 as the first item in the above program, pushing all existing items down.

C.Add 20, After:=3 ‘Add value 20 After existing item number 3

C.Add 20,,,3

This statement inserts the value 20 after the third item, after value 15, in the list.

The Code below demonstrates the Before:= and After:= Parameter Names.

Public Sub CollTest2()
Dim C As Collection
Dim j As Integer

Set C = New Collection

C.Add 5
C.Add 15
C.Add "iPhone"
C.Add "Disk 2TB"
C.Add 35.75

GoSub Listing

C.Add 2, Before:=1 'Insert the item before the first item
C.Add 20, After:=3 'Insert the item after first 3 items

GoSub Listing

Set C = Nothing
Exit Sub

Listing:
'Print the items
Debug.Print
For j = 1 To C.Count
   Debug.Print C(j)

Next
Return

End Sub

Note: The advantage of using parameter names is that you can pass the values in any order you want when you need to use more than one parameter in a statement.

C.Add After:=3,Item:=20

The second example demonstrates the use of parameter values without explicitly specifying their parameter names, placing each value in its correct positional order.

Public Sub CollTest2_2() Dim C As Collection Dim k As Integer

Set C = New Collection C.Add 5 C.Add 15 C.Add "iPhone" C.Add "Disk 2TB" C.Add 35.75 GoSub Listing C.Add 2, , 1 'Insert the item before the first item C.Add 20, , , 3 'Insert the item after first 3 items GoSub Listing Set C = Nothing Exit Sub Listing: 'Print the items Debug.Print For k = 1 To C.Count Debug.Print C(k) Next: Debug.Print Return End Sub

Sample printout on the Debug Window is shown below:

5 
 15 
iPhone
Disk 2TB
 35.75 

 2 
 5 
 15 
 20 
iPhone
Disk 2TB
 35.75

In all our printing examples, we have used the Item’s index number to retrieve values for display in the Debug Window. However, when a collection contains many items, it becomes difficult to remember the index number of a specific item we want to access. To overcome this, we can associate each item with an easily memorable Key along with its Value—for example, using a contact’s first name as the key in an address book collection—so we can retrieve the item’s value directly by its key instead of relying on its index number.

Usage of Item Keys

Let’s write a new program to demonstrate how to use Keys with Values in a Collection.

Public Sub CollTest3()
Dim C As Collection
Dim strKey As String
Dim strGet As String

Set C = New Collection

C.Add 5, Key:="FIVE" 
C.Add 15, Key:="FIFTEEN"
C.Add "iPhone", "7+"     'you can omit the KEY param name
C.Add "Disk 2TB", "DISK" ' Add method's 2nd Parameter is KEY
C.Add 35.75, "999"

'add value 2 with Key "TWO" before the first item.
'this item will be the first item in the collection
'parameter names not in proper order – valid

C.Add Item:=2, Before:=1, Key:="TWO"

'add value 7 with Key "SEVEN" as third item in the collection
'parameter names not in proper order – valid

C.Add Key:="SEVEN", Item:=7, After:=2

'Retrieve value using it's KEY from collection

strKey = ""
Do While strKey = ""
    strKey = InputBox("Value Key: " & vbCr & vbCr & "Q - Quit", "Enter Key", "")
    
    Select Case strKey
        Case "Q"
           Exit Do
        Case "TWO", "FIVE", "SEVEN", "FIFTEEN", "7+", "DISK", "999"
           strGet = C(strKey)
        Case Else
           strGet = " Not Found!"
   End Select
   
MsgBox "Key:<<" & strKey & ">> Value: " & strGet
strKey = ""
Loop

Set C = Nothing

End Sub

The KEY value must be of String Type. The KEY value must be a unique identifier.

Refer to the second image, on top of this page, as a reference for the proper order of the Parameters of the Add Method of Collection Object, displayed by VBA Intellisense.

The sample programs shown earlier, with mixed data types, were intended solely for demonstration purposes. In practical use, a Collection object typically stores items of a single data type, most commonly used to hold objects—such as Forms, Reports, Class Module objects, or Database objects—along with their associated properties and methods.

CLASS MODULES.

COLLECTION OBJECT.

DICTIONARY OBJECT.

Wrapper Class Functionality Transformation

a.p.r. pillaiDecember 16, 2018Array, Calculation, Class Module, VBA, Wrapper Classes 7 comments

Introduction.

Last week, we created a new Wrapper Class named ClsTiles, which used the ClsArea Class twice within the same Class Module—one instance to store the room’s floor dimensions and another to store the tile dimensions—to calculate the number of tiles needed for the room.

In this new Wrapper Class Module, we will take the existing ClsVolume2 Class and transform it into a ClsSales Class. With a few cosmetic changes, we will give it a complete facelift inside the Wrapper Class, concealing its original purpose as a volume calculation class and repurposing it to calculate the selling price of products with a discount.

Interestingly, the ClsVolume2 Class already has all the necessary properties to store sales-related data: strDesc, dblLength, dblWidth, and dblHeight can be repurposed to represent Description, Quantity, Unit Price, and Discount Percentage, respectively.

Remember, the ClsVolume2 Class itself is a derived class, originally built using ClsArea as its base class.ClsVolume2 Class Re-Visited.

But, first, the VBA Code of ClsVolume2 Class Module (the Base Class for our new ClsSales Class Module) is reproduced below for reference:

Option Compare Database
Option Explicit

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 = CArea.dblLength * CArea.dblWidth * Me.dblHeight
End Function

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

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

Private Sub Class_Terminate()
Set p_Area = Nothing
End Sub

The only obstacle preventing us from using the ClsVolume2 Class directly for sales data entry is that its Property Procedure names—dblLength, dblWidth, and dblHeight—do not correspond to the required sales-related properties: Quantity, Unit Price, and Discount Percentage.

Fortunately, all these properties in the ClsVolume2 Class are of the Double data type, which is perfectly suitable for our sales calculations, so no data type changes are needed.

Similarly, the public function names Area() and Volume() are not meaningful in the context of sales, but their underlying calculation logic can be repurposed for sales-related computations without modification.

a) Area = dblLength * dblWidth is suitable for TotalPrice = Quantity * UnitPrice

b) Volume = Area * dblHeight is good for DiscountAmount = TotalPrice * DiscountPercentage

Here, we have two choices to use the ClsVolume2 as the ClsSales Class.

The easiest approach is to create a copy of the ClsVolume2 Class and save it as a new Class Module named ClsSales. Then, update the Property Procedure names and public Function names to reflect sales-related values and calculations. You can also add any additional functions needed for handling sales operations directly within this new class module.
Create a Wrapper Class based on the ClsVolume2 Base Class. In the Wrapper Class, implement corresponding property procedures and public functions that encapsulate and rename the Base Class’s property procedures and functions, effectively masking their original names. Add new functions, if any, to the Wrapper Class as needed.

The first option is relatively straightforward and easy to implement. However, we will choose the second option to learn how to access the Base Class’s properties within the new Wrapper Class and how to mask their original names with new property names.

The Transformed ClsVolume2 Class.

Open your Database and display the VBA Editing Window (Alt+F11).
Select the Class Module from the Insert Menu to insert a new Class Module.
Change the Class Module’s Name property Value from Class1 to ClsSales.

Copy and Paste the following VBA Code into the Module and Save the Code:

Option Compare Database
Option Explicit

Private m_Sales As ClsVolume2

Private Sub Class_Initialize()
    'instantiate the Base Class in Memory
    Set m_Sales = New ClsVolume2
End Sub

Private Sub Class_Terminate()
    'Clear the Base Class from Memory
    Set m_Sales = Nothing
End Sub

Public Property Get Description() As String
  Description = m_Sales.CArea.strDesc 'Get from Base Class
End Property

Public Property Let Description(ByVal strValue As String)
  m_Sales.CArea.strDesc = strValue ' Assign to Base Class
End Property

Public Property Get Quantity() As Double
Quantity = m_Sales.CArea.dblLength
End Property

Public Property Let Quantity(ByVal dblValue As Double)
   If dblValue > 0 Then
     m_Sales.CArea.dblLength = dblValue ' Assign to clsArea, Base Class of ClsVolume2
   Else
     MsgBox "Quantity: " & dblValue & " Invalid.", vbExclamation, "ClsSales"
     Do While m_Sales.CArea.dblLength <= 0
          m_Sales.CArea.dblLength = InputBox("Quantity:, Valid Value >0")
     Loop
   End If
End Property

Public Property Get UnitPrice() As Double
   UnitPrice = m_Sales.CArea.dblWidth
End Property

Public Property Let UnitPrice(ByVal dblValue As Double)
   If dblValue > 0 Then
      m_Sales.CArea.dblWidth = dblValue ' Assign to clsArea, Base Class of ClsVolume2
   Else
     MsgBox "UnitPrice: " & dblValue & " Invalid.", vbExclamation, "ClsSales"
       Do While m_Sales.CArea.dblWidth <= 0
          m_Sales.CArea.dblWidth = InputBox("UnitPrice:, Valid Value >0")
       Loop
   End If
End Property

Public Property Get DiscountPercent() As Double
   DiscountPercent = m_Sales.dblHeight
End Property

Public Property Let DiscountPercent(ByVal dblValue As Double)
' Assign to Class .dblHeight of ClsVolume2
Select Case dblValue
    Case Is <= 0
       MsgBox "Discount % -ve Value" & dblValue & " Invalid!", vbExclamation, "ClsSales"
       Do While m_Sales.dblHeight <= 0
          m_Sales.dblHeight = InputBox("Discount %, Valid Value >0")
       Loop
    Case Is >= 1
       m_Sales.dblHeight = dblValue / 100
    Case 0.01 To 0.75
       m_Sales.dblHeight = dblValue
End Select
End Property

Public Function TotalPrice() As Double
Dim Q As Double, U As Double
Q = m_Sales.CArea.dblLength
U = m_Sales.CArea.dblWidth
If (Q * U) = 0 Then
   MsgBox "Quantity / UnitPrice Value(s) 0", vbExclamation, "ClsVolume"
Else
   TotalPrice = m_Sales.CArea.Area 'Get from Base Class ClsArea
End If
End Function

Public Function DiscountAmount() As Double
   DiscountAmount = TotalPrice * DiscountPercent
End Function

Public Function PriceAfterDiscount()
   PriceAfterDiscount = TotalPrice - DiscountAmount
End Function

So far in the Wrapper Class, we have created an instance of the ClsVolume2 class, renamed its property and function members to more suitable names, and added validation checks with appropriate error messages. We also ensured that invalid input does not trigger the Base Class’s own validation routines, which could otherwise display error messages like “Value of the dblLength property is invalid” from the ClsVolume2 class.

Review the highlighted lines in the code above; they should help you understand how the property values are assigned to and retrieved from the Base Class ClsVolume2.

First, review the ClsArea Class Module, and then examine the ClsVolume2 Class Module, which is derived from ClsArea. After understanding both of these, revisit the code in this Wrapper Class for a better perspective.

Test Program for ClsSales Class in Standard Module.

Let us write a Test Program to try out the Wrapper Class.

Copy and Paste the following VBA Code into a Standard Module.

Public Sub SalesTest()
Dim S As ClsSales

Set S = New ClsSales

S.Description = "Micro Drive"
S.Quantity = 12
S.UnitPrice = 25
S.DiscountPercent = 0.07

Debug.Print "Desccription", "Quantity", "UnitPrice", "Total Price", "Disc. Amt", "To Pay"
With S
    Debug.Print .Description, .Quantity, .UnitPrice, .TotalPrice, .DiscountAmount, .PriceAfterDiscount
End With
End Sub

Run The Code.

Keep the Debug Window open (Ctrl+G).
Click somewhere in the middle of the Code and press the F5 key to run the Code and to print the output in the Debug Window.
You can further test the code by entering negative numbers for any of the input values and running it to trigger the new error messages. You may also try disabling one or more input lines by placing a comment symbol (') at the beginning of the line, then rerun the code and observe the results.

Calculate Price/Discount for an Array of Products.

The following test code creates an array of three Products and Sales Values by entering directly from the Keyboard.

Copy and paste the following Code into a Standard Module and run to test the Wrapper Class further.

Public Sub SalesTest2()

Dim S() As ClsSales
Dim tmp As ClsSales
Dim j As Long

For j = 1 To 3
Set tmp = New ClsSales
   tmp.Description = InputBox(j & ") Description")
   tmp.Quantity = InputBox(j & ") Quantity")
   tmp.UnitPrice = InputBox(j & ") UnitPrice")
   tmp.DiscountPercent = InputBox(j & ") Discount Percentage")
 ReDim Preserve S(1 To j) As ClsSales
 Set S(j) = tmp
 Set tmp = Nothing
Next

'Output Section
Debug.Print "Desccription", "Quantity", "UnitPrice", "Total Price", "Disc. Amt", "To Pay"
For j = 1 To 3
   With S(j)
       Debug.Print .Description, .Quantity, .UnitPrice, .TotalPrice, .DiscountAmount, .PriceAfterDiscount
   End With
Next

For j = 1 To 3
  Set S(j) = Nothing
Next

End Sub

Once the correct values have been successfully entered into the array, the product names and corresponding sales values are displayed in the Debug Window.

CLASS MODULES.

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

Demo Database Download

WrapperClass.zip

COLLECTION OBJECT.

DICTIONARY OBJECT.

Access Class Module and Wrapper Classes

a.p.r. pillaiDecember 06, 2018 No comments

Introduction.

There are times when a particular Class Module needs to be instantiated more than once to use a similar set of values for a specific application.

For example, our ClsArea Class Module, which we have designed for calculating the carpet area of Rooms, is a candidate for similar applications. Assuming we want to determine the number of floor tiles needed for a room, we should be able to input the Length and width of the tile, as well as the dimensions of the Floor. Since both Floor and Tile have similar Property values as input, we can use two instances of the ClsArea Class Module, one instance for the FLOOR area and the second instance for the TILE area calculations. Floor Area / Tile Area gives the total number of tiles for a particular room.

Two Instances of the same Class Module.

We can do this by creating two different instances of the ClsArea Class Module in the Standard Module Program if there is only one Room.

Public Sub FloorTiles()
Dim FLOOR As ClsArea
Dim TILES As ClsArea
Dim flrArea As Double, tilearea As Double
Dim lngTiles As Long

Set FLOOR = New ClsArea
Set TILES = New ClsArea

FLOOR.strDesc = "Bed Room1"
FLOOR.dblLength = 25
FLOOR.dblWidth = 15
flrArea = FLOOR.Area()

TILES.strDesc = "Off-White"
TILES.dblLength = 2.5
TILES.dblWidth = 1.25
tilearea = TILES.Area()

lngTiles = flrArea / tilearea

Debug.Print FLOOR.strDesc & " Required Tiles: " & lngTiles & " Numbers - Color: " & TILES.strDesc

Set FLOOR = Nothing
Set TILES = Nothing

End Sub

Handling an Array of Objects.

But what if we want to apply the same method to an array of rooms, each with different dimensions or tile colors?

The solution is to create a new Class Module that contains two separate instances of the ClsArea Class—one instance to represent the Floor properties and the other to represent the Tile properties. Both of these instances can be encapsulated (wrapped) within the new Class Module.

Let us do that.

Open your Database and display the Code Editing Window (ALT+F11).
Select the Class Module from the Insert Menu.
Change the Name Property value to ClsTiles.
Copy and Paste the following VBA Code into the ClsTiles Class Module and save the Code:
```
Option Compare Database
Option Explicit

Private pFLOOR As ClsArea
Private pTILES As ClsArea

Private Sub Class_Initialize()
    Set pFLOOR = New ClsArea
    Set pTILES = New ClsArea
End Sub

Private Sub Class_Terminate()
    Set pFLOOR = Nothing
    Set pTILES = Nothing
End Sub

Public Property Get Floor() As ClsArea
 Set Floor = pFLOOR
End Property

Public Property Set Floor(ByRef NewValue As ClsArea)
  Set pFLOOR = NewValue
End Property

Public Property Get Tiles() As ClsArea
  Set Tiles = pTILES
End Property

Public Property Set Tiles(ByRef NewValue As ClsArea)
  Set pTILES = NewValue
End Property

Public Function NoOfTiles() As Long
   NoOfTiles = pFLOOR.Area() / pTILES.Area()
End Function
```
Both instances, pFLOOR and pTILES, are declared as Private Properties of the ClsTiles Class Object.
The Class_Initialize() Subroutine instantiates both objects in memory when the ClsTiles Class Module is instantiated in the user program.
The Class_Terminate() subroutine removes both instances (pFLOOR and pTILES) from memory when the ClsTiles Class Module instance is set to Nothing in the user program.
The Get and Set Property Procedures enable retrieving values from, and assigning values to, the pFLOOR instance within the ClsTiles Class Object.

We have added a new function, NoOfTiles(), in the New Class Module to calculate the number of tiles, based on the floor area and Tile dimensions.
Let us write a Program and learn the usage of multiple instances of the same Class Object in a new Class Module: ClsTiles.
The next Get and Set Property Procedures allow the same operations in the pTILES instance of the ClsArea Class.

Copy and paste the following VBA Code into a Standard Module:

Public Sub TilesCalc()
Dim FTiles As ClsTiles
Dim TotalTiles As Long

Set FTiles = New ClsTiles

FTiles.Floor.strDesc = "Warehouse"
FTiles.Floor.dblLength = 100
FTiles.Floor.dblWidth = 50

FTiles.Tiles.dblLength = 2.5
FTiles.Tiles.dblWidth = 1.75

TotalTiles = FTiles.NoOfTiles()

Debug.Print "Site Name", "Floor Area", "Tile Area", "No. of Tiles"
Debug.Print FTiles.Floor.strDesc, FTiles.Floor.Area, FTiles.Tiles.Area, TotalTiles

End Sub

Keep the Debug Window Open (CTRL+G) to print the test data.
Click somewhere in the middle of the VBA Code and press the F5 Key to run the Code. The result is printed on the Debug Window.
If you need to calculate the tile requirements for multiple rooms or for rooms across several floors of a high-rise building, running the above program repeatedly and manually recording the results would be impractical.

Finding Tile Requirement of Several Rooms.
Now, let us write another program to find the Tile requirements of several Rooms, with an Array of ClsTiles Objects by inputting the Property values directly from the keyboard.

Copy and Paste the following VBA Code into a Standard Module.

Public Sub TilesCalc2()
Dim tmpFT As ClsTiles
Dim FTiles() As ClsTiles
Dim j As Long, L As Long, H As Long

For j = 1 To 3
   Set tmpFT = New ClsTiles
      'Floor dimension
    With tmpFT.Floor
      .strDesc = InputBox(Str(j) & ") Floor Desc", , 0)
      .dblLength = InputBox(Str(j) & ") Floor Length", , 0)
      .dblWidth = InputBox(Str(j) & ") Floor Width", , 0)
    End With
    
    'Tile Dimension
    With tmpFT.Tiles
      .strDesc = InputBox(Str(j) & ") Tiles Desc", , 0)
      .dblLength = InputBox(Str(j) & ") Tile Length", , 0)
      .dblWidth = InputBox(Str(j) & ") Tile Width", , 0)
    End With
   
   ReDim Preserve FTiles(1 To j) As ClsTiles
   Set FTiles(j) = tmpFT
   
   Set tmpFT = Nothing
Next

'Take Printout
L = LBound(FTiles)
H = UBound(FTiles)

Debug.Print "FLOOR", "Floor Area", "TILES", "Tile Area", "Total Tiles"
For j = L To H
  With FTiles(j)
   Debug.Print .Floor.strDesc, .Floor.Area(), .Tiles.strDesc, .Tiles.Area(), .NoOfTiles
  End With
Next
   
'Remove all objects from memory
For j = L To H
   Set FTiles(j) = Nothing
Next
  
End Sub

Keep the Debug Window open to print the output there.
Run the Code as before and input values for Floor and Tile dimensions for three Rooms.

As you can see from the above code, both values of Room and Tile dimensions go into the same Class Object Array instance, side by side.

The above program is a demo that runs only for three sets of values within the For... Next loop. It can be modified with a conditional loop that runs a required number of times till a conditional break code terminates the program.

The program can be modified to save each set of data values and calculation results in a Table for future reference.

A Wrapper Class is a container class designed to hold instances of other classes, data structures, or collections of objects. In this case, we are using it to encapsulate two instances of the same class object.

List of All the Links on this Topic.

Earlier Post Link References:

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.