Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Opening Multiple Instances of Form in Memory

Introduction.

For the 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 tricks 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 of this particular topic.

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

    A sample screenshot of two instances of the Employees Form is given below for reference.  The first form is behind the second instance of the form, displaying employee details of ID: 4 & 5.   Click on the image to enlarge the picture. 


    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 in order to call the function procedure of the form from outside.

Function Procedures in a Form module are 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 in 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 code that does something will go 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.  The private declaration is absolutely necessary to avoid a procedure name clash with the same name in another Class Module or Standard Module.  The Form's 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 sample database.
  2. Click on the Employees Table to select it.
  3. Click on Create Ribbon.
  4. Select the Form option and create a Form, for 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 them into the VBA Module of the Form.

    Public Function in Form ClassModue.

    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.

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

The Function GetFirstName() accepts a parameter EmployeeID value, finds that record and will make 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 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 the Module option from Insert Menu and add a new Standard Module.
  3. Copy and paste the following VBA Function code into the new Module.

    Call GetFirstName() from 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 Function frmInstanceTest()

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

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

    The program will pause at the Stop statement and this will facilitate the viewing of 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, the second form overlapping the first one.
  3. Click and hold on to 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 the forms, both are showing frmEmployees titles.  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 the F5 key one more time 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: 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 the execution of the VBA code on that statement.  Normally, this statement is used in a program for debugging code, to trace logical errors and corrections.  Here, it is required to pause the 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 see only the topmost instance of the form. We cannot drag the top form to the right side while the MsgBox is displaced.

If we don'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. It is not necessary to make the Form instances visible, before calling the Function GetFirtName().

The VBA Code Line by Line.

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 a few things here will make them more clear to you.  We will start with the first two Dim Statements.

Dim frm As New Form_frmEmployees
Dim frm2 As New Form_frmEmployees

In the above 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 character 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, makes both instances of the frmEmployees Form visible in the Application Window, for information purposes only.

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

Default Instance and Other Instances.

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

A Fancy Approach.

In the above command, 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
Share:

No comments:

Post a Comment

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

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