Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/101312

From BetaArchive Wiki

Article ID: 101312

Article Last Modified on 5/6/2003


  • Microsoft Access 1.0 Standard Edition
  • Microsoft Access 1.1 Standard Edition
  • Microsoft Access 2.0 Standard Edition

This article was previously published under Q101312


Advanced: Requires expert coding, interoperability, and multiuser skills.

When you try to open a second instance of an open form with an OpenForm macro action, the instance of the form that is already open is activated.

NOTE: In Microsoft Access for Windows 95 version 7.0 there is a new feature that allows multiple instances of the same form. For more information about this feature, search on the phrase "Displaying Multiple Instances of a Form" using Answer Wizard in Microsoft Access for Windows 95 Help.


You can have only one copy of a form open at a time.


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:

Could not open form "Customers"
Please close another instance and try again.

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.

   Option Explicit

   ' FUNCTION: OpenFormInstance()
   '   Allows you to open more than one copy of the same form
   '   on screen at a time.
   '   You must make X number of copies of the the
   '   form, renaming them with a numeric suffix greater than zero.
   '   Example:
   '        "Customers1," "Customers2," "Customers3," ...
   '   Function IsLoaded() - Determines if form is loaded.
   '   Function GetFormNames() - Supplied in this module.
   '   FormName - The generic name of the form to open.
   '   Example:
   '        "Customers"
   '   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()
   '   Fills the string array with a list of forms names.
   '    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)

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

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

      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.

  1. Repeat step 6.

NOTE: The second instance of the Customers form opens.

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

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


This behavior no longer occurs in Microsoft Access version 7.0.


Microsoft Access, "Introduction to Programming," version 1.1, Chapter 7, "Forms and Report Variables"

Microsoft Access, "Building Applications," version 2.0, Chapter 4, "Using Forms to Collect, Display, and Filter Information"

Additional query words: open twice isloaded running

Keywords: kbfix kbprb kbusage KB101312