How to have several copies of the same forms open simultaneously

cornholme
cornholme used Ask the Experts™
on
I have a database that allows users to create job quotations and there are thousands of them. The principle Form has 5 sub forms and it works fine.  Now users wish to view several passed quotes and select one to a  create a new record.  The issue I have is  how to have several copies of the same form open simultaneously that a user can switch between?  Someone will have done this already, but where is it?

Regards
Cornholme
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
The usual scenario is to open instances of the application not multiple instances of same form on the single application instance...i haven't tried if it's possible to see if this work...
Ryan ChongSoftware Team Lead

Commented:
how to have several copies of the same form open simultaneously that a user can switch between?
Not too sure how you want to present it but just an idea that you may use the Tab Control and put your relevant controls on to it. That may do the trick.

SnapShot.png

Author

Commented:
Hi Ryan
Thank you for your comment. I've already set Tabbed documents to true in Access Options. That part works ok.
cheers
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Hi John,
Thank you for your feedback. Having several instances of Access open is going to confuse users I feel. I'd like to keep the Forms in one App. I was thinking maybe of using a Collection but I'm unsure is that the appropriate solution? I don't know. Anyway the law according to Sod has prevailed here, I just found Allen Browne's Managing Multiple Instances of a Form which advocates using Collections, so I shall follow that up. Just after when I've posted a question!

Regards
cornholme
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
You can easily open several instances of the same form, as shown years ago:

    http://allenbrowne.com/ser-35.html

The trouble is to get hold on these. Here is working code by Kenneth Ismert:

Enhancements on Allen's approach:
* One module per form. Every method in the module is prefixed with the form name. This keeps managing this kind of code simple
* You send the parameters when you call the 'nameNew' routine
* The form gets one chance to read the parameters using the 'nameOpenArgs' function
* Since multiple instances of a form all share the same query, save the form with a blank RecordSource, and set it with SQL when the form opens

To create a new instance of form 'Blah', call 'BlahNew' with your OpenArgs parameter:

Option Compare Database
Option Explicit

' Module MBlah

Private mrBlahs As New Collection
Private mvBlahParameter As Variant

Public Sub BlahDestroy(lHwnd As String)
    On Error Resume Next
    mrBlahs.Remove CStr(lHwnd)
End Sub

Public Sub BlahNew(vBlahParameter As Variant)
    Dim rForm As Access.Form
    mvBlahParameter = vBlahParameter
    Set rForm = New Form_frmBlah
    rForm.Visible = True
    mrBlahs.Add rForm, CStr(rForm.hwnd)
    mvBlahParameter = Empty
End Sub

Public Function BlahOpenArgs() As Variant()
    BlahOpenArgs = Array()
    If Not IsEmpty(mvBlahParameter) Then
        BlahOpenArgs = Array(mvBlahParameter)
    End If
End Function

Public Function BlahSql(vBlahParameter As Variant)
    BlahSql = "SELECT [Blah] FROM [Blah] WHERE ([Blah].[Blah] = '" & vBlahParameter & "');"
End Function

Open in new window

And here is the Form code.
Note that the Recordsource is left blank for multi-instance forms, and is filled in using code:
Option Compare Database
Option Explicit

' Form class Form_frmBlah

Private mvMyParameter As Variant

Private Sub cmdClose_Click()
    BlahDestroy Me.hwnd
End Sub

Private Sub Form_Close()
    BlahDestroy Me.hwnd
End Sub

Private Sub Form_Open(Cancel As Integer)
    Dim vArgs() As Variant
    vArgs = BlahOpenArgs()
    mvMyParameter = vArgs(0)
End Sub

Private Sub Form_Load()
    Me.RecordSource = BlahSql(mvMyParameter) 
End Sub

Open in new window

/gustav

Author

Commented:
Thank you Gustav the code looks interesting I shall follow it up

john cliviger
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!

/gustav
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
cornholme,

  Just to add a comment (and if you already realize this, apologies).

 The issue with multiple forms in Access is that while it can do them, once you do you can no longer refer to them by name as all will have the same name.

 So there are two basic techniques:

1. Create multiple copies of the form (say five frmCustomers; frmCustomer1, frmCustomer2, ......frmCustomer5)  and limit the user.   Limits resources, but bloats the app and makes code management more of a problem (five copies of the same form code if you have any).

2. Use a collection to track the forms opened and use that to deal with them.   This allows unlimited forms, but that might not be a hot idea because of resources.   At some point you'll get "out of resources" from Access if a user decides to open copy after copy.

  Which method is a toss up.  #1 is *fairly* manageable if you do it with one or two forms in the app and you can limit the user.   I actually find most users can't keep track of more than a few anyway.

  And while #2 is a nice feature to add to your app, you can shoot yourself in the foot.

  So in general, despite being a bit messy, I've typically leaned towards #1 myself when someone insists on the ability to have multiple copies of a form open.

Jim.

Author

Commented:
Jim, thank you for those comments that is really helpful!

regards
Cornholme

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial