Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Ms-Access and Collection Object Basics

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

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

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

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

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

'Print the items in debug window GoSub Listing

C.Remove 3 'Remove 3rd item

GoSub Listing

Set C = Nothing

Exit Sub

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

End Sub

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

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

An image of the Collection Object instance displaying it's methods list below:

  • The Add method adds value or object as Collection member to the Collection Object and the Remove method is for deleting an item from the Collection.
  • The Item method combined with an item number as Index we can retrieve a particular item from Collection. The Count method gives the total items count in the Collection.
  • In the above example we are not using any Object type items but few simple mixed data type values: integer, String and Double Precision Number, as Collection members.
  • By using the Add method of C Collection Object we have added five items into the collection.  First two items are integer numbers, next two items are String data type and the last item is a double precision number. 
  • The Collection Object instance name C and the Add method is joined with a dot separator, followed by a space then the actual value to be added to the Collection.
  • The Add method have four parameters, check the image given below:

  • The parameters: Item, [Key], [Before], [After].  The first parameter Item is mandatory; the Value to be added to the Collection.
  • The next three Parameters are optional. 
  • When any of the optional parameter is used with the .Add method other parameter places must be skipped with comma separator, except for the right side items.  By  explicit usage of parameter names with their values enables us to give the parameter values in any order.  We will learn their usage in another example VBA Code.

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

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

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

Next, we are calling a printing sub-routine, within the program, that prints the Collection members to the debug window.  The sub-routine have a For . . . Next Loop to run for 1 to the number of items ( C.Count) in the Collection.  The value in the j control variable is used as index number parameter to the Item() method of the Collection Object, to retrieve the value and print it in the debug window.

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

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

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

5 15 iPhone Disk 2TB 35.75

5 15 Disk 2TB 35.75

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

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

OR

C.Add 2,,1

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

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

OR

C.Add 20,,,3

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

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

Public Sub CollTest2() Dim C As Collection Dim j As Integer Set C = New Collection C.Add 5 C.Add 15 C.Add "iPhone" C.Add "Disk 2TB" C.Add 35.75 GoSub Listing C.Add 2, Before:=1 'Insert the item before the first item C.Add 20, After:=3 'Insert the item after first 3 items GoSub Listing

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

Next Return End Sub

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

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

The second example without the use of Parameter Names and giving the parameter value in it’s proper position.

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

Set C = New Collection

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

GoSub Listing

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

GoSub Listing

Set C = Nothing

Exit Sub

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

End Sub

Sample printout on the Debug Window is shown below:

5 
 15 
iPhone
Disk 2TB
 35.75 

 2 
 5 
 15 
 20 
iPhone
Disk 2TB
 35.75 

In all our printing examples we have used the Item's Index number to retrieve the values to print them in the Debug Window.  When there are several items in the collection it is very difficult to keep track of particular Item's Index number to retrieve the specific value we want.  To overcome this problem we can store an easily memorable Key Value, along with the Item Value, like the First Name of  a Contact in Address Book, to retrieve item value randomly from the Address Book Collection members.

Let us write a new program to demonstrate the usage of Item Keys with Values in Collection.

Public Sub CollTest3() Dim C As Collection Dim strKey As String Dim strGet As String Set C = New Collection C.Add 5, Key:="FIVE" C.Add 15, Key:="FIFTEEN" C.Add "iPhone", "7+" 'you can omit the KEY param name C.Add "Disk 2TB", "DISK" ' Add method's 2nd Parameter is KEY C.Add 35.75, "999" 'add value 2 with Key "TWO" before the first item. 'this item will be the first item in the collection 'parameter names not in proper order – valid

C.Add Item:=2, Before:=1, Key:="TWO" 'add value 7 with Key "SEVEN" as third item in the collection 'parameter names not in proper order – valid

