multiple instances of a form 1

[hmlhml]

I am making a mess of opening two instances of a form. The situation is this.
FormA is a menu, opening FormB (a continuous form showing a lot of records) modally. This can open FormC (a dialog form, opened modally), which sometimes wants to open a new copy of FormB for the purposes of selecting data to be retrieved for use on FormC. To avoid visual confusion, each form up to FormC is hiding itself when it opens the next form (me.visible = false), and each form is restoring visibility of its calling form before it closes.

When FormC opens the new copy of FormB, I've been trying to do this by
dim SomeForm as form
set SomeForm = new Form_FormB
I've tried adding SomeForm.SetFocus and SomeForm.Visible = true, but with no noticeable effect.

If I use my code directly from FormC when there is no copy of FormB already open, then I get an error that FormB can't be recognised. If I use the code from FormC reached via the original FormB, then there isn't an error, but no new form appears. Nothing happens. I assume this means that my new copy has opened (somewhere) but it isn't visible.

I think I don't understand what I'm doing, and need help. Does anyone know any good resources for learning about multiple instances of forms in access? Or is this something simple that I'm missing? Many thanks!

 

[MajP]

This will not work, or at least it appears not to work

Public Sub TestInstances()
  Dim frmA As New Form_frmEmployees
  Dim frmB As New Form_frmEmployees
  With frmA
    .Visible = True
    .Caption = "Form A"
  End With
  With frmB
    .Visible = True
    .Caption = "Form B"
  End With
End Sub

The reason is that when you create this form instance it is local in scope. It does open, but as soon as the procedure is over it goes out of scope and closes.

You need to make a module level variable to hold the instances such as.

Public frmA As Form_frmEmployees
Public frmB As Form_frmEmployees

Public Sub TestInstances()
  Set frmA = New Form_frmEmployees
  Set frmB = New Form_frmEmployees
  With frmA
    .Visible = True
    .Caption = "Form A"
  End With
  With frmB
    .Visible = True
    .Caption = "Form B"
  End With
End Sub

However it is even better to manage your forms with a collection. Because then you can work with them by Name. If the collection is at the module level it will hold an open pointer to the form instance even when the local variable goes out of scope.

Public myForms As New Collection

Public Sub TestInstances()
  Dim frmA As New Form_frmEmployees
  Dim frmB As New Form_frmEmployees
  myForms.Add frmA, "Form A"
  myForms.Add frmB, "Form B"
  With myForms.Item("Form A")
    .Visible = True
    .Caption = "Form A"
  End With
  With myForms.Item("Form B")
    .Visible = True
    .Caption = "Form B"
  End With
End Sub
Public Sub RemoveInstance()
  myForms.Remove "Form B"
End Sub

Even better is to roll your own custom collection for managing forms

 

[MajP]

You may also want some helper functions for this. This approach assumes you do not need to use the tag property of the form for something else.

Public EmployeeForms As New Collection

Public Function OpenEmployeeForm(FormName As String) As Form_frmEmployees
  On Error GoTo errLabel
  Dim frm As New Form_frmEmployees
  EmployeeForms.Add frm, FormName
  frm.Visible = True
  frm.Tag = FormName
  Set OpenEmployeeForm = frm
  Exit Function
errLabel:
  If Err.Number = 457 Then
    MsgBox "A Form with this name already exists."
  Else
    MsgBox Err.Number & " " & Err.Description
  End If
End Function

Public Sub CloseAllEmployeeForms()
  Dim I As Integer
  For I = 1 To EmployeeForms.Count
    EmployeeForms.Remove 1
  Next I
End Sub

Public Sub CloseEmployeeForm(FormName As String)
  Dim I As Integer
  Dim blnFound As Integer
  For I = 1 To EmployeeForms.Count
    If FormName = EmployeeForms.Item(I).Tag Then
       blnFound = True
      Exit For
    End If
  Next I
  If blnFound Then
    EmployeeForms.Remove (FormName)
  Else
    MsgBox "Form not Found."
  End If
End Sub

Then you can use the code like this

Public Sub TestOpenForms()
  Dim FormA As Form_frmEmployees
  Dim formB As Form_frmEmployees
  
  Set FormA = OpenEmployeeForm("Form A")
  Set formB = OpenEmployeeForm("Form B")
End Sub

Public Sub TestCloseAForm()
  CloseEmployeeForm "Form A"
End Sub

Public Sub TestCloseAllForms()
  CloseAllEmployeeForms
End Sub

When a user closes a form the GUI it will not automatically close the pointer to the form in the collection. So you need to make sure that the form also removes the pointer from the collection.

Private Sub Form_Close()
    'Remove from Collection
    'You cannot call the remove from collection function because it would
    'run and then run again as the form closes
    Dim I As Integer
    For I = 1 To EmployeeForms.Count
      If EmployeeForms.Item(I).Tag = Me.Tag Then
        EmployeeForms.Remove Me.Tag
      End If
    Next I
