ACC: Cannot Open Multiple Instances of the Same Form

RESOLUTION

NOTE: This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information on Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x, or the "Building Applications" manual in version 2.0.

The following information describes how to create and use an Access Basic function called OpenFormInstance(), which allows you to open more than one copy of the same form in Microsoft Access. To make OpenFormInstance work, you must first make <x> number of copies of your original form, giving each copy a numeric suffix greater than zero. That is, if your form is called "Customers," make copies of the form renaming them to "Customers1," "Customers2," "Customers3," and so forth.

When OpenFormInstance() is invoked with "Customers," it checks to see if a copy of the form is available for display. It returns the following error message if all instances are currently open:

To use the OpenFormInstance() function, create a new module with the following Access Basic functions:

NOTE: In the following sample code, an underscore (_) is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

   '*****************************************************************
   ' DECLARATION SECTION
   '*****************************************************************
   Option Explicit

   '*****************************************************************
   ' FUNCTION: OpenFormInstance()
   '
   ' PURPOSE:
   '   Allows you to open more than one copy of the same form
   '   on screen at a time.
   '
   ' PREREQUISITES:
   '   You must make X number of copies of the the
   '   form, renaming them with a numeric suffix greater than zero.
   '   Example:
   '
   '        "Customers1," "Customers2," "Customers3," ...
   '
   ' REQUIRES:
   '   Function IsLoaded() - Determines if form is loaded.
   '   Function GetFormNames() - Supplied in this module.
   '
   ' ARGUMENTS:
   '   FormName - The generic name of the form to open.
   '   Example:
   '
   '        "Customers"
   '
   ' RETURNS:
   '   True  - if an instance of FormName was opened.
   '   False - if no instance of FormName was found or all available
   '           instances are active.
   '
   ' NOTES:
   '   You can customize the OpenForm action in the code below to add
   '   additional (optional) parameters, such as a Where condition or
   '   or alternate view.
   '
   '********************************************************************
   Function OpenFormInstance (ByVal FormName As String)
      Dim Count, i, Msg As String, InstanceCount
      ReDim Names(0) As String

      ' Get a list of all the forms in the current database.
      Count = GetFormNames(Names())

      ' Loop through the list of forms for a match with
      ' .. the requested form.
      For i = 0 To Count - 1
         ' Is the name of the form, minus the suffix, the same?
         If FormName = Left(Names(i), Len(FormName)) Then

            ' .. is the suffix a number greater than zero?
            If Val(Mid(Names(i), Len(FormName) + 1)) > 0 Then

               ' Count the number of instances.
               InstanceCount = InstanceCount + 1

               ' If the form is NOT loaded,load it.
               If Not IsLoaded(Names(i)) Then
                  DoCmd OpenForm Names(i)
                  OpenFormInstance = True
                  Exit Function
               End If
            End If
         End If
      Next i

      ' No form was found or all instances are being used.
      OpenFormInstance = False

      ' If no instance was found, just return.
      If InstanceCount = 0 Then Exit Function

      ' If all instances are being used, display error message.
      Msg = "Couldn't open form """ & FormName & """."
      Msg = Msg & Chr$(13) & Chr$(13)
      Msg = Msg & "Please close another instance and try again."
      MsgBox Msg, 48

   End Function

   '****************************************************************
   ' FUNCTION: GetFormNames()
   '
   ' PURPOSE:
   '   Fills the string array with a list of forms names.
   '
   ' ARGUMENTS:
   '    Names      - A single dimensional array of type string.
   '
   ' RETURN:
   '   The number(zero based) of names stored in the Names array.
   '
   ' NOTES:
   '   This function uses information stored in the
   '   MySysObject table of the currently opened database.
   '   The system tables are undocumented and are subject to
   '   change in future versions of Microsoft Access.
   '
   '****************************************************************
   Function GetFormNames (Names() As String)
      Dim db As Database, ss As Snapshot
      Dim Count, SQL
      Dim Msg As String

      SQL = "Select Name,Type from MSysObjects Where Type="
      SQL = SQL & "-32768 And Left(Name,1)<>'~' Order By Name;"
      Set db = CurrentDB()
      Set ss = db.CreateSnapshot(SQL)

      ss.MoveLast
      If ss.RecordCount > 0 Then
         ReDim Names(0 To ss.RecordCount - 1)
      Else
         GetFormNames = 0
         Exit Function
      End If

      ss.MoveFirst
      Count = 0
      Do While Not ss.EOF
         Names(Count) = ss![name]
         Count = Count + 1
         ss.MoveNext
      Loop

      GetFormNames = ss.RecordCount
   End Function
				

How to Use the OpenFormInstance() Function

The following steps assume there is a need to see more than one customer form on the screen at a given time in the sample database NWIND.MDB:

1. Open NWIND.MDB and choose the Form button in the Database window.
2. Click once on the Customers form, and choose Copy from the Edit menu.
3. Choose Paste from the Edit menu. Type Customers1 in the Form Name box and then press ENTER.
4. Repeat step 3 for however many instances you expect your users to need ("Customers2," "Customers3," and so on).
5. Open the module containing the OpenFormInstance() function in Design view, and then choose Immediate Window from the View menu.
6. Type the following and press ENTER:
   
   ? OpenFormInstance("Customers")
   
   NOTE: The first instance of the Customers form opens.
7. Repeat step 6.
   
   NOTE: The second instance of the Customers form opens.
8. Repeat step 6 until you receive an error message indicating that no more instances are available.

Problems to Consider When Using OpenFormInstance()

Since you are not actually opening the same form multiple times (you are opening copies), it is important that any macros and module code that make direct references to the form using "Forms!FormName" references be reconsidered.

If you have more than one instance of the form opened, it is best to use the following techniques when you reference controls on the form.

• User Screen.ActiveForm form notation wherever possible.
  

        MsgBox Screen.ActiveForm![ControlName]
  						

• When Screen.Active form is not appropriate, pass the name of the form to the Access Basic function to call.
  

        Function MyFunction(MyForm)
           MsgBox Forms(MyForm)![ControlName]
        End Function
  						

• Because you cannot pass arguments to macros, consider reworking the macro as an Access Basic function. You could call the function above, MyFunction(), using a RunCode macro action as follows:
  

        RunCode
           Function Name: =MyFunction(Screen.ActiveForm.FormName)