Access Class Module and Wrapper Classes ~ LEARN MS-ACCESS TIPS AND TRICKS

Thursday, December 6, 2018

Access Class Module and Wrapper Classes

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.

LEARN MS-ACCESS TIPS AND TRICKS

Thursday, December 6, 2018