End Sub

It is unlikely that a user could have access to open a form from the interface, but if that is the case you would have to handle that as well.

 

[hmlhml]

You hero! Thank you so much. I will be working on this by VPN later this evening, and will get going sorting it out. Yes, the explanation of local variable going out of scope makes perfect sense too. I may as well not cut corners, and do the proper implementation of a collection with relevant functions straight away.
Very much appreciated!

 

[MajP]

Yes, the explanation of local variable going out of scope makes perfect sense too

It does not make that much sense to me, because it is pretty Access unique. If doing this in Excel for example or vb/vb.net it does not work the same way. In excel this works fine. The procedure completes and the forms remain open even though the variables go out of scope.

Public Sub ShowForms()
  'Set modal to false to test
  Dim frm As New UserForm1
  frm.Show
  frm.Caption = "Frm A"
  Dim frm2 As New UserForm1
  frm2.Show
  frm2.Caption = "Frm 2"
End Sub

 

[softhemc]

I used something very similar to this many years ago (Access 97) and created a Forms Stack and complementary Peek, Push and Pop and Take code (Take enabling a Form to be pulled off the stack out of order - this required the For loop being used in descending order i.e. For I = EmployeeForms.Count To 1 Step - 1 to avoid the list going out of sync.)

 

[MajP]

The trick is the same regardless of the data structure. In Access you have to persist a pointer to the form so the scope of the data structure needs to be at the module level. Not sure what utility you would get from a stack or queue but as long as it persists it will work. I would probably actually use a dictionary instead of a collection because then you can check it exists by its index cleaner than with a collection.

 

[hmlhml]

thanks for the mention of Excel working differently. I haven't had much to do with Excel vba, but will remember that one.

I found a note elsewhere that forms started as new instances in the manner described above don't operate in the same way as a typical modal dialogue (no matter how hard one tries), in which the calling-form's code would halt until the caller is closed. At the time I didn't think about its relevance, but it is actually important: if the calling code had halted, the local variables would have remained, and the called form wouldn't have vanished as soon as it arrived.

Good point about handling the case that the form is opened directly. Even if I'm discouraging users from doing this, it's something I do when testing things, and it's always wise to make ones applications self-proof.

 

[hmlhml]

In case anyone finds this thread in years to come, a few follow-up points:

(1) It is really important to avoid modal forms being open when using multiple instances. A modal form open at the same time means that even though the instance is open and visible, no one can click on it or do anything with it.

I've just spent a good while weeding out unnecessary modal forms and acDialogs from my database. It was a useful thing to do anyway. Modal forms should only be used for short tasks that the user absolutely must complete before doing anything else.

(2) New instances of forms are, I think, based on the original form opened the normal access way. This includes local variables and the name of the form. This makes for two Gotchas:

(2a) If you have a "close" button behind which lurks code like: DoCmd.Close acForm me.Name, then calling it will close the original form, not the nice new form, which has to be done using the multiple instance code from MajP.
(2b) I frequently use a local variable to hold information passed in OpenArgs, which I might use to make minor modifications to the form to suit its current purpose. I also use this when I have made FormA hide itself before calling FormB, so FormB now has a local variable called CallingForm = "FormA", so that on exit, it can make FormA visible again. Since another instance of FormB opened the MajP way also contains CallingForm = "FormA" even though it wasn't opened from FormA's module and has nothing to do with FormA, it still opens in a context appropriate to being called from FormA, and makes FormA visible when it's closed.

I'm now struggling to find a way to tell my multiple instances about why they exist and who opened them. I don't have OpenArgs. I wanted to use the Tag property as I'm already giving it a value that will do the job. Unfortunately in the form's OnOpen event the tag property doesn't yet seem to contain its value. I could use TempVars, but I don't like things that are persistent and global unless they really should be visible everywhere, for ever.

 

[MajP]

I'm now struggling to find a way to tell my multiple instances about why they exist and who opened them.

So think of the form module as any class module. You can give it properties and methods. Vba does not really have constructors, but you can fake it. So in you form module you could add a property for who called that instance. Example

'my form module

private mCallingForm as access.form

Public Property Get CallingForm() As Access.form 
    CallingForm = mCallingForm 
End Property 
Public Property Set CallingForm(frm As Access.Form) 
    set mCallingForm = frm 
End Property

So now you have a property that you added to your form. When you create a form instance you can pass who called it. So going back to the openemployee form.

Public Function OpenEmployeeForm(FormName As String, CallingForm as access.form) As Form_frmEmployees
  On Error GoTo errLabel
  Dim frm As New Form_frmEmployees
  EmployeeForms.Add frm, FormName
  Set frm.CallingForm = CallingForm
  frm.Visible = True
  frm.Tag = FormName
  Set OpenEmployeeForm = frm
  Exit Function