C.Add Key:="SEVEN", Item:=7, After:=2 'Retrieve value using it's KEY from collection strKey = "" Do While strKey = "" strKey = InputBox("Value Key: " & vbCr & vbCr & "Q - Quit", "Enter Key", "") Select Case strKey Case "Q" Exit Do Case "TWO", "FIVE", "SEVEN", "FIFTEEN", "7+", "DISK", "999" strGet = C(strKey) Case Else strGet = " Not Found!" End Select MsgBox "Key:<<" & strKey & ">> Value: " & strGet strKey = "" Loop Set C = Nothing End Sub

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

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

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


  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 Class Module and Collection Object
  10. Table Records in Collection Object and Form
  11. Dictionary Object Basics
  12. Dictionary Object Basics-2
  13. Sorting Dictionary Object Keys and Items
  14. Display Records from Dictionary to Form
  15. Add Class Objects as Dictionary Items
  16. Update Class Object Dictionary Item on Form
Share:

Wrapper Class Functionality Transformation

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

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

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

We should not forget that the ClsVolume2 Class is a Derived Class, built using ClsArea as Base Class.

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

Option Compare Database
Option Explicit

Private p_Height As Double
Private p_Area As ClsArea

Public Property Get dblHeight() As Double
    dblHeight = p_Height
End Property

Public Property Let dblHeight(ByVal dblNewValue As Double)
    p_Height = dblNewValue
End Property

Public Function Volume() As Double
    Volume = CArea.dblLength * CArea.dblWidth * Me.dblHeight
End Function

Public Property Get CArea() As ClsArea
   Set CArea = p_Area
End Property

Public Property Set CArea(ByRef AreaValue As ClsArea)
  Set p_Area = AreaValue
End Property

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

Private Sub Class_Terminate()
Set p_Area = Nothing
End Sub

