Base Class and Derived Object Variants

Base Class and Derived Object Variants.

Encapsulation of Objects.

Last week, we explored an example where a Base Class Object was passed through a Set Property Procedure, allowing it to become part of another object in memory. The passed object essentially became an extension or child object of the main object. In that earlier program, we passed the child object to the target object during the instantiation phase of our test program and then assigned values to the child object’s properties later in the code.

In the next example, we will take a slightly different approach.

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

• MS-Access Class Module and VBA

• MS-Access VBA Class Object Arrays

• MS-Access Base Class and Derived Objects

• VBA Base Class and Derived Object-2

This time, we will open both objects—ClsArea (the base class) and ClsVolume2 (the target class)—separately in our test program. We will assign values to the ClsArea base class properties before passing it to the ClsVolume2 target class object. Remember, the ClsVolume2 class has only one property, p_Height, and its Volume() method requires the Length and Width values from the base class ClsArea to calculate the volume.

1. Copy and paste the following sample Test Code into a Standard Module.

   The SetNewVol2_2 Procedure.

   Public Sub SetNewVol2_2()
   'Method 2/2
   Dim CA As ClsArea
   Dim Vol As ClsVolume2
   
   Set CA = New ClsArea
   Set Vol = New ClsVolume2
   
   CA.strDesc = "Bed Room"
   CA.dblLength = 90
   CA.dblWidth = 10
   Stop
   
   
   'Here ClsArea class Object CA is passed to the 
   ‘Property procedure Set CArea of ClsVolume2 object Vol
   Set Vol.CArea = CA 'Pass ClsArea obj to ClsVolume2
   
   Vol.dblHeight = 10 'assign height to ClsVolume2
   
   
   Debug.Print "Description", "Length", "Width", "Area", "Height", "Volume"
   With Vol.CArea
     Debug.Print .strDesc, .dblLength, .dblWidth, .Area(), Vol.dblHeight, Vol.Volume()
   End With
   Stop
   
   Set CA = Nothing
   Set Vol = Nothing
   
   End Sub
   

   VBA Code Review.

   In the first Dim statement, CA is declared as a ClsArea object and Vol as a ClsVolume2 object. The next two statements instantiate these objects in memory.

   The following three statements assign values to the properties of the ClsArea object.

   A Stop statement is then used to pause code execution, allowing us to inspect the object property values in the Locals window.

   Next, the statement Set Vol.CArea = CA assigns the ClsArea object (CA) as a child object of the ClsVolume2 object (Vol).

   After that, the value 10 is assigned to the dblHeight property of the ClsVolume2 object.

   The subsequent statements, placed before the next Stop statement, print the property values from memory to the Debug window.

   Finally, the last two Set statements release both objects from memory before the program ends.

   Display the Locals Window.

2. Inspecting the Locals Window

   1. Open the Locals Window
      From the View menu in the VBA Editor, select Locals Window.

   2. Run the Code

      • Click anywhere in the middle of the code window.

      • Press F5 to run the program until it pauses at the Stop statement.

      • Alternatively, press F8 to run the code step by step, which lets you observe the changes in the Locals Window at each step.

   3. Expand the Objects
      Click the [+] symbol next to the object names in the Locals Window to expand and display their properties and current values.

   4. Observe Object References

      • Check the CArea and p_Area object references under the Vol object.

      • At this point, their values will show as Nothing because we have not yet passed the CA object to the Vol object.

   5. Continue Running the Code

      • After reviewing the Locals Window, run the code until it pauses at the next Stop statement.

      • Now, the CArea Set Property Procedure assigns the p_Area object reference to the ClsArea object, linking it into the ClsVolume2 object.

   Next, we will try another variation of this example using the same two classes — ClsArea and ClsVolume2 — to demonstrate a slightly different approach.

New Class Module: ClsVolume3.

1.  Insert a new Class Module and change its name Property Value to ClsVolume3.

2.  Copy and Paste the following VBA Code into the ClsVolume3 Class Module:

Option Compare Database
Option Explicit
'Method three 
Private p_Height As Double
Public p_Area As ClsArea

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

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

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

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

Private Sub Class_Terminate()
    Set p_Area = Nothing
End Sub

In the code, p_Height is declared as a Private Property, while p_Area is declared as a public ClsArea object within the ClsVolume3 class. This means p_Area appears as a property of the ClsVolume3 class, with its own accessible properties and methods for direct Get/Let operations in the user program (from a standard module). Although the ClsArea object is exposed as a public property of ClsVolume3, its internal properties and methods remain encapsulated within the ClsArea class itself.

It is important to ensure that the ClsArea class is fully developed and free of errors before it is encapsulated into other classes.

The Class_Initialize() and Class_Terminate() routines handle the lifecycle of the embedded object: The ClsArea object is instantiated in Class_Initialize() when a ClsVolume3 object is created, and released from memory in Class_Terminate() When the user program ends.

The Testing Program.

The sample Test VBA Code is given below.

Copy and paste the code into the Standard Module.

Public Sub SNewVol3()
'Here ClsArea class is declared as a Public Property of ClsVolume3
Dim volm As ClsVolume3

Set volm = New ClsVolume3

volm.p_Area.strDesc = "Bed Room"
volm.p_Area.dblLength = 15 'assign length
volm.p_Area.dblWidth = 10 'assign width in clsArea
volm.dblHeight = 10 'assign height to ClsVolume2

Debug.Print "Description", "Length", "Width", "Area", "Height", "Volume"
With volm.p_Area
   Debug.Print .strDesc, .dblLength, .dblWidth, .Area, volm.dblHeight, volm.Volume
End With
Set volm = Nothing

End Sub

Display the Locals Window (View -> Locals Window), if it is not already open.

Click somewhere in the middle of the code, and press F8 to execute the VBA Code one line at a time and watch the Locals Window to track what happens at each step.

All the above variants of the ClsVolume Class have been written with less Code, except the ClsVolume Class.  

Working with the Recordset Object.

Next week, we will work with a built-in Object 'DAO.Recordset' and build a Class Module to:

1. Calculate and update a Field,

2. Sort the Data,

3. Print the sorted data in the Debug Window,

4. And create a Clone of the Table with the data sorted.

That is a lot of action next week.

List of All the Links on this Topic.

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

Read More

Opening Multiple Instances of Form in Memory

Opening Multiple Instances of a Form in Memory.

1. Over the past few weeks, we have been learning about the dot (.) separator and the exclamation (!) symbol in VBA object references. Now, we will explore some interesting techniques with Forms in VBA, specifically:

   • How to call a function procedure embedded in a form’s module (class module) from the code outside that form?

   • How to open multiple instances of a single Microsoft Access Form in memory, each displaying different information?

   For example, the Screenshot below shows two instances of the Employees Form. The first instance is behind the second, displaying employee details for IDs 4 and 5. Click the image to enlarge for a clearer view.

   

   Right-click to open Large Image in New Tab/Window.

   
   

   Calling the Form's Class Module Public Function.

2. How to call a Function Procedure on the Form's Class Module from outside the Form?

   Call the Function from a Standard Module,  from the Module of another form, or from the VBA Debug Window (Immediate Window).  The target Form must be opened in memory to call the Form's function procedure from outside.

Function procedures in a form module are useful for avoiding code duplication. They can be called from subroutines in different parts of the same form, from a command button click, or from other event procedures within the form. A function procedure in a form module can perform calculations, validation checks, update information, or act as a standalone operation—such as the one we will use in our sample Employees Form.

All event procedures in a form module are automatically declared as Private Subroutines, and they begin and end with standard statements, as shown in the sample below. Any custom VBA code that performs specific tasks should be placed within this block:

Private Sub Command8_Click()
.
.
End Sub

A Private subroutine or function is limited in scope to the module in which it is declared and cannot be called from outside that module. Declaring procedures as private is essential to avoid name conflicts with procedures of the same name in other Class Modules or Standard Modules.

To call a function or procedure from outside a Form, the function must be declared Public in the Form’s module.

To perform a trial run of the above trick, you need the Employees Table and a Form.

1. Import the Employees Table from the Northwind sample database.

2. Click on the Employees Table to select it.

3. Click the Create Ribbon.

4. Select the Form option and create a Form for the Employees Table, in the format shown above.