errLabel:
  If Err.Number = 457 Then
    MsgBox "A Form with this name already exists."
  Else
    MsgBox Err.Number & " " & Err.Description
  End If
End Function

Now once it is passed in you can figure who called it
EmployeeForms("Form A").callingForm

 

[MajP]

If you have a "close" button behind which lurks code like: DoCmd.Close acForm me.Name

This is a little confusing with Access. When you open a form it is added to the "Forms" collection. The Forms collection is all "Open" Access forms. This is not the same as Excel or VB. For these apps the forms collection is all forms not just the open forms. Anyways, form instances are not added to the Forms collection only the first instance. All instances share the same name, that is why using a collection is a good way to manage them. So when you use the docmd.close it only closes the 1 instance added to the forms collection. So if you would open all instances by code such as "set frm = new frmA", I do not think any would get added to the Forms collection.
:

 

[hmlhml]

Thanks again! Yes, I'll set it up that way, with properties. The more I use access, the more I realise how little I know.

I'm sorry about still having my multiple-instance form being opened traditionally at the same time. This was laziness on my part; I should go back and find all other places in the database where it can be opened, and make them all use the "set frm = new FormA" approach, but was scared I'd miss one.

I really do appreciate all the help

 

[hmlhml]

This is just another update in case someone finds this thread in the future. I just want to add a few cautions.

I really think that if you are considering having multiple instances of a form in a database, this is an important design decision to be taken at an early stage, because it has far-reaching consequences.

Firstly there is the whole question of dialog forms. I've had to remove quite a few dialogs that might have been upstream of my form for which I want multiple instances, because they prevent the multiple instance from doing anything (except appearing in the background and triggering that awful jitter-flash effect that happens when you click outside a dialog form). But this in itself is a problem if, like me, you have forms that affect the results displayed by the form that opened them. This happens to me where I have (Form A) a continuous form summarising all customers, and (Form B) a customer-detail form that can be opened by double-clicking a record in Form A. Normally I let A open B in dialog mode, and then refresh itself. If you have to remove the dialog status of B because it opens something else for which we want multiple instances, then A's code runs straight through simultaneously with B opening, A refreshes itself before there are any changes, and when B is closed, A is not updated. The alternatives are to make A sit and wait in an endless loop for the instance of B it opened to close (do you really want to use 99% of system resources waiting for nothing?). Or to make B refresh A, which is a bit clumsy if B can also be opened by other forms (B has to know about all the forms that could open it, so it can check and refresh each).

Note that this can all happen several forms away from the multiple instance. If you have forms opening forms opening other forms etc., then any multiple instance buried somewhere in the middle of it can have consequences for any dialog somewhere upstream, so there's a need to plan exactly how people will navigate around the database interface.

Secondly, there is a horrible complexity about how the multiple-instance form can find out about its calling-form and surroundings, and react to it. Everything MajP has written has been utterly accurate, for which I'm grateful. But the fact that each form runs its code independently and simultaneously can lead to interesting effects. So far as I can make out, when you open a new instance of a form, it initially inherits all the local variables and properties of the form that Access knows about. If you are opening a new instance, and assigning it new properties and setting its variables, obviously Access can't do this before the instance exists. But as soon as the instance starts to exist, it starts to run its start-up code. It's very hard to work out which will get there first, the OnLoad event of the form you are opening, or the code you are using to set properties that you intend to use in the OnLoad event! I've been reduced to writing public subs in the multiple-instance form, which make controls visible or invisible, independently of anything connected with the OnLoad event, and calling these from the form that created the new instance.

 

[MajP]

If you are opening a new instance, and assigning it new properties and setting its variables, obviously Access can't do this before the instance exists.

This is a limitation in VBA, but not so in other applications. For example in VB.NET you can set properties of a form instance and then open it dialog. Simplifies a lot of these problems you are seeing

One design solution in Access that may help is to be subform heavy. You can put a tabular form for example into a sub form. Then put that subform on to different main forms. The main form may only have some command buttons or different controls but most of the form is taken up by the sub form. Your subform could have a lot of code on it, and be detail heavy. The main forms are just shells. This gives you a lot of the advantages of not having to maintain two copies of the same form, because 90% of the editing is on the subform. But it allows you to open these dialog, and also make them slightly unique.

 

[strongm]

>This is a limitation in VBA

It is more a limitation of Access forms (versus UserForms)

 

[MajP]

That is a good point. Using a userform in Access my solve some of these problems for the OP especially if the instances are only displaying data for a single record. If the instances are continuous forms I would think the additional coding required to do that would make that solution even more complicated than the current solution.