The only problem that prevents us from using ClsVolume2 Class directly for the Sales data entry  is that the Property Procedure names dblLength, dblWidth, dblHeight doesn’t match for the Sales property values Quantity, UnitPrice, Discount Percentage.   The numeric data types of ClsVolume2 Class are all double precision numbers and they are suitable for our Sales Class and can be used without data type change.  The  public functions Area() and Volume()  names are also not suitable but their calculations formula can be used for Sales calculations without change. 

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

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

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

  1. The easiest way is to make a copy of the ClsVolume2 Class and save it in a new class Module with the name ClsSales.  Make appropriate changes to the Property Procedure and public Function names suitable for sales values and calculations.  Add more functions, if required, in the new class module.
  2. Create a Wrapper Class using ClsVolume2 as Base Class and create suitable property procedure and public function name changes, masking the Base Class’s Property Procedures and Function names.  Create new Functions in the Wrapper Class, if necessary.

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

  1. Open your Database and display the VBA Editing Window (Alt+F11).
  2. Select Class Module from Insert Menu, to insert a new Class Module.
  3. Change the Class Module’s Name property Value from Class1 to ClsSales.
  4. Copy and Paste the following VBA Code into the Module and Save the Code:
    Option Compare Database
    Option Explicit
    
    Private m_Sales As ClsVolume2
    
    Private Sub Class_Initialize()
        'instantiate the Base Class in Memory
        Set m_Sales = New ClsVolume2
    End Sub
    
    Private Sub Class_Terminate()
        'Clear the Base Class from Memory
        Set m_Sales = Nothing
    End Sub
    
    Public Property Get Description() As String
      Description = m_Sales.CArea.strDesc 'Get from Base Class
    End Property
    
    Public Property Let Description(ByVal strValue As String)
      m_Sales.CArea.strDesc = strValue ' Assign to Base Class
    End Property
    
    Public Property Get Quantity() As Double
    Quantity = m_Sales.CArea.dblLength
    End Property
    
    Public Property Let Quantity(ByVal dblValue As Double)
       If dblValue > 0 Then
         m_Sales.CArea.dblLength = dblValue ' Assign to clsArea, Base Class of ClsVolume2
       Else
         MsgBox "Quantity: " & dblValue & " Invalid.", vbExclamation, "ClsSales"
         Do While m_Sales.CArea.dblLength <= 0
              m_Sales.CArea.dblLength = InputBox("Quantity:, Valid Value >0")
         Loop
       End If
    End Property
    
    Public Property Get UnitPrice() As Double
       UnitPrice = m_Sales.CArea.dblWidth
    End Property
    
    Public Property Let UnitPrice(ByVal dblValue As Double)
       If dblValue > 0 Then
          m_Sales.CArea.dblWidth = dblValue ' Assign to clsArea, Base Class of ClsVolume2
       Else
         MsgBox "UnitPrice: " & dblValue & " Invalid.", vbExclamation, "ClsSales"
           Do While m_Sales.CArea.dblWidth <= 0
              m_Sales.CArea.dblWidth = InputBox("UnitPrice:, Valid Value >0")
           Loop
       End If
    End Property
    
    Public Property Get DiscountPercent() As Double
       DiscountPercent = m_Sales.dblHeight
    End Property
    
    Public Property Let DiscountPercent(ByVal dblValue As Double)
    ' Assign to Class .dblHeight of ClsVolume2
    Select Case dblValue
        Case Is <= 0
           MsgBox "Discount % -ve Value" & dblValue & " Invalid!", vbExclamation, "ClsSales"
           Do While m_Sales.dblHeight <= 0
              m_Sales.dblHeight = InputBox("Discount %, Valid Value >0")
           Loop
        Case Is >= 1
           m_Sales.dblHeight = dblValue / 100
        Case 0.01 To 0.75
           m_Sales.dblHeight = dblValue
    End Select
    End Property
    
    Public Function TotalPrice() As Double
    Dim Q As Double, U As Double
    Q = m_Sales.CArea.dblLength
    U = m_Sales.CArea.dblWidth
    If (Q * U) = 0 Then
       MsgBox "Quantity / UnitPrice Value(s) 0", vbExclamation, "ClsVolume"
    Else
       TotalPrice = m_Sales.CArea.Area 'Get from Base Class ClsArea
    End If
    End Function
    
    Public Function DiscountAmount() As Double
       DiscountAmount = TotalPrice * DiscountPercent
    End Function
    
    Public Function PriceAfterDiscount()
       PriceAfterDiscount = TotalPrice - DiscountAmount
    End Function
     

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

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

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

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

  1. Copy and Paste the following VBA Code into a Standard Module.
    Public Sub SalesTest()
    Dim S As ClsSales
    
    Set S = New ClsSales
    
    S.Description = "Micro Drive"
    S.Quantity = 12
    S.UnitPrice = 25
    S.DiscountPercent = 0.07
    
    Debug.Print "Desccription", "Quantity", "UnitPrice", "Total Price", "Disc. Amt", "To Pay"
    With S
        Debug.Print .Description, .Quantity, .UnitPrice, .TotalPrice, .DiscountAmount, .PriceAfterDiscount
    End With
    End Sub
    
  2. Keep the Debug Window open (Ctrl+G).
  3. Click somewhere in the middle of the Code and press F5 key to Run the Code and to print the output in the Debug Window.
  4. You may test the Code further by entering any of the input value with a Negative number and running the code to trigger the new Error Message.  Disable any of the input line, with a comment symbol ('), run the code and see what happens.

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

Copy and Paste the following Code into a Standard Module and Run to test the Wrapper Class further.

Public Sub SalesTest2()

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

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

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

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

End Sub

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

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

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.

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

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

http://clicky.com/101199826
Blog Directory Visit blogadda.com to discover Indian blogs

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email

Labels

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

Featured Post

Call Function From MouseMove Event Property

Introduction. This is about running a User-Defined Function (say myFunction(Parameter) ) on the Mouse Move Event Property of Text Boxes.  W...

Labels

Blog Archive

Recent Posts