Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Access Class Module and Wrapper Classes

There are times that a particular Class Module needs to be instantiated more than once, to use similar set of values for a particular Application. 

For example: our ClsArea Class Module, we have designed for calculating carpet area of Rooms is a candidate for similar applications.  Assume 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 Tile also, besides the dimension values of Floor.  Since, both Floor and Tile have similar Property values as input we can use two instances of ClsArea Class Module, one instance for FLOOR area and the second instance for TILE area.  Floor-Area / Tile-Area gives the total number of tiles for a particular room. 

We can do this by creating two different instances of 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

But, what if we need to apply the above method for an array of several rooms with different sizes or color 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 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(ByVal NewValue As ClsArea) Set pFLOOR = NewValue End Property Public Property Get Tiles() As ClsArea Set Tiles = pTILES End Property Public Property Set Tiles(ByVal 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 allows retrieval and assignment of values, from and to the pFLOOR instance respectively, in the ClsTiles Class Object.

    The next Get and Set Property Procedure allows 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 Floor-Area and Tile dimension.

    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 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 may be rooms of several floors of a high-rise building then you should run the above program that number of times and note down the values, which is practically very difficult.

    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 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.
  11. As you can see from the above code that both values of Room and Tile dimensions goes into the same Class Object Array instance, side by side. 

    The above program is a demon that runs only for three set of values within the For. . . Next loop.  It can be modified with a conditional loop that runs for 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 into 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.

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

MS-Access Recordset and Class Module

Here, we will build a Class Module for data processing task, by passing a DAO.Recordset Object to our Custom Class Object.  Since it is an Object that is passing to our Custom Class we need the Set and Get Property Procedure pair to assign and retrieve the Object or it’s Property values.

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

The above table have only four fields: Desc, Qty, UnitPrice and TotalPrice.  The TotalPrice field is empty.

  • One of the task of our Class Module is to Update the TotalPrice field with the product of Qty * UnitPrice.
  • The Class Module have 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 parameter.
  1. Open your Access Database and open the VBA Window.
  2. Insert a Class Module.
  3. Change it’s 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 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 first column * second column.

The DataSort() subroutine Sorts the records in Ascending order based on the Column Number passed as parameter. 

The Sorting Column data type must be Number or  Currency or String.  Other data types are ignored.

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 have 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 parameter, and creates a Table with a new name.  Parameter is optional, if a column number is not passed as parameter then the Table will be sorted on 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 Source Table name is Table1 then the new table name will be Table1_2.

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 test the Class Object by passing any recordset.

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

Selection of Sort Column data type must be String, Numeric or Currency Type only.  Other Types are ignored.  Recordset column numbers are 0 based, that means the first column number is 0, second column is 1 and so on.

  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object Arrays
  3. MS-Access Base Class and Derived Objects
  4. Base Class and Derived Object Variants
  5. Ms-Access Recordset and Class Module
Share:

Base Class and Derived Object Variants

Last week we have tried an example as how to pass a Base Class Object, through the Set Property Procedure,  to become part of the Object in memory.  The passed object become 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 at the instantiating phase of our test program.  We have assigned  values to the passed Object Properties in later part of the program.  The next example is slightly different. 

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.  Assign values into the Base Class ClsArea Properties, before passing it to the target Class ClsVolume2 Object.  Remember the Volume2 Class have only one Property, the p_Height Property, and it’s function 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.

    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

    In the first Dim statement CA is defined as ClsArea Object and Vol as ClsVolume2 Object.  Next two statements instantiates both objects in memory.

    Next three statements  assigns values into the properties of 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 next statements before the Stop statement prints the Values from memory to the Debug Window.

    Next two Set Statements removes the Objects from memory, before ending the program.

  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 with the ClsArea Class Object.

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

1.  Insert a new Class Module and change it’s 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 declared as Public ClsArea Object. That means p_Area will appear as a Property of the ClsVolume3 Class with it's own displayable properties for direct Get/Let operations in the User Program in Standard Module. Even though ClsArea Class Object has been declared as Public Property of ClsVolume3 Class, it’s Properties are encapsulated in ClsArea Class itself.

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

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

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 into the Debug Window,
  4. and Create a Clone of the Table with Sorted data.

That is lot of actions next week.


  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object Arrays
  3. MS-Access Base Class and Derived Objects
  4. Base Class and Derived Object Variants

Share:

VBA Base Class and Derived Object-2

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 usage of 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:

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. 

Create a new Class Module and change it’s 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 = CArea.dblLength * CArea.dblWidth * Me.dblHeight End Function

‘New Get and Set Property Procedure for ClsArea Objects 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 Error Free.  If you have encountered any Error in your other VBA Programs, please track down the error, correct it and recompile your Project.  Otherwise VBA  intellisense that displays  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 pops up and displays the list, as an assistant in Coding.

We have omitted all property procedures of ClsArea, created in the last version of ClsVolume Class, and replaced with a Get/Set Property Procedures.

Take note of the Set CArea() procedure. It’s ByRef parameter AreaValue is declared as 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 directly to the p_Area Property of ClsVolume2 Object.

The Get CArea() Property procedure returns the Object or it’s Property Values 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 Object as Paraneter to the Let Procedure and to retrieve an individual element (say lblHeight) we must address, like Vol.CArea.dblLength. The Get/Set Procedure name CArea become child object of the main Object when declared in the Main Program.

We will write a small program in the Standard Module to test our new 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

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

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

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

Next four lines assigns 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 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 into the Debug Window.

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 View Menu to open a new window below the Code Window, to display as how the ClsArea and ClsVolume2 Objects, their Properties and their member property procedures  are held in memory.  Sample image of the Locals Window is given below.


  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, 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 have 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 as 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 it’s elements are accessible only through the CArea Object Property Get/Set Procedures to the outside world.

    In 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 into the Object Properties.  The program will pause at the next Stop statement.  Check the Locals Window for change of Values.

Inside the CArea the first two lines with values 90, 10 and the last strDesc with value "Bed Room" are the Get Property Procedures. The p_Desc, p_Length and p_width are values assigned through Set Property Procedures to p_Area Property of ClsVolume2 Class Object.

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.

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

  1. Instantiate ClsVolume2 and ClsArea Class as two different Object 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.

  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object Arrays
  3. MS-Access Base Class and Derived Objects
  4. Base Class and Derived Object Variants

Share:

MS-Access Base Class and Derived Objects

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 Volume of something,

The  dbl in dblLength and dblWidth Property Procedure Names for 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 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 first value multiplied by the second value, like Total Price * Tax Rate to calculate 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 area of materials, Rooms or similar items with Length and Width Properties only.  It doesn't calculate area of Triangle or Circle.  But, it can be part of a new Class Object that calculate Volume of Rooms, Warehouses to find storage capacity.  For that we need one more value Height of Room, Warehouse etc.

Let us create a new Class Module ClsVolume, using the ClsArea as Base Class.  Insert a Class Module and change it’s 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 (Me.dblHeight > 0) Then
    Volume = p_Area.Area * Me.dblHeight
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 a Private Property p_Area of ClsArea Class Object.

Next line, declares a Private Property with the name p_Height of data type Double. 

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

The Initialize()  runs and instantiate the ClsArea Object in memory,  when we instantiate 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 into the private property p_Height.

The Public Function Volume() calculates the Volume, by calling the p_Area.Area() Function and the returned area value is multiplied by dblHeight to calculate Volume, with the expression: Volume = p_Area.Area * Me.dblHeight.  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 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 it’s Properties (strDesc, dblLength, dblWidth and Area() function) visible to the outside world for 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. 

Add the following Property Get/Let Procedures and Area() function to 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 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 to the calling program.

The Let Property Procedure assigns the parameter value in 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 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 Objecct to the calling program.

The completed code of 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 that 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 compact code as well.  We will explore them later, but for now let us continue with our original plan.

Let us test our new ClsVolume Class in main Program.  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 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 

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 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 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 parameter to the function and print the values in the Debug Window.

The changed Code for both Programs are 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.


  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object Arrays
  3. MS-Access Base Class and Derived Objects
  4. Base Class and Derived Object Variants

Share:

Ms-Access VBA Class Object Arrays

Last Week we had a brief introduction of a simple Custom Class Object with only two Variables, for Length and Width Values. A Method for calculating Area of anything with Length and Width Values. Hope you understood the basics of an Ms-Access Custom Class Module.  If not, here is the link: Ms-Access Class Module and VBA. Please visit the page before continuing.

Class Module Objects saves a lot of Code in your Programs.  When you found something that you do repeatedly in Code think of a Class Module Object.  The repetitive code can be turned into a utility function too, but for Class Objects you must take a different approach.

For a simple task it may take more code to refine the functions of the Class Module Object, but your main module programs will be simple and all the complicated code developed and refined will remain hidden from others.

But for now, we have a simple Class Module in hand we will try how to create an Array of Objects to calculate Area of many items.

The sample VBA Code in the Standard Module creates an Array of five Objects of ClsArea Class and prints their Property Values and Method Result in the Debug Window.  Copy and Paste (or better if you type them in, to know each line better what they do) the following code into a Standard Module:

Public Sub ClassArray()
Dim tmpA As ClsArea
Dim CA() As ClsArea
Dim j As Long, title As String
Dim lower As Long, upper As Long

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

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


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

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

’stop

'Clear array objects from memory
For j = lower To upper
   Set CA(j) = Nothing
Next

End Sub
 

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

Sample Run of the Program is given below:

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

Let us examine the Code line by line.  In the declaration area we have declared an Object Variable tmpA of ClsArea Type. In the second line declared an empty Array CA() of ClsArea Type. The required number of elements will be Re-dimensioned later in the program. 

In the next two lines we have declared variables title of String type and j, lower and upper variables of Long Integer type.

Next, the title variable is initialized with the string ‘ClassArray’ and will be used as a Title in the InputBox() Function.  The For….Next loop is set with control variable j to run the loop five times. After the next two comment lines the Set statement instantiates (allocates memory) ClsArea Class Object in memory with the name tmpA.

Next three lines of code serves to Input values for strDesc, dblLength and dblWidth Properties of tmpA Class Object.

After the next line of comment the ClsArea Class Object (CA) is Re-dimensioned for 1 to j  times (1 to 1 times) by preserving the existing Object elements, if any (this is the first Object in the Array).  This will keep increasing, through the ReDim statement, to 1 to 2, 1 to 3, 1 to 4 and 1 to 5 by preserving the earlier object values, within the For . . . Next loop.  The Preserve key word ensures that the existing array objects are not lost.

Note: The Re-Dimension statement increase/decrease the number of object elements specified but erases the existing objects loaded into the array earlier, without the Preserve key word.

The Set statement in next line copies the tmpA Object, with it’s data into the CA(j) ClsArea Object newly created jth array element.

Next line Set tmpA = Nothing removes the temporary object from memory.

The For…Next loop repeats this action four more times to input other items into the newly instantiated temporary object tmpA, one after the other and copies the object into the CA Object Array.

Next two lines, after the comment line, finds the Object Array index range (finds the lowest and highest index numbers. 

The next Debug.Print statement prints an heading line in 14 column zones in the Debug Window.   The For . . . Next loop with the Lower and Upper bound array index number ranges runs the inner statements to access each Object from Array CA, with index number in control variable j.

The current object reference is set within With. . . End With structure, rather than repeating the Object name CA(j).strDesc, CA(j).dblLength, CA(j).dblWidth and CA(j).Area to print the Object’s Property Values and Method Area() results on the Debug Window.

Remove the comment symbol from the ‘Stop statement to create a pause in the Program on the Stop statement.  Run the code again and enter 5 item details so that you can experiment with the Array Object.   You can selectively address any of the Object Property, with the Array Index number, to edit or print any value on the Debug Window as shown below.


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

We can pass the Class Object Array as a Parameter to a program, as we did with User-Defined Data Type.

Let us create a simple Printing Subroutine, by cutting the Printing Section Code, of the main program, and placing it into the new program.

Copy the ClassArray() sub-routine Code, Paste it in the same Standard Module and change the name as ClassArray2(), as shown below.

Public Sub ClassArray2()
Dim tmpA As ClsArea
Dim CA() As ClsArea
Dim j As Long, title As String
Dim lower As Long, upper As Long

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

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

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

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

’stop

'Clear array objects from memory
For j = lower To upper
   Set CA(j) = Nothing
Next

End Sub
 

Create a new Sub-Routine as given below:
Public Sub ClassPrint(ByRef clsPrint() As ClsArea)
Dim lower As Long, upper As Long
Dim j As Long

End Sub

Highlight the Printing Section up to the ‘Stop statement in the ClassArray2() Program, cut the highlighted area of Code and paste it below the Dim statements in the ClassPrint() Program.  The modified version of both Codes are given below:

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

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

tmpA.strDesc = InputBox(Str(j) & ") Description:", title, "") tmpA.dblLength = InputBox(Str(j) & ") Enter Length:", title, 0) tmpA.dblWidth = InputBox(Str(j) & ") Enter Width:", title, 0) 'Redimension Array ReDim Preserve CA(1 To j) As ClsArea Set CA(j) = tmpA 'Copy Object to Array Set tmpA = Nothing 'Remove temporary object from memory. Next Call ClassPrint(CA) ‘Pass the Object Array to print routine 'Clear array objects from memory For j = lower To upper Set CA(j) = Nothing Next End Sub



Public Sub ClassPrint(ByRef clsPrint() As ClsArea)
Dim lower As Long, upper As Long
Dim j As Long

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

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

End Sub

Check for code changes in both programs and do corrections, wherever applicable.  Click in the middle of ClassArray2() code and press F5 to run the program. 

The Call statement in the first program passes the CA Class Object Array as ByRef Parameter to the ClassPrint() Subroutine.  The program prints the Object Properties and calls the function Area() to calculate and return the value to print.

Next week we will learn how to use our ClsArea Class module as a Base Class to create a new VBA Class Object to calculate Volume of something.

  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object Arrays
  3. MS-Access Base Class and Derived Objects
  4. Base Class and Derived Object Variants

Share:

MS-Access Class Module and VBA

Last few weeks we have learned how to use User Defined Type (UDT)  by creating a complex data structure and we know UDT’s strength or weakness, more or less by now.  If you have not gone through those Articles then you may visit them. Use the following links:

In Microsoft Access there are two Types of VBA Modules.

  1. Standard Modules
  2. Class Modules

We have worked with Class Modules in Forms and Reports of Microsoft Access. Form/Report Class Module programs are mostly Event driven (Button-Clicks, Before-Update, Form/Report Current Event and so on) small routines.

More serious data processing Programs will be written in Standard Modules. Several programs, not necessarily related, can be written in one Standard Module, to do different tasks.

Class Modules are different. They are used for building Custom Objects and one Class Module is used for only one object.

Let us create a simple Class Module from scratch and learn the basics.

  1. Start Microsoft Access and Open a database or Create a new Database.  If it is a New Database then save it to the existing Trusted Location (Folder) or add the new Location to the trusted locations list.  Click Office Button –>Access Options—> Trust Center –>Trust Center Settings.  Add the database folder into the list and click OK.
  2. Open the VBA Editing Window (Alt+F11).
  3. Click on Insert Menu and select Class Module from the list. A new Class Module is inserted.

  4. In the left panel there is a Name control with the name Class1.
  5. Change the name Class1 to ClsArea. This is the name of our Custom Object.

Note: The Class Name: ClsArea become the Object Name. That means, wherever we use this object it will be like normal variable declaration: Dim xyz As ClsArea).  We did write similar statement for User-Defined Data Type declarations.

Next, we will add three Variables (for Description, Length and Width) at the top of the module, below the Option Compare Database and  Option Explicit lines.  Type in the following lines into the Class Module.

Option Compare Database
Option Explicit

Public p_Desc as String
Public p_Length as Double
Public p_Width as Double

These variables are identified as Properties of the VBA Custom Class Object.  Save the Class Module.

Note: Our Class Module Object and lines of code will not be this simple.  It will undergo changes with several lines of Code. Better be prepared to follow them step by step, without loosing track of each stage of changes. This is a simple area-calculation Class (area = length * width), that simple. It will gradually undergo changes, so that you will know why those changes become necessary.

We will write a small program in a Standard Module to test our new Class Module. Insert a Standard Module from Insert Menu.  You may  Type or Copy and Paste the following Code into the Standard Module, overwriting the existing line in the Module:

Option Compare Database
Option Explicit

Public Function ClassTest1()
Dim oArea As ClsArea

Set oArea = New ClsArea

oArea.

Set oArea = Nothing

End Function

The Dim statement declares a Variable as we do for a normal variable, like Dim Desc as String.  But, this is not an ordinary variable we are setting a reference to our Class Module Object ClsArea.  Since, it is an object a simple Dimension statement alone is not enough because it will not allocate any memory space to store values into our locally defined Object oArea Properties.

The Set statement in the next line with the New key word required, to create an instance of an Object out of ClsArea in memory, with the Object Instance Name oArea.  We can open several instances of the same Class Object in memory in this way, if needed,  (We will learn about them in the coming weeks) so that we can store values into it’s Properties (p_Desc, p_Length, p_Width).  The p_ prefix to the variables is an indicator that the Scope of variables are Private, i.e. the Variables are not visible outside the Class Module. Any valid variable name will do.

Note:We have not yet changed it to Private. We are on the way towards that change

Immediately after the key word Set the local Object name (you can pick a suitable name you prefer but it should conform to the normal variable name rules) followed by an equal sign and the key word New and the Class Module Name (ClsArea) to create an instance of the clsArea Object in memory with all it's Properties (Variables). 

There is a shortcut for this two line code. Actions of both these lines of code can be achieved with one statement as shown below :

Dim oArea As ClsArea


Set oArea = New ClsArea

Dim oArea As New ClsArea

When you type the next line oArea followed by a dot (.) separator the following display will appear to show the list of available Custom Object Properties to select from.

If it doesn't appear then go to the Options dialog from the Tools menu box and put a check mark in Auto List Members in the Editor Tab.

Before exiting the Function the last statement should be Set oArea = Nothing. This statement explicitly releases the memory occupied by the instance of the Custom Object, so that more memory is available for other programs.  This is a responsible cleaning up operation by our program.

Whatever we do with the instantiated Custom Object should be coded between the first and last Set statements.

The completed Class Testing Program Code is given below:

Option Compare Database
Option Explicit

Public Function ClassTest1()
Dim oArea As ClsArea

Set oArea = New ClsArea

oArea.p_Desc = "Carpet"
oArea.p_Length = 25
oArea.p_Width = 15

Debug.Print "Description", "Length", "Width"

Debug.Print oArea.p_Desc, oArea.p_Length, oArea.p_Width

Set oArea = Nothing

End Function

Click somewhere in the middle of the Code and press F5 to run the program. Run of the program  is given below for reference.

Description   Length        Width
Carpet         25            15 

Our simple Class Module Object have few drawbacks and we are going to rectify them.

The first one is that we have declared all Variables (or Properties) with Public Scope.  Because of that they are visible to other VBA programs and may get it’s value changed directly.  The second issue is that it will accept any invalid values, like negative or zero values, which is not suitable for our Class Object.  We have to incorporate some validation checks before accepting the values into the variables.

The first problem we can solve easily by changing the variable declarations from Public to Private.  When we do that we should have some indirect method to store and retrieve values from the Private Variables. That is the purpose of the Get and Let Property Procedures.  Let us make these changes in the Class Module.

Open the Class Module ClsArea. Change the word Public to Private for all three variables.

Select Procedure from Insert Menu, type strDesc in the Name text control, select Property in the Type Option Group and Public in the Scope option group.  Click OK to insert the Property Procedures for the Private p_Desc Variable (Property).

Option Compare Database
Option Explicit

Private p_Desc As String
Private p_Length As Double
Private p_Width As Double

Public Property Get strDesc() As String
  strDesc = p_Desc 'return the value from p_Desc
End Property

Public Property Let strDesc(ByVal strNewValue As String)
  p_Desc = strNewValue ‘store the value into p_Desc
End Property

Both the Get Procedure and Let Procedure are declared as Public. Both Procedure names are same strDesc.  By default the returned data type is Variant in the Get Procedure and the Parameter data type is also inserted as Variant in the Let Procedure.  These we can change to specific type as needed, which we did and changed to String type.  The first three letters str in strDesc gives the User a hint that the Property expect a String data type value. Change the Let Property Procedure Parameter Variable Name vNewValue to strNewValue

When we insert the Property Procedure  they are always inserted with Get and Let Procedure pairs for a Variable.

Now, take a look closely the expression we have written within the Get Procedure.  Left side of the = Sign the name of the Get Procedure strDesc acts as a variable to return the value copied from the Private Variable p_Desc  to the calling program.

The Let Procedure strDesc accepts String value in parameter Variable strNewValue.  The input value is transferred  into our private variable p_Desc.

The point to note here is that there is no direct access to our private variable p_Desc to the outside world.  Transportation of values From/To the Variable (Property) p_Desc is always routed through the Get/Let Property Procedures only and subject to Validation checks(not yet implemented),  We will introduce validation checks on values input (Let Procedure) into the Property later.

The Get/Let Procedures are executed automatically depending on what we do with the Object property in an expression in VBA Programs. 

The Get Procedure is executed when we use the Property Name in an expression in the following manner:

‘ Reads the value from p_Desc to Print

Debug.Print oArea.strDesc

OR

‘ Reads the value from p_Desc and assigns it to the variable X

X = oArea.strDesc

The Let Property Procedure is run when we try to assign a value into the Property Name. Check the example expression in our Test Program below:

oArea.strDesc = “Carpet”

In earlier BASIC Language books you can see the usage of the key word LET.

LET X = 25 ‘ LET is optional

Since, it was optional the statement works without it and stopped using it at all.

Here, if you are only reading some value from a Variable and not storing anything into it directly then you can omit the Let Procedure and use only the Get Procedure.

This rule applies to Let procedure also.  You may use only the Let Procedure, if you are assigning some value into a Private Variable but not reading anything back from the same variable then omit Get Procedure.

The Get and Let Procedures will run one after the other, if our expression is something like the following:

oArea.strDesc = oArea.strDesc & “ – King Size.”

In the above expression we will Get the existing value from the Private Variable p_Desc and modify the description and store it back into the same variable.  In short in an expression if you use the Property name to the right of the equal sign (=) the Get Procedure is called and Let Procedure is run when the object property procedure name appears to the left of the equal (=) sign.

Insert two set of Property Procedures for the variables p_Length and p_Width. When you give the Procedure names in the Name control give the name dblLength and dblWidth to give a hint to the User that these Properties expects Double Precision numbers as Input.

The completed code so far with dblLength and dblWidth Property Procedures is given below for reference and to update your code.

Option Compare Database
Option Explicit

Private p_Desc As String
Private p_Length As Double
Private p_Width As Double

Public Property Get strDesc() As String
  strDesc = p_Desc 'copy the value from p_Desc
End Property

Public Property Let strDesc(ByVal strNewValue As String)
  p_Desc = strNewValue
End Property

Public Property Get dblLength() As Double
  dblLength = p_Length
End Property

Public Property Let dblLength(ByVal dblNewValue As Double)
  p_Length = dblNewValue
End Property

Public Property Get dblWidth() As Double
  dblWidth = p_Width
End Property

Public Property Let dblWidth(ByVal dblNewValue As Double)
  p_Width = dblNewValue
End Property

If you are through completing the above code then let us make changes to our test program, to reflect the changes we made here.  The modified sample code is given below.

Option Compare Database
Option Explicit

Public Function ClassTest1()
Dim oArea As ClsArea

Set oArea = New ClsArea

‘Property Let procedures called here

oArea.strDesc = "Carpet"
oArea.dblLength = 25
oArea.dblWidth = 15

Debug.Print "Description", "Length", "Width"

‘Property Get Procedures called here to print
Debug.Print oArea.strDesc, oArea.dblLength, oArea.dblWidth

Set oArea = Nothing

End Function

When you enter a dot (.) immediately after the object name oArea (oArea.) the list of Property Procedure names are displayed by the VBA intellisense and you may select the required one from the list without typing it manually.

The purpose of this Class Object is to calculate Area of something, like Area of Room, Carpet, Floor Tile or whatever material, which have values of Length and Width.  That means we need a Public Function to calculate Area of whatever item’s Length, Width and Description Values entered into the Class Object.

Here is the Code for the Public Function:

Public Function Area() As Double
   Area = Me.dblLength * Me.dblWidth
End Function

You may insert this Function from the Insert Menu by entering Area in the Name Control, selecting Function from the Type option group and Public as Scope into the ClsArea Class Module.  Complete the Function by entering the line in the middle.

We can directly address p_Length and p_Width variables (because the Function Area() is part of the Class Module) in the expression for calculating the Area.  But, we are taking the proper route and calls Get Procedures dblLength and dblWidth for calculation.  You might have noticed the reference Me.  used to qualify the dblLength, dblWidth Get Procedures, like we used to write in Form/Report Class Modules, to refer to the current Object in memory and it’s Properties.  As I stated earlier our Custom Class Object can have several Object instances opened in memory at the same time and Me key word refers to the current instance that the Function Area() belongs to.

Modify our Test Function ClassTest1() to incorporate the Area() function output as below:

Option Compare Database
Option Explicit

Public Function ClassTest1()
Dim oArea As ClsArea

Set oArea = New ClsArea

oArea.strDesc = "Carpet"
oArea.dblLength = 25
oArea.dblWidth = 15

Debug.Print "Description", "Length", "Width", "Area"
Debug.Print oArea.strDesc, oArea.dblLength, oArea.dblWidth, oArea.Area

Set oArea = Nothing

End Function

The change is only in the Debug.Print statements.  Run the code and check the Debug Window for result.

There are two Event Procedures required in the Custom Class Modules: Class_Initialize() and Class_Terminate()

The Class_Initialize() program is executed automatically when we instantiate an Object with the New Key Word.  This program can be used to set default values into variables or Instantiate other  Objects in memory.  One Class Object may use other Classes as child object(s) and needs to be instantiated.  This aspect we will explore further and learn how to do it later.

The Class_Terminate() program runs when we try to clear the object from memory when the Nothing key word is run in the statement Set oArea = Nothing.  When the program that uses the Class Object ends, the Instance of the Object in memory is removed by default.  But it is a good programming practice that we use Set oArea = Nothing  statement as last executable statement in our programs to clear the object from memory.

We will add the above programs in our Class Module.  Add the following Code at the end of your Class Module:

Private Sub Class_Initialize()
   p_Length = 0
   p_Width = 0

   'MsgBox "Initialize.", vbInformation, "Class_Initialize()"
End Sub

Private Sub Class_Terminate()
   'MsgBox "Terminate.", vbInformation, "Class_Terminate()"
End Sub

If you would like to test these two sub-routines then remove the Comment symbol and make the MsgBox active.  Run your test program one more time.  You will find the Initialize message appears in the beginning (Click OK to continue)  and Terminate message appears at the end of the Test Program.

I know what you are thinking by now, like “so much code for multiplying two variables together”.  It is true in that perspective, but it is very likely that we have written code for similar problem solving issues repeatedly every time, duplicating code for validation checks and for other logical error safeguards.

Here, we are  not writing an ordinary Program but developing a Custom Object that can be used many times, or can be part of other Objects, wherever we need it without worrying about how it works, in the user point of view.  Microsoft Access have many built-in Objects/Functions that we use all the time without worrying about how it works, by setting their Properties or Parameters and get the work done.

We have one more issue to take care of, the validation checks on values input into the dblNewValue  Parameter in the Let Property Procedures of dblLength() and dblWidth(), to ensure that valid values are assigned to Object Property p_Length and p_Width

Negative or Zero Values entered are considered invalid and we have to take precautions to see that the correct value is entered by the User.  The modified Let Property Procedure Code segments are given below.  Make changes in your code accordingly.

Public Property Let dblLength(ByVal dblNewValue As Double)
   Do While dblNewValue <= 0
      dblNewValue = InputBox("Negative/0 Values Invalid:", "dblLength()", 0)
   Loop

   p_Length = dblNewValue
End Property


Public Property Let dblWidth(ByVal dblNewValue As Double)
   Do While dblNewValue <= 0
       dblNewValue = InputBox("Negative/0 Values Invalid:", "dblwidth()", 0)
   Loop

   p_Width = dblNewValue
End Property

The Do While. . . Loop runs repeatedly till a valid value (greater than 0) is entered into dblNewValue by the User

We need one more validation check in the Area() Function.  If the user calls the Area() function without entering valid values for Length and Width first, then the User must be informed about it.  We will check whether p_Length and p_Width variables have valid values before running the expression for area calculation.  Here is the Code:

Public Function Area() As Double
  If (Me.dblLength > 0) And (Me.dblWidth > 0) Then
        Area = Me.dblLength * Me.dblWidth
  Else
        Area = 0
        MsgBox "Error: Length/Width Value(s) Invalid., Program aborted."
  End If
End Function

The fully completed Code of our Class Module ClsArea is given below:

Option Compare Database
Option Explicit

Private p_Desc As String
Private p_Length As Double
Private p_Width As Double

Public Property Get strDesc() As String
    strDesc = p_Desc 'copy the value from p_Desc
End Property

Public Property Let strDesc(ByVal strNewValue As String)
    p_Desc = strNewValue
End Property

Public Property Get dblLength() As Double
   dblLength = p_Length
End Property

Public Property Let dblLength(ByVal dblNewValue As Double)
    Do While dblNewValue <= 0
       dblNewValue = InputBox("Negative/0 Values Invalid:", "dblLength()", 0)
    Loop

    p_Length = dblNewValue
End Property

Public Property Get dblWidth() As Double
   dblWidth = p_Width
End Property

Public Property Let dblWidth(ByVal dblNewValue As Double)
    Do While dblNewValue <= 0
       dblNewValue = InputBox("Negative/0 Values Invalid:", "dblwidth()", 0)
    Loop

    p_Width = dblNewValue
End Property

Public Function Area() As Double

   If (Me.dblLength > 0) And (Me.dblWidth > 0) Then
       Area = Me.dblLength * Me.dblWidth
   Else
       Area = 0
       MsgBox "Error: Length/Width Value(s) Invalid., Program aborted."
   End If

End Function

Private Sub Class_Initialize()
   p_Length = 0
   p_Width = 0
 'MsgBox "Initialize.", vbInformation, "Class_Initialize()"
End Sub

Private Sub Class_Terminate()
   'MsgBox "Terminate.", vbInformation, "Class_Terminate()"
End Sub

You may test our Custom Class Object by entering negative or 0 values as input to dblLength, dblWidth Properties. 

In the Test Program Comment out the lines (oArea.dblLength=25 and oArea.dblWidth=15) to test the Area() function. It should display the Error message we have written within the function.

Our Area calculation Class Module is now considered complete and we have tested and found it is working correctly. You may test it further for any logical errors that I have overlooked. If you come across anything that I didn't anticipate, please share it with me.

We have tested the Class Object for only one item.  We need to calculate the area of several items (say area of 5 bed-rooms or 10 different sized Carpets and so on.  We are told that once an object is developed we can instantiate it several times in memory assigning different set of values into each instance of the Object and can work with them.

Besides that, this Object can be used as part of other Objects we develop with lesser code, because part of our new Class Object is already developed in ClsArea Class Module.

Next week we will learn how to create an Array of Custom Objects to calculate area of several items.

  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object Arrays
  3. MS-Access Base Class and Derived Objects
  4. Base Class and Derived Object Variants

Share:

User-Defined Data Type-3

Last week we have learned how to define a User-Defined Data Type and it’s usage in programs.  If you have landed on this page, you may go through the earlier Post: User-Defined Data Type-2, before proceeding further. 

User defined type declaration is done within the [Private/Public ]Type…End Type structure.  Immediately after the word Type we give a name to the data type.  Actual variables which holds the data values are defined as individual elements, mostly with built-in variable types: String, Integer, Long Integer, Double or Variant.

Besides built-in variables we can use other User-Defined Type, consisting of several elements of it’s own, as child element within a User-Defined Type.  That is what we are going to try out here.

First, we will declare some User Defined Types separately, for different category of information, like Home Address, Qualification, Experience,  for Employee Record.

We will build an employee record with the above logical group of information defined separately with it’s own data elements and place them along with the elements of Type Employee.

The layout of the group of information of Employees, defined separately, before they are organized under the User Defined Type Employee is shown below.

1.Qualification
Desc1
Desc2
2.Experience
Desc1
Desc2
3.Address and Date of Birth
Address1
Address2
City
State
PIN
BirthDate
4.Employee Details
Employee Name
Designation
Join Date
Monthly Performance Score(1 to 12)
Salary

First, let us declare the Data Types:

Option Compare Database

Public Type Qualification
    Q_Desc1 As String
    Q_Desc2 As String
End Type

Public Type Experience
     X_Desc1 As String
     X_Desc2 As String
End Type

Public Type BioData
    Address1 As String
    Address2 As String
    City As String
    State As String
    PIN As String
    BirthDate As Date
End Type

Public Type Employee
        E_Name As String * 45
        E_Desig As String * 25
        E_JoinDate As Date
        E_PerfScore(1 To 12) As Double
        E_Salary As Double
End Type

The Qualification and Experience Types are defined as child elements inside the BioData Type.  After the change the BioData Type looks like the Code given below.

Public Type BioData Address1 As String Address2 As String City As String State As String PIN As String BirthDate As Date Q_Desc As Qualification X_Exp As Experience End Type

The Qualification Data Type have two elements of String Data Type.  The Experience Type also have two elements of String data type.

The BioData type have Address details and Date of Birth as elements. Besides that Qualification and Experience Data Types are inserted into the BioData Type as it’s child elements.

Now, we will define the Bio-Data Data Type (along with Qualification and Experience) as child element of Employee Data Type. 

The Employee Data Type with BioData Type as child element.

Public Type Employee E_Name As String * 45 E_Desig As String * 25 E_JoinDate As Date E_PerfScore(1 To 12) As Double E_Salary As Double B_BioData As BioData

End Type

Got the idea how all these fits together as Employee Record.

The Employee type have four elements. The E_Name element is String type and it’s length is limited to 45 characters long.  The next element is to store the Designation of the employee and can store up to 25 characters.  The String length specification is purely arbitrary, you may use it as it is or simply say E_Name As String.  Join Date is Date type. 

Next item is an array with 12 elements to store the employee’s monthly performance evaluation score (on a scale of 10) recorded by the management.

We have declared Qualification and Experience data types first, before inserting them as elements of BioData Type.

BioData Data Type is declared above Employee type before inserting it into Employee data type.

Ensure that you are not placing a Type within itself.

Now, that we are all set to try out this complex data structure and the first question comes in one’s mind is that how to address each element to assign values into them.

We will write a small program to try out Employee Data Type and assign values into each element of the nested complex data structure.  It is not that complicated, as it sounds.  If you find it difficult to follow then try out simpler examples defined on your own level of understanding.

The program code is given below and look closely at each element as how it is addressed  to assign values into it.

Public Function EmplTypeTest() Dim Emp As Employee Emp.E_Name = "John" Emp.E_Desig = "Manager" Emp.E_JoinDate = #01/01/2018# Emp.E_PerfScore(Month(Date) - 1) = 4.5 Emp.E_Salary = 40000 'BioData Emp.B_BioData.Address1 = "115/8" Emp.B_BioData.Address2 = "Olencrest," Emp.B_BioData.City = "Columbus" Emp.B_BioData.State = "Ohio" Emp.B_BioData.PIN = "43536" Emp.B_BioData.BirthDate = #9/29/1979# 'Qualifications Emp.B_BioData.Q_Desc.Q_Desc1 = "Degree in Computer Science" Emp.B_BioData.Q_Desc.Q_Desc2 = "PG Degree in Computer Science" 'Experience Emp.B_BioData.X_Exp.X_Desc1 = "From Jan-2010 onwards Working as Project Manager, with XYZ Company." Emp.B_BioData.X_Exp.X_Desc2 = "From Mar-2005 to Dec-2009 worked as Team Leader with ABC Enterprise."

Call ListEmp(Emp) End Function

As you can see in the above code we have addressed each element with fully qualified names showing it's hierarchical position in the Employee Data Structure. The same code is given below using full object references with With XXXX....End With statements, where XXXX part represents the object hierarchical names.

Public Function EmplTypeTest0()
Dim Emp As Employee

With Emp
  .E_Name = "John"
  .E_Desig = "Manager"
  .E_JoinDate = #01/01/2018#
  .E_PerfScore(Month(Date) - 1) = 4.5
  .E_Salary = 40000
End With

        'BioData
With Emp.B_BioData
        .Address1 = "115/8"
        .Address2 = "Olencrest,"
        .City = "Columbus"
        .State = "Ohio"
        .PIN = "43536"
        .BirthDate = #9/29/1979#
End With

       'Qualifications
With Emp.B_BioData.Q_Desc
            .Q_Desc1 = "Degree in Computer Science"
            .Q_Desc2 = "PG Degree in Computer Science"
End With

        'Experience
With Emp.B_BioData.X_Exp
            .X_Desc1 = "From Jan-2010 onwards Working as Project Manager, with XYZ Company."
            .X_Desc2 = "From Mar-2005 to Dec-2009 worked as Team Leader with ABC Enterprise."
End With

    Call ListEmp(Emp)

End Function

Check the With...statement how the Object references are given in proper order of it’s placement to reach it's element variable. The above Code is again modified with With XXXX...End With statements in a nested form only using the Object name nearest to the data variable.

Public Function EmplTypeTestA() Dim Emp As Employee With Emp .E_Name = "John" .E_Desig = "Manager" .E_JoinDate = #01/01/2018# .E_PerfScore(Month(Date) - 1) = 4.5 .E_Salary = 40000 'B_BioData With Emp.B_BioData .Address1 = "115/8" .Address2 = "Olencrest," .City = "Columbus" .State = "Ohio" .PIN = "43536" .BirthDate = #9/29/1979# 'Qualifications With .Q_Desc .Q_Desc1 = "Degree in Computer Science" .Q_Desc2 = "PG Degree in Computer Science" End With 'Experience With .X_Exp .X_Desc1 = "From Jan-2010 onwards Working as Project Manager, with XYZ Company." .X_Desc2 = "From Mar-2005 to Dec-2009 worked as Team Leader with ABC Enterprise." End With

End With ‘ Emp.B_BioData End With ‘ Emp Call ListEmp(Emp) End Function

You can use any of the above three programs or all of them one by one to try out the following Printing program to list the data on to the Debug Window.

Public Function ListEmp(ByRef EmpList As Employee)
With EmpList
    Debug.Print "Name: ", , .E_Name
    Debug.Print "Designation: ", , .E_Desig
    Debug.Print "Join Date: ", , .E_JoinDate
    Debug.Print "Performance Score July: ", .E_PerfScore(8)
    Debug.Print "Salary: ", , .E_Salary
    
    Debug.Print "Address1: ", , .B_BioData.Address1
    Debug.Print "Address2: ", , .B_BioData.Address2
    Debug.Print "City: ", , .B_BioData.City
    Debug.Print "State: ", , .B_BioData.State
    Debug.Print "PIN: ", , .B_BioData.PIN
    
    Debug.Print "Qualification1: ", .B_BioData.Q_Desc.Q_Desc1
    Debug.Print "Qualification2: ", .B_BioData.Q_Desc.Q_Desc2
    
    Debug.Print "Experience1: ", , .B_BioData.X_Exp.X_Desc1
    Debug.Print "Experience2: ", , .B_BioData.X_Exp.X_Desc2
    
End With

End Function

Sample Output displayed on the Debug Window is given below:

Name:                       John                                         
Designation:                Manager                  
Join Date:                  01-01-2018 
Performance Score August:    4.5 
Salary:                      40000
Address1:                   115/8
Address2:                   Olencrest,
City:                       Columbus
State:                      Ohio
PIN:                        43536
Qualification1:             Degree in Computer Science
Qualification2:             PG Degree in Computer Science
Experience1:                From Jan-2010 onwards Working as Project Manager, with XYZ Company.
Experience2:                From Mar-2005 to Dec-2009 worked as Team Leader with ABC Enterprise.

If you would like to try out an array example, then copy paste the following two programs into a Standard Module and run the first code. 

Public Function EmplTypeTestB()
Dim Emp(1 To 3) As Employee
Dim j As Integer, strlabel As String

For j = 1 To 3

With Emp(j)
strlabel = "( " & j & " )"
    .E_Name = InputBox(strlabel & "Name:")
    .E_Desig = InputBox(strlabel & "Designation:")
    .E_JoinDate = InputBox(strlabel & "Join Date:")
    .E_PerfScore(Month(Date) - 1) = InputBox(strlabel & "Performance Score:")
    .E_Salary = InputBox(strlabel & "Salary:")
    
   'B_BioData
    With Emp(j).B_BioData
        .Address1 = InputBox(strlabel & "Address1:")
        .Address2 = InputBox(strlabel & "Address2:")
        .City = InputBox(strlabel & "City:")
        .State = InputBox(strlabel & "State:")
        .PIN = InputBox(strlabel & "PIN:")
        .BirthDate = InputBox(strlabel & "Birth Date:")
       
       'Qualifications
        With .Q_Desc
            .Q_Desc1 = InputBox(strlabel & "Qualification-1:")
            .Q_Desc2 = InputBox(strlabel & "Qualification-2:")
        End With
    
        'Experience
        With .X_Exp
            .X_Desc1 = InputBox(strlabel & "Experience-1:")
            .X_Desc2 = InputBox(strlabel & "Experience-2:")
        End With
    End With
End With
Next

    
    Call ListEmp2(Emp)

End Function

The Inputbox() Function will allow you to type details of three employees directly from keyboard, based on the prompt displayed asking for specific values.  In the last statement Call ListEmp2(Emp) will run the following code by passing the employee records array  and prints the output into the Debug Window. Keep the Debug Window Open (Ctrl+G).

Public Function ListEmp2(ByRef EmpList() As Employee)
Dim j As Integer, strlabel As String
Dim lower As Integer
Dim upper As Integer

lower = LBound(EmpList)
upper = UBound(EmpList)

For j = lower To upper
With EmpList(j)
Debug.Print
    Debug.Print "=== Employee: " & .E_Name & "  Listing ==="
    Debug.Print "Name: ", , .E_Name
    Debug.Print "Designation: ", , .E_Desig
    Debug.Print "Join Date: ", , .E_JoinDate
    Debug.Print "Performance Score " & MonthName(Month(Date) - 1) & ": ", .E_PerfScore(8)
    Debug.Print "Salary: ", , .E_Salary
    
    Debug.Print "Address1: ", , .B_BioData.Address1
    Debug.Print "Address2: ", , .B_BioData.Address2
    Debug.Print "City: ", , .B_BioData.City
    Debug.Print "State: ", , .B_BioData.State
    Debug.Print "PIN: ", , .B_BioData.PIN
    
    Debug.Print "Qualification1: ", .B_BioData.Q_Desc.Q_Desc1
    Debug.Print "Qualification2: ", .B_BioData.Q_Desc.Q_Desc2
    
    Debug.Print "Experience1: ", , .B_BioData.X_Exp.X_Desc1
    Debug.Print "Experience2: ", , .B_BioData.X_Exp.X_Desc2
    
End With

Next
End Function

I hope you will try out the User Defined Type in your projects and explore it’s strength and weaknesses further.

The main problem with the User Defined Type is that it doesn’t have any feature to validate the data passed to it before accepting in it’s elements.  For example, if any future date is entered into the Date of Birth element there is no built-in code to validate and inform the user that the date entered is not valid to accept in the field.  Like-wise if a negative value is entered into the Salary field it simply accepts it.  Wherever the validation check is required we have to write separate code to do that, whenever we use the User Defined Type (UDT) all the time.

A better option  is to use Class Modules. We can define individual elements in Class Module, run validation checks on each item, wherever necessary,  before accepting the data into the element.  Write Functions or Subroutines to operate on the data for common tasks and call the Functions from user programs.  All these remains as part of the package and don’t have to write separate code for it.

We will learn how to use Class Modules to define structured data and use it in programs.


Share:

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads

blog.feedspot.com Microsoft Access Blogs

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

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

Featured Post

Access Class Module and Wrapper Classes

There are times that a particular Class Module needs to be instantiated more than once, to use similar set of values for a particular Applic...

Labels

Blog Archive

Recent Posts