Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Ms-Access and Collection Object Basics

Introduction.

VBA Array is more popular and commonly used, for storing several sets of related values (rows and columns) than Collection Object.  We have used Arrays to store User-Defined Types and Class Module objects.  I think it is time to learn something new and different, the usage of Collection and Dictionary Objects. The Collection Object is very convenient to store related items as a group.  Why Dictionary Object, we will take it up at an appropriate time?

To use Arrays, we need to Dimension a Variable, User-Defined Type, or Class Module Object for the required number of elements in advance, or Re-dimension to increase or decrease the size of an Array before we are able to store value(s) into them. But this procedure is not required for Collection Object.  After instantiating the Collection Object, we can add any number of items to it. The Collection members can be of any data type, built-in Objects, Class-Module Object, or another Collection Object, with its own item members.

Collection Demo Program.

Let us write a simple program to demonstrate the usage of 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.

  • The Add method adds value or object as a Collection member to the Collection Object and the Remove method is for deleting an item from the Collection.

  • With the Item method combined with an item number as Index, we can retrieve a particular item from the Collection. The Count method gives the total items count in the Collection.

  • In the above example, we are not using any Object type items, but a few simple mixed data Types  Integer, String, and Double Precision Number, as Collection members.

  • By using the Add method of C Collection Object we have added five items to the collection.  The first two items are integer numbers, the next two items are String data types and the last one is a  double-precision number. 

  • The Collection Object instance name C and the Add method are joined with a dot separator, followed by space than the actual value to be added to the Collection.

  • The Add method has four parameters, check the image given below:

  • 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 any of the optional parameters are used with the Add method other parameter places must be skipped with a comma separator, except for the right side items.  Explicit usage of parameter names with their values enables us to give the parameter values in any order.  We will learn their usage in another example VBA Code. 

    Note: Do not get confused with the Item Parameter of Add Method with the Item() Method of 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 simply 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, we are calling a printing sub-routine, within the program, that prints the Collection members to the debug window.  The sub-routine has a For . . . Next Loop to run from 1 to the number of items (C.Count) in the Collection.  The value in the j control variable is used as the index number parameter to the Item() method of the Collection Object, to retrieve the value and print it in the debug window.

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

The printing sub-routine is called one more time to display the changed list of items, after the removal of the third item in the old list.

The Exit Sub statement prevents the control of the program 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 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  Parameter Name.

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

OR

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

OR

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

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 is without the use of Parameter Names and giving the parameter value in its proper position.

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 the values to print them in the Debug Window.  When there are several items in the collection it is very difficult to keep track of a particular Item's Index number to retrieve the specific value we want.  To overcome this problem we can store an easily memorable Key-Value, along with the Item Value, like the First Name of a Contact in the Address Book, to retrieve the item value randomly from the Address Book Collection members.

Usage of Item Keys

Let us write a new program to demonstrate the usage of Item Keys with Values in the 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 Parameters of Add Method of Collection Object, displayed by VBA Intellisense.

The above sample programs with mixed types of data items were presented for demonstration purposes only.  The Collection Object will normally contain only one type of data and is mostly used for adding Objects, like Forms, Reports, Class-Module Objects, Database Objects, and so on, with their properties and methods.

CLASS MODULES.

  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object Arrays
  3. MS-Access Base Class and Derived Objects
  4. VBA Base Class and Derived Objects-2
  5. Base Class and Derived Object Variants
  6. Ms-Access Recordset and Class Module
  7. Access Class Module and Wrapper Classes
  8. Wrapper Class Functionality Transformation

COLLECTION OBJECT.

  1. Ms-Access and Collection Object Basics
  2. Ms-Access Class Module and Collection Object
  3. Table Records in Collection Object and Form

DICTIONARY OBJECT.

  1. Dictionary Object Basics
  2. Dictionary Object Basics-2
  3. Sorting Dictionary Object Keys and Items
  4. Display Records from Dictionary to Form
  5. Add Class Objects as Dictionary Items
  6. Update Class Object Dictionary Item on Form
Share:

Wrapper Class Functionality Transformation

Introduction.

Last week we have created a new Wrapper Class ClsTiles, using the  ClsArea Class twice in the new Class Module, one instance for Floor dimension values, and the second instance for Floor-Tile dimension, to calculate the number of Tiles for the room.

