Last few weeks we have been through learning the usage of dot(.) separator and exclamation symbol(!) in VBA object references.  Now, we will explore some interesting trick with Forms in VBA.  How to Call a Function Procedure embedded in a Form Module (Class Module), from a program outside the Form?

We will explore two different aspects on this particular topic.

1. How to open several instances of a single Microsoft Access Form in memory, displaying different information on each of them?

   Sample screen shot of two instances of Employees Form is given below for reference.  The first form is behind the second instance of the form, displays employee details of ID: 4 & 5.   Click on the image to enlarge the picture. 
2. How to call a Function Procedure on the Form 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 in order to call the function procedure of the form from outside.

Function Procedures in a Form module is helpful to avoid duplication of code. It can be called from subroutines in different locations on the same Form, from a command button click or from some other Event Procedures of the Form.  The function procedure on a Form Module can be anything that does some calculation, validation check, updating the information or a stand-alone search operation procedure, like the one we are going to use on our sample Employees Form.

All the Event Procedures on a Form Module are automatically declared as Private Subroutines and they all will start with the beginning  and end Statements, like the sample statements given below.   Our own VBA codes that does something goes within this block of codes:

Private Sub Command8_Click()
.
.
End Sub

The scope of Private declared Subroutine/Function stays within that module and cannot be called from outside.  Private declaration is absolutely necessary to avoid procedure name clash with the same name on another Class Module or Standard Module.  Form Function Procedure must be declared as Public in order to call it from outside the Form.

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

1. Import Employees Table from Northwind.accdb sample database.
2. Click on the Employees Table to select it.
3. Click on Create Ribbon.
4. Click on Form Button, from the Forms group, to create a Form, like the image given above, for Employees Table.
5. Save the Form with the name frmEmployees.
6. Open the frmEmployees Form in Design View.
7. Click on the View Code button, in Tools button group, to open Form Module.
8. Copy the following VBA Code and Paste them into the VBA Module of the Form.

   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 'create search criteria
       crit = "ID = " & EmpID
   'make a clone of the recordset of the Form
       Set rst = Me.RecordsetClone
   'Find the first record that matches the criteria
       rst.FindFirst crit
   'If the record is found then
       If Not rst.NoMatch Then
   'copy the recordset bookmark to the form bookmark
   'this will make the found record current on the form
        Me.Bookmark = rst.Bookmark
   'Return the Employee first name to the calling program
        GetFirstName = rst![First Name]
       End If
   'close the recordset clone and release the used memory
       rst.close
       Set rst = Nothing
   'IF EmployeeID is not in range of 1 to empCount
   Else
   'then display a message and exit function
       MsgBox "Valid Employee IDs: 1 to " & empCount
   End If
   
   End Function
   

9. Save and Close the Form.

Have you noticed the starting line of the above Function that is declared as Public?

The Function GetFirstName() accepts Employee ID number as parameter, finds the record and makes that record current on the form. The Function returns the First Name of the Employee to the calling program,  if the search was successful.  If the search operation fails then it gives a warning message, saying that the employee ID code passed to the function is not within the range of ID codes available in the Employees table.

Now, we need another program, in the Standard Module, to run the search function GetFirstName() from the frmEmployees Form Module.  Besides that this program demonstrates as how to create more than one instance of a Microsoft Access Form and open them in memory, to access their properties, methods or control contents.

1. Open VBA Editing Window (Alt+F11).
2. Select Module from Insert Menu and add a new Standard Module.
3. Copy and paste the following VBA Function code into the new Module.

   Public Function frmInstanceTest()
   
   'Create first instance of the form frmEmployees
   'and opens it in memory(not visible in application window)
         Dim frm As New Form_frmEmployees
   
   'Create second instance of the Form frmEmployees
   'and opens it in memory(not visible in application window)
         Dim frm2 As New Form_frmEmployees
   
   'Name1, Name2 string variables
         Dim Name1 As String, Name2 As String
   
   'Make both instances of the frmEmployees
   'visible in the Application Window
   
     frm.Visible = True
     frm2.Visible = True
   
   'Call the GetFirstName() Public Function of
   '1st instance of the frmEmployees with Employee ID: 4
   'Record of ID 4 becomes current on frmEmployees
   'and returns first name to variable Name1
   
     Name1 = frm.GetFirstName(4)
   
   'Call the GetFirstName() Public Function of
   '2nd instance of the frmEmployees with Employee ID:5
   'Record ID 5 becomes current on frmEmployees
   'and returns first name to the variable Name2
   
     Name2 = frm2.GetFirstName(5)
   
   'pause execution of this code to view
   'the Employees Form instances in Application Window.
   
   Stop
   
   'display the first names retrieved from
   'both instances of the Employees Form
     MsgBox "Employees " & Name1 & ", " & Name2
   End Function
   

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

1. Click somewhere within the body of frmInstanceTest() function and press F5 key to run the code.

   The program will pause at the Stop statement and this will facilitate to view the Application window, where the frmEmployees Form instances are open in normal view mode, one overlapping the other.

2. Press Alt+F11 to display the Application Window displaying both instances of the Form, second form overlapping the first one.
3. Click and hold on the title bar area of the top form and drag it to the right, to make part of the form behind visible.

   Check the employee records on both forms, they are different, one with employee code 4 and the other is 5.  Check the title area of forms, both are showing frmEmployees title.  Now, let us come back 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 press F5 key one more time to continue executing the remaining lines of code.

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

5. Click OK button on the MsgBox.

Note: I would like to draw your attention to the Stop statement above the MsgBox() function, at the end part of the code. The Stop statement pauses execution of the VBA code on that statement.  Normally, this statement is used in a program for debugging of code, to trace logical errors and corrections.  Here, it is required to pause execution of code so that we can go to the Application Window and view both instances of the frmEmployees Form there.  The MsgBox() will pause the code but we will be able to view the topmost instance of the form only. We cannot drag the top form to the right side while msgBox is displaced.

If we doesn't create a pause in the 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.

Let us take a closer look at each line of code of the frmInstanceTest() function.  Even though hints are given on each line of code, explaining few things here will make them more clear to you.  We will start with the first two Dim Statement.

Dim frm As New Form_frmEmployees
Dim frm2 As New Form_frmEmployees

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

Next we have 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, makes both instances of frmEmployees Form visible in the Application Window.

In Next two lines of code we are calling the GetFirstName() method of first and second instances of the frmEmployees to search, find and return the First Names of employee code 4 and 5.

The default instance of a Form openes in the following manner in programs for accessing their Properties, Methods and Controls.  These style of statements are always used to open a form in programs. The default instance will be automatically visible, when it is open, 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 frm object

Assume that we have opened frm & frm2 instances first in memory before the default instance through the above code.  How to address those three instances in a program to do something?  Let us forget about the frm, frm2, frm3 object references for now, we will go by 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 name of the form. You have to use only the index number of Forms collection to address the other two instances.

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

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 on the Debug Window and press Enter Key:

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

What happens when we execute the above command?  It opens an instance of the frmEmployees in memory, Calls the Function GetFirstName() with the employee Code 5. The GetFirstName() runs and finds the record and returns the First Name of the employee and closes the form.

Tip: Even after closing the Form, after execution of the above command, the current record of Employee ID 5 remains as current on the closed Form.

You can check this by executing the following shortcut command by typing it in the debug window and pressing Enter Key.

? form_frmEmployees![First Name]
'Result: Steven

In the above command we didn't run the GetFirstName() method but directly printed the First Name from the current record on the form. If you want get little fancy with the command then try this by typing it in debug window and press 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