5. Save the Form with the name frmEmployees.

6. Open the frmEmployees Form in Design View.  Set the Form Property 'Has Module' Value to Yes.

7. Select the Design Menu and select VBA Code from the Tools button group to open the Form Module.

8. Copy the following VBA code and paste it into the VBA Module of the Form.

   Public Function in the Form Class Module.

   Public Function GetFirstName(ByVal EmpID As Integer) As String
   Dim rst As Recordset, crit As String
   Dim empCount As Integer
   
   'get total count of employees in the Table
   empCount = DCount("*", "Employees")
   
   'validate employee code
   If EmpID > 0 And EmpID <= empCount Then
   
       crit = "ID = " & EmpID
       Set rst = Me.RecordsetClone
       rst.FindFirst crit
       
       If Not rst.NoMatch Then
             
        	Me.Bookmark = rst.Bookmark
           GetFirstName = rst![First Name]
       End If
       
          rst.close
          Set rst = Nothing
       
     Else
       MsgBox "Valid Employee IDs: 1 to " & empCount
   End If
   
   End Function
   

9. Save and Close the Form.

Notice that the starting line of the above function is declared as Public.

The function GetFirstName() accepts EmployeeID as a parameter, locates the corresponding record on the form, and makes that record the current record. If the search is successful, the function returns the Employee’s first name to the calling procedure. If the search fails, it displays a warning message that the EmployeeID is not within a valid range of IDs in the Employees table.

Next, we need a program in a Standard Module that calls the GetFirstName() function from the frmEmployees form module. This program will also demonstrate how to create multiple instances of a Microsoft Access form, allowing you to open them in memory and access their properties, methods, or control contents independently.

1. Open the VBA Editing Window (Alt+F11).

2. Select the Module option from the Insert Menu and add a new Standard Module.

3. Copy and paste the following VBA Function code into the new Module.

   Call GetFirstName() from the Standard Module.

   Public Function frmInstanceTest()
   
         Dim frm As New Form_frmEmployees '1st Form instance
   
         Dim frm2 As New Form_frmEmployees '2nd instance declaration
   
         Dim Name1 As String, Name2 As String
   
     frm.Visible = True 'make the instance visible in Application Window
     frm2.Visible = True '2nd instance visible
   
     Name1 = frm.GetFirstName(4) 'Call the GetFirstName of Employee ID 4
     
     Name2 = frm2.GetFirstName(5) ''Call the GetFirstName of Employee ID 5
   
   'pause execution of this code to view
   'the Employees Form instances in Application Window.
   
   Stop
   
     MsgBox "Employees " & Name1 & ", " & Name2
     
   End Function

Trial Run of the function frmInstanceTest()

Let us run the code and view the result in the Application Window.

1. Click within the frmInstanceTest() Function code and press F5 to run the Code.

   The program will pause at the Stop statement, allowing you to view the Access application window, where multiple instances of the frmEmployees form are open in Normal View, with one instance overlapping the other.

2. Press Alt+F11 to display the Application Window, where both instances of the Form are visible, the second form overlapping the first.

3. Click and hold the Title Bar area at the top and drag it to the right to make part of the form behind visible.

   Observe the employee records on both forms—they are different: one displays Employee ID 4, and the other displays Employee ID 5. Notice the title bar of both forms; they both show the same form name, frmEmployees. Now, return to the program and continue running the code to complete the task.

4. Press Alt+F11 again to switch back to the VBA Window and to continue executing the remaining lines of code.

   The Message Box appears in the Application Window, displaying the Employee names Mariya and Steven together.  When you click the OK MsgBox Button, the frmEmployee form instances disappear from the Application Window.

5. Click the OK button on the MsgBox.

Note: Pay special attention to the Stop statement placed above the MsgBox() function at the end of the code. The Stop statement halts VBA execution at that point. While it is typically used during debugging to trace logical errors or make corrections, here it serves a different purpose: it pauses the program so that we can switch to the Access application window and view both open instances of the frmEmployees form.

If we relied on the MsgBox() function alone, the code would still pause, but the message box would remain on top of the forms. This would prevent us from dragging the front form aside to view the one behind it. By using the Stop statement, we gain the flexibility to examine both instances directly in the application window.