In the new Wrapper Class  Module, we will transform the Volume Class (ClsVolume2) into the Sales (ClsSales) Class.  With some cosmetic changes, we will give it a total face-lift in the Wrapper Class,  hiding its true identity as a Volume calculation Class, and use it for calculating the Selling Price of Products with Discount.

 That’s right, our ClsVolume2 Class has all the necessary properties to enter the required Sales data values like Description, Quantity, Unit Price, and Discount Percentage, which will go into the Volume Class Properties strDesc, dblLength, dblWidth, and dblHeight respectively. 

We should not forget that the ClsVolume2 Class is a Derived Class, built using ClsArea as 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 problem that prevents us from using ClsVolume2 Class directly to the Sales data entry is that the Property Procedure names dblLength, dblWidth, and dblHeight do not match for the Sales property values Quantity, Unit Price, Discount Percentage.   The numeric data types of ClsVolume2 Class are all double precision numbers and they are suitable for our Sales-Class Object and can be used without data Type change.  The public functions Area() and Volume()  names are also not suitable, but their calculation formula can be used for Sales calculations without change. 

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 make use of the ClsVolume2 Class as ClsSales Class.

  1. The easiest way is to make a copy of the ClsVolume2 Class and save it in a new class Module with the name ClsSales.  Make appropriate changes to the Property Procedure and public Function names suitable for sales values and calculations.  Add more functions, if required, in the new class module.

  2. Create a Wrapper Class using ClsVolume2 as Base Class and create suitable property procedures and public function name changes, masking the Base Class’s Property Procedures and Function names.  Create new Functions in the Wrapper Class, if necessary.

The first option is somewhat straightforward and easy to implement.  But, we will select the second option to learn how to address the Base Class’s Properties in the new wrapper Class and how to mask its original property names with new ones. 

The Transformed ClsVolume2 Class.

  1. Open your Database and display the VBA Editing Window (Alt+F11).

  2. Select the Class Module from Insert Menu, to insert a new Class Module.

  3. Change the Class Module’s Name property Value from Class1 to ClsSales.

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

What we already did, in the Wrapper Class? Created an instance of the ClsVolume2 Class and changed its Property Names, Function Names and added Validation checks with appropriate error messages and prevented from dropping into the validation check of the Base class with error messages like "Value of the dblLength property is invalid" may pop up from the Volume Class.

Check the lines I have highlighted in the above Code and I hope you will be able to figure out how the property values are assigned/retrieved to/from the Base Class ClsVolume2.

You may go through the ClsArea Class Module first and next to the ClsVolume2 Class Module – the derived Class using ClsArea Class as Base Class.  After going through both these Codes you may take a second look at the Code in this Wrapper Class.

