Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

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:

No comments:

Post a Comment

Comments subject to moderation before publishing.

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