How to have several copies of the same forms open simultaneously

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?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
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 LeadCommented:
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.

cornholmeAuthor Commented:
Hi Ryan
Thank you for your comment. I've already set Tabbed documents to true in Access Options. That part works ok.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

cornholmeAuthor 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!

Gustav BrockCIOCommented:
You can easily open several instances of the same form, as shown years ago:

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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cornholmeAuthor Commented:
Thank you Gustav the code looks interesting I shall follow it up

john cliviger
Gustav BrockCIOCommented:
You are welcome!

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

  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.

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

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.