Test Program for ClsSales Class in Standard Module.

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

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

  2. Keep the Debug Window open (Ctrl+G).

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

  4. You may test the Code further by entering any of the input values with a Negative number and running the code to trigger the new Error Message.  Disable any of the input lines, with a comment symbol ('), run the code, and see what happens.

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

After the successful entry of correct values into the Array, the product names and sales values are printed in the Debug window.

CLASS MODULES.

  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object Arrays
  3. MS-Access Base Class and Derived Objects
  4. VBA Base Class and Derived Objects-2
  5. Base Class and Derived Object Variants
  6. Ms-Access Recordset and Class Module
  7. Access Class Module and Wrapper Classes
  8. Wrapper Class Functionality Transformation

COLLECTION OBJECT.

  1. Ms-Access and Collection Object Basics
  2. Ms-Access Class Module and Collection Object
  3. Table Records in Collection Object and Form

DICTIONARY OBJECT.

  1. Dictionary Object Basics
  2. Dictionary Object Basics-2
  3. Sorting Dictionary Object Keys and Items
  4. Display Records from Dictionary to Form
  5. Add Class Objects as Dictionary Items
  6. Update Class Object Dictionary Item on Form
Share:

Access Class Module and Wrapper Classes

Introduction.

There are times that a particular Class Module needs to be instantiated more than once, to use a similar set of values for a particular 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 that we would like to find out how many floor tiles we need to lay in a room then we should be able to input the Length and Width values of the Tile also, besides the dimension values 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 need to apply the above method for an array of several rooms with different sizes or colors of tiles?  The answer to that is to create a new Class Module with two instances of the same ClsArea Class, one instance for Floor, and the other for Tiles Properties.  Both instances are wrapped in a new Class Module.

Let us do that.

  1. Open your Database and display Code Editing Window (ALT+F11).
  2. Select the Class Module from Insert Menu.
  3. Change the Name Property value to ClsTiles.
  4. Copy and Paste the following VBA Code into 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 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 allow retrieval and assignment of values, from and to the pFLOOR instance respectively, in the ClsTiles Class Object.

    The next Get and Set Property Procedures allow the same operations in the pTILES instance of the ClsArea Class.

    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.

  5. 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
     
  6. Keep the Debug Window Open (CTRL+G) to print the test data.
  7. 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 want to calculate the tile requirements of several rooms or rooms on several floors of a high-rise building, then you should run the above program a number of times and note down the values, which is practically very difficult.

    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.

  8. 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
    
    
  9. Keep the Debug Window open to print the output there.
  10. 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 for instances of other Classes, Data Structures, or instances collection of other objects.  Here we have used it to hold two instances of the same class Object.

List of All the Links on this Topic.

Earlier Post Link References:

  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object Arrays
  3. MS-Access Base Class and Derived Objects
  4. VBA Base Class and Derived Objects-2
  5. Base Class and Derived Object Variants
  6. Ms-Access Recordset and Class Module
  7. Access Class Module and Wrapper Classes
  8. Wrapper Class Functionality Transformation
  9. Ms-Access and Collection Object Basics
  10. Ms-Access Class Module and Collection Object
  11. Table Records in Collection Object and Form
  12. Dictionary Object Basics
  13. Dictionary Object Basics-2
  14. Sorting Dictionary Object Keys and Items
  15. Display Records from Dictionary to Form
  16. Add Class Objects as Dictionary Items
  17. Add Class Objects as Dictionary Items
  18. Update Class Object Dictionary Item on Form
Share:

MS-Access Recordset and Class Module

Introduction.

Here, we will build a Class Module for data processing tasks, a DAO.Recordset Object will be passed to the Custom Class Object.  Since it is an Object, that is passed to our Custom Class, we need the Set and Get Property Procedure pair to assign and retrieve the Object or its Property values.

We have a small Table: Table1, with a few records on it.  Here is the image of Table1.

The above table has only four fields: Desc, Qty, UnitPrice, and TotalPrice.  The TotalPrice 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 has a subroutine to sort the data, on the user-specified field, and dumps a listing on 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.

  1. Open your Access Database and open the VBA Window.

  2. Insert a Class Module.

  3. Change its Name Property Value to ClsRecUpdate.

  4. 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 accepts a Recordset object can be passed to the Class ClsRecUpdate  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 * 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 dumped on the Debug Window.  The listing of fields will be limited to five fields only, if the record source has more than that then the rest of the fields are ignored.

The TblCreate() subroutine will Sort the data, based on the column number passed as a parameter, and creates a Table with a new name.  The parameter is optional, if a column number is not passed as a parameter, then the Table will be sorted on the data in the first column if the data type of the column is a valid type. The original name of the Table will be modified and added with the String “_2” to the original name. If the Source Table name is Table1 then the new table name will be 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 pass any column numbers for updating a particular column. The column numbers not necessarily be consecutive numbers. But, the third column number parameter is the target column to update. The first parameter is multiplied by the second column parameter to arrive at the result value to update. You may modify the Class Module code to do any other operation you wish to do on the table.

List of All the Links on this Topic.

  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object Arrays
  3. MS-Access Base Class and Derived Objects
  4. VBA Base Class and Derived Objects-2
  5. Base Class and Derived Object Variants
  6. Ms-Access Recordset and Class Module
  7. Access Class Module and Wrapper Classes
  8. Wrapper Class Functionality Transformation
  9. Ms-Access and Collection Object Basics
  10. Ms-Access Class Module and Collection Object
  11. Table Records in Collection Object and Form
  12. Dictionary Object Basics
  13. Dictionary Object Basics-2
  14. Sorting Dictionary Object Keys and Items
  15. Display Records from Dictionary to Form
  16. Add Class Objects as Dictionary Items
  17. Update Class Object Dictionary Item on Form
Share:

Base Class and Derived Object Variants

Introduction.

Last week we tried an example to pass a Base Class Object, through the Set Property Procedure,  to become part of the Object in memory.  The passed object becomes an extension or Child-Object of the Main Object in memory.  In our earlier program, passing the child Object to the Target Object was done in the instantiating phase of our test program.  We have assigned values to the passed Object Properties in the later part of the program.  The next example is slightly different. 

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

This time we will open both Objects (ClsArea – the base class, ClsVolume2 – the target Class) separately in our test program.  Assigning values in the Base Class ClsArea Properties, before passing them to the target Class ClsVolume2 Object.  Remember the Volume2 Class has only one Property, the p_Height Property, and its Method Volume() needs the Length and Width Values of the Base Class ClsArea to calculate Volume. 

  1. 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 defined as ClsArea Object and Vol as ClsVolume2 Object.  The next two statements instantiate both objects in memory.

    The next three statements assign values to the properties of the ClsArea Class Object.

    The Stop statement gives a pause in the Code execution so that we can verify the Object Property values in the Locals Window.

    The Set Vol.CArea = CA statement assigns the ClsArea Class Object CA, as a child object into the Vol (ClsVolume2) Object. 

    In the Next step dblHeight Property of ClsVolume2 Class Object is assigned with the value 10.

    The following statements before the Stop statement print the Values from memory to the Debug Window.

    The next two Set Statements remove the Objects from memory, before ending the program.

    Display the Locals Window.

  2. Select Locals Window Option from the View Menu.

  3. Click somewhere in the middle of the Code and press F5 to run the code till the program pauses at the Stop statement. Alternatively, you can press F8 to run the code one step at a time to inspect the Locals Window for changes, at each step.

  4. Click on the [+] Symbol to expand and display both Objects Properties and values.

  5. Check the CArea and p_Area Object reference in the Value column of the Vol ObjectThe Value in there is showing as Nothing because we have not yet passed CA Object to the Vol Object.

  6. If you have finished viewing the Locals Window contents, then run the code till the next Stop statement.  Now, the CArea Get Property Procedure and p_Area Object are assigned to the ClsArea Class Object.

We will try another Variant example of both these two Classes ClsArea and ClsVolume2.

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

Check the Code from the beginning: p_Height declared as Private property. The p_Area Property of ClsVolume3 Class is declared as a Public ClsArea Object. That means p_Area will appear as a Property of the ClsVolume3 Class with its own displayable properties for direct Get/Let operations in the User Program, in the Standard Module. Even though ClsArea Class Object has been declared as Public Property of ClsVolume3 Class, its Properties and Methods are encapsulated in ClsArea Class itself. 

The ClsArea Class must be a fully developed object, and be error-free to use it within other Objects.

Check the Class_Initialize() and Class_Terminate() Sub-Routines. The ClsArea Object is instantiated in the Class_Initialize() Code and removes the Object from memory in Class_Terminate() Code 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 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:

  1. Calculate and update a Field,

  2. Sort the Data,

  3. Print the sorted data in the Debug Window,

  4. 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:

  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object Arrays
  3. MS-Access Base Class and Derived Objects
  4. VBA Base Class and Derived Objects-2
  5. Base Class and Derived Object Variants
  6. Ms-Access Recordset and Class Module
  7. Access Class Module and Wrapper Classes
  8. Wrapper Class Functionality Transformation
  9. Ms-Access and Collection Object Basics
  10. Ms-Access Class Module and Collection Object
  11. Table Records in Collection Object and Form
  12. Dictionary Object Basics
  13. Dictionary Object Basics-2
  14. Sorting Dictionary Object Keys and Items
  15. Display Records from Dictionary to Form
  16. Add Class Objects as Dictionary Items
  17. Update Class Object Dictionary Item on Form

Share:

VBA Base Class and Derived Object-2

Introduction.

Last week we have created a Derived Class ClsVolume Object, using Class ClsArea as Base Class.  We have created Property Procedures in the Derived Class to expose the Base Class’s Properties and Function to the Object user programs.  This method demands repetition of all the property procedures of the Base Class in the derived class too.  Here, we explore how to create the same Derived ClsVolume Class without repeating the Property Procedures of the Base ClsArea Class.

We have learned the use of the Get and Let Property Procedures in Classes.  There is one more Property Procedure used in Classes:  The Set Property Procedure.  The Set Property Procedure directly assigns an Object to a Class Object of the same Type.

Before continuing further you may visit the earlier pages on this topic, if you have not already done so, the links are given below:


ClsVolume Class, the Makeover.

We shall create a different variant of the same ClsVolume Class Module, that we have 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 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

Select Compile Project Name from Debug Menu to compile the VBA Code in the Database to ensure that all VBA Project Code is Err Free.  If you have encountered an Error in your other VBA Programs, please track down the error, correct it and recompile your Project.  Otherwise, the VBA IntelliSense that displays a list of properties and functions of Objects will not work.  That will not prevent us from assigning/retrieving values to/from Object Properties.  But, while learning it is important to see the Properties of an Object pop up and displays the list, as an assistant in Coding.

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

We have omitted all property procedures of ClsArea, created in the last version of ClsVolume Class, and replaced them with a Get/Set Property Procedures, instead of the Get / Let. Check the declaration Area where we have declared p_Area is declared as a ClsArea Class Object.

When an Object is declared is in this way we normally should create an instance of this object in the Class_Initialize() Procedure in the above Code. We didn't do it here because we plan to do it in the user Program and fill up its Properties with appropriate values and then pass it to the ClsVolume2 Class, before the final calculation phase to use their values.

Take note of the Set CArea() procedure. Its ByRef parameter AreaValue is declared as the ClsArea Object. It will accept the ClsArea Class Object when passed to the Property Set CArea(ByRef AreaValue as ClsArea), in object variable AreaValue, and assigns to the p_Area Property of ClsVolume2 Object.

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

In our earlier programs, we have written Property procedures for individual elements (Length, Width, Height) of an object to assign/return values To/From them.  Here, the difference is that we are passing an entire Object as a Parameter to the Set Procedure. To retrieve this Object's Property Value (say lblHeight) we must address it as CArea.dblLength. The Get/Set Property Procedure name CArea becomes the child object of the main Object when declared in the Main Program and their Property Procedures can be accessed directly through the Object Address Vol.CArea.dblLength.

A Test Program in Standard Module.

We will write a small program in the Standard Module to test our newly derived Class Object ClsVolume2.

Insert a new Standard Module in your Project. Copy and paste the following Code into the Module and Save the code:

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 us take a quick look at the VBA code above. First-line instantiates the Class ClsVolume2 with the name Vol. After the next two comment lines the Set statement with the Vol.CArea Property Procedure is called and passes the New instantiated ClsArea Object as the Parameter.

I put a Stop statement on the next line to give a pause in the Program to see how the object is being assigned to the Set CArea Object.  How to do that, We will explore that shortly?

The next four lines assign values to the ClsArea Object and to the Height property of the ClsVolume2 Object.

The next Stop creates a pause in the Program so that we can inspect the memory of how the values are kept in memory.

Next line prints the Headings in the Debug Window for the values printed on the next line.

Next line prints the values of Object Properties from memory in the Debug Window.

Run the Code to the Next Stop Statement

Let us run the Code and inspect the memory to see what happens there at each stage, where I put the Stop statement.

  1. Click somewhere in the middle of the code and press F5 to run the code and pause the program at the first Stop Statement.
  2. Select Locals Window from the View Menu to open a new window below the Code Window, to display how the ClsArea and ClsVolume2 Objects, their properties, and their member property procedures are held in memory.  A sample image of the Locals Window is given below.

    The Locals Window View.



  3. Drag other Windows' sizing handles up to reduce their height to give more space for the display of Locals Window.  Better, close Debug Window, for the time being, and use Ctrl+G to bring it back when needed later.

    We can have a graphical view of all the objects and their Properties in the Locals Window.  The first name with the plus [+] symbol shows the name of the Standard Module, from where our program is running.

    The next plus [+] symbol with the name Vol is the ClsVolume2 instantiated Object in memory.

  4. Click on the [+] symbols to expand and display the details.

    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.

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

    The expansion of  [+]CArea gives us the view of the ClsArea Object we have passed to the Set CArea() property procedure.

    The expansion of [+]p_Area gives the view of the ClsArea Property declared as Private.

    Note the p_Area Private Property, of 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 of the Locals window will show the values assigned to the Object Properties and currently no values in them.

    The Third Column shows the Data Type or Object Class Module Names.

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

Inside the CArea Object the first two lines with values 90, 10, and the last strDesc Variable with value "Bed Room" are assigned through the Get Property Procedures respectively. The p_Desc, p_Length, and p_width are values assigned through Set Property Procedures to p_Area Property of 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 ClsArea Base Class.

Usage of ClsArea and ClsVolume2 Class Objects Differently.

Next week we will try another approach with the same two objects.  If you want to try it yourself, here is the clue as to how to try it out yourself.

  1. Instantiate ClsVolume2 and ClsArea Class as two different Objects in the Standard Module Program.
  2. Assign values into both Object Properties.
  3. Assign the ClsArea instantiated Object to the CArea Object in ClsVolume2 Class Object, before printing the Values to the Debug Window.

In this example, we can achieve the same result as we did in the above example, without repeating the Get/Let Property Procedures as we did in the ClsVolume Class Module.

The Links of All the Pages in this Topic.

  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object Arrays
  3. MS-Access Base Class and Derived Objects
  4. VBA Base Class and Derived Objects-2
  5. Base Class and Derived Object Variants
  6. Ms-Access Recordset and Class Module
  7. Access Class Module and Wrapper Classes
  8. Wrapper Class Functionality Transformation
  9. Ms-Access and Collection Object Basics
  10. Ms-Access Class Module and Collection Object
  11. Table Records in Collection Object and Form
  12. Dictionary Object Basics
  13. Dictionary Object Basics-2
  14. Sorting Dictionary Object Keys and Items
  15. Display Records from Dictionary to Form
  16. Add Class Objects as Dictionary Items
  17. Add Class Objects as Dictionary Items
  18. Update Class Object Dictionary Item on Form

Share:

MS-Access Base Class and Derived Objects

Introduction.

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

  1. MS-Access Class Module and VBA.
  2. MS-Access VBA Class Object Array.

The ClsArea Class can perform as a Base Class in another Class Object, the calculation performed by the base class can be used as part of the new object’s calculations. For example, It can be part of an Object that calculates the Volume of something,

The dbl in dblLength and dblWidth Property Procedure Names of the Get / Let Procedures are simply an indication that the Class Object expects the Length and width Values in double precision numbers.  Similarly, If we change the Property Procedure Names to Quantity and the UnitPrice, then the first value multiplied by the second value gives us the Total Price of some item. 

It simply means that you can use the ClsArea Class as a base class, wherever you need the result of the first value multiplied by the second value, like Total Price * Tax Rate to calculate the tax amount or Total Price * Discount Rate to find Discount Amount and so on. 

Even though we have developed a simple Class Module it can be part of many other derived Classes.  The possibilities are open to your imagination and creativity.

Our ClsArea Class calculates the area of materials, Rooms, or similar items with Length and Width Properties only.  It doesn't calculate the area of the Triangle or Circle.  But, it can be part of a new Class Object that calculates the Volume of Rooms, Warehouses to find storage capacity.  For that, we need one more value Height of Room, Warehouse, etc.

The Volume Class: ClsVolume.

Let us create a new Class Module ClsVolume, using the ClsArea as Base Class.  Insert a Class Module and change its 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

The ClsVolume Class Object’s Code is not yet complete.  Let us examine the code line-by-line.  In the third line declared as Private Property p_Area of ClsArea Class Object.

The next line declares a Private Property with the name p_Height of data type Double. 

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

The Next two Sub-Routines (Initialize() and Terminate()) are very important here. 

The Initialize()  runs automatically and instantiates the ClsArea object in memory when we use the ClsVolume Class Object in our Standard Module program.

When we execute the Statement Set ClsVolume = Nothing in the Standard Module program, to clear the ClsVolume Object from memory, the Terminate() Sub-Routine runs and releases the memory space occupied by the ClsArea Object.

The Property Get dblHeight Procedure returns the value from p_Height Property to the calling Program.

The Property Let dblHeight Procedure validates the value passed to the NewValue parameter and assigns it to the private property p_Height.

The Public Function Volume() calculates the Volume, by calling the p_Area.Area() Function and the returned area value are multiplied by p_Height to calculate the Volume, with the expression: Volume = p_Area.Area * p_Height.  But, before executing this statement, we are performing a validation check to ensure that p_Area.Area() function returns a value greater than zero, indicating that p_Area.dblLength, p_Area.dblWidth Properties have valid values in them, and the p_Height property value is greater than zero.

Note:  Since, the p_Area Object of Class ClsArea is defined as Private Property of ClsVolume Class we have to make its Properties (strDesc, dblLength, dblWidth and Area() function) visible to the outside world for the Get / Let Operations and to return Area Value.  That means we have to define Get/Let Property Procedures for strDesc,  dblLength, dblWidth Properties, and Area() function of ClsArea Class Object in ClsVolume Class Module too. 

The Let/Get Property Procedures.

Add the following Property Get/Let Procedures and Area() function in the ClsVolume Class Module Code:

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 lines of Code.  The usage of Procedure name strDesc is simply a matter of choice, if you want to use a different name you are welcome.  But, the original Property Name we have used in the ClsArea Class is strDesc.  Using that original name here reminds us of the relationship with the original ClsArea Class Object.

In the next Get dblLength() Property Procedure, the expression to the right of the = sign p_Area.dblLength reads the dblLength value stored in the ClsArea Class Object and returns it to the calling program.

The Let Property Procedure assigns the parameter value in the NewValue variable to the p_Area.dblLength Property of ClsArea Class Object.  Here, we are not running any validation check on the received value in the NewValue parameter variable.  The Validation check will be performed within the ClsArea Class itself when we assign the value to p_Area.dblLength property.

Similarly, the Get/Let Property Procedures are added for the p_Area.dblWidth Property too,

Next, the p_Area.Area() Function is made visible through the ClsVolume Class Object to the calling program.

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

I know, what you are thinking by now: like “this is double work, it is nice if we can, some way, skip these steps of repeating ClsArea Property Procedures again in the ClsVolume Class ”.  Or say, we could have added the dblWidth Property in the ClsArea itself and run the Area() and Volume() methods from there itself, right?

The whole point here is how a Base Class Object can become part of designing another Class Object.

Remember, the whole idea of designing a Reusable Class Module Object is that the main programs, using the Class Object, will be simple and the intricacies built into the Class Object remains hidden.

Yes, we can do it more than one way, with a compact code as well.  We will explore them later, but for now, let us continue with our original plan.

The Main Program that Uses the ClsVolume Class.

Let us test our new ClsVolume Class in the main Program in 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.  Press Ctrl+G Keys to display the Debug Window if it is not already in the open state. Click somewhere in the middle of the Code and press F5 Key to run the Code.  The sample output on the Debug Window is 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.

We will run tests to see that the Base Class ClsArea Class’s input value validation check works when values are passed to it through the ClsVolume Class. We have performed some validation checks in the Area() and Volume() functions too.

Let us try them one by one:

First, we will pass a negative value to ClsArea.dblLength property through the ClsVolume Class.  It should trigger the error message and open up the Inputbox() function within the Do While…Loop to input the correct value.

1.  Replace the Value 25, in the line Vol.dblLength = 25,  with –5 and press 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 in the line, replacing –5.

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.

Since there is no input value passed to the Property the Vol.Volume() function will generate an Error saying that all the three Properties: dblLength, dblWidth, and dblHeight, should have values in them to run the Volume function.

Similarly, you may check the Vol.Area() Function’s performance too.

We can create a data printing Function and pass the ClsVolume Object as a parameter to the function and print the values 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 with less Code.

The Links of All Pages on this Topic.

Earlier Post Link References:

  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object Arrays
  3. MS-Access Base Class and Derived Objects
  4. VBA Base Class and Derived Objects-2
  5. Base Class and Derived Object Variants
  6. Ms-Access Recordset and Class Module
  7. Access Class Module and Wrapper Classes
  8. Wrapper Class Functionality Transformation
  9. Ms-Access and Collection Object Basics
  10. Ms-Access Class Module and Collection Object
  11. Table Records in Collection Object and Form
  12. Dictionary Object Basics
  13. Dictionary Object Basics-2
  14. Sorting Dictionary Object Keys and Items
  15. Display Records from Dictionary to Form
  16. Add Class Objects as Dictionary Items
  17. Update Class Object Dictionary Item on Form

Share:

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code