If we don't create a pause in code execution, both instances of the form are closed immediately when the program ends.  In that case, we will not be able to view the forms.  Since it is a trial run, we would like to know what is happening in the program. It is not necessary to make the Form instances visible before calling the Function GetFirstName ().

The VBA Code Line by Line.

Let’s take a closer look at each line of code in the frmInstanceTest() function. While brief hints are already included within the code, explaining a few points in detail will make them clearer. We’ll begin with the first two Dim statements.

Dim frm As New Form_frmEmployees

Dim frm2 As New Form_frmEmployees

In the Dim statement, you can see that the New keyword is followed by the object reference. The object name is our frmEmployees, prefixed by the direct Object Class name FORM, followed by an underscore separation (Form_) to the frmEmployees Form name (Form_frmEmployees).  These Dim statements themselves open two instances of the frmEmployees in memory.   Form instances opened in this way are not immediately visible in the Application Window.  If we need them to be visible, then make them visible with another statement.

Next, we declared two String Variables: Name1 & Name2 to hold the names returned by the GetFirstName() method.

Next two statements: frm.Visible=True and frm2.Visible=True make both instances of the frmEmployees form visible in the Application Window, for information purposes only.

The next two lines of code call the GetFirstName() method, the first and second instances of frmEmployees, to search and return the first names associated with employee codes 4 and 5, respectively.

Default Instance and Other Instances.

The default instance of a Form is opened in the code shown below to access its Properties, Methods, and Controls. This style of statement is commonly used to open a Form in applications. Once opened, the default instance of the Form is automatically displayed in the Application Window.

Dim frm as Form 'define a Form class object
DoCmd.OpenForm "frmEmployees", vbNormal 'open frmEmployees in Memory
Set frm3 = Forms!frmEmployees ' attach it to the frm3 object

Assume that we have opened frm & frm2 instances first in memory before the default instance through the above code.  How do we address those three instances in a program to do something?  Let us forget about the frm, frm2, and frm3 object references for now; we will go with the straight method, like the one given below:

name3 = Forms![frmEmployees].GetFirstName(5) 'target form in memory is the default instance
'OR
name3 = Forms("frmEmployees").GetFirstName(5) 
'OR
name3 = Forms(2).GetFirstName(5) ' this is the third and default instance

The other two instances in memory cannot be referenced like the first two default methods, using the Form name. You have to use only the index number of the Forms collection to address the other two instances.

name1 = Forms(0).GetFirstName(3)
name2 = Forms(1).GetFirstName(6)

A Shortcut Method.

There is a shortcut method you can use to run the GetFirstName() Method of the frmEmployees Form from the debug window (Ctrl+G).  Type the following command in the Debug Window and press Enter Key:

? form_frmEmployees.GetFirstName(5)
'Result: Steven
'OR
X = form_frmEmployees.GetFirstName(5) 

When we execute the above command, it opens an instance of the frmEmployees form in memory and calls the GetFirstName() function with Employee Code 5 as the parameter. The GetFirstName() function searches for the record, finds it, returns the employee’s first name to the calling program, and then closes the form.

Tip: Even after the form is closed, the current record—Employee ID 5 in this case—remains the active record of the closed form’s last session.

You can verify this behavior by typing the following shortcut command in the Debug Window and pressing Enter:

? Forms!frmEmployees!EmployeeID

? form_frmEmployees![First Name]

'Result: Steven

A Fancy Approach.

In the command above, we didn't run the GetFirstName() method, but the current record's First Name field value is printed. If you want to get a little fancy with the command, then try this by typing it in the debug window and pressing the Enter Key:

MsgBox "First Name: " & form_frmEmployees.GetFirstName(8)
'OR
MsgBox "First Name: " & form_frmEmployees![First Name]

Or try the above command from a Command Button Click Event Procedure from another Form's Module, as given below.

Private Sub Command8_Click()
  MsgBox "First Name: " & Form_frmEmployees.GetFirstName(8)

End Sub

• Create 3D Headings on Form
• Reminder Ticker on Form
• Open Forms with Hyperlink in Listbox
• Overlaying Sub-Forms in Real-Time

Read More