Introduction

Welcome! Open Multiple Instances of a Form

S M L XL FS | Slo Reg Fast 2x | Bookmark

Welcome to Microsoft Access Developer Level 47. In this course we will learn how to open multiple instances of the same form, such as viewing several customers or products at once. We will discuss using collections to manage these form instances, track them with key values, handle initialization and closing, prevent duplicate forms, give specific forms focus, and cascade their windows. The course also covers reviewing code with ChatGPT for improvements and suggestions. This material is based on Access 365 but should work with versions back to Access 2007.

Navigation

Prev: D47
Next: Lesson 01

Keywords

Access Developer, multiple form instances, open same form multiple times, VBA collections, track forms with key value, cascade open forms, prevent duplicate forms, initialize collection, manage open forms, memory management, Access 365, ChatGPT code revie

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

Welcome to Microsoft Access Developer Level 47 brought to you by AccessLearningZone.com. I am your instructor Richard Rost.

Today we are going to learn about opening multiple instances of the same form. For example, if you want to open four customers at the same time, or if you are working on orders and you want to open five different products so you can see all their information at the same time. We will use the same form, copies, and instances of the same form.

To do this, we will learn how to create our own collections, which is an object type in Access. We will learn how to track the individual forms with a key value so we can tell which one is which. We will learn how to cascade them when they open, so they open layered. All kinds of cool stuff like this in today's class.

Today's class follows Access Developer 46, of course. It is not a hard prerequisite, but I do cover a lot of stuff that I have covered in previous developer courses. As always, I always tell you my classes are designed to be followed one after the other. So I recommend you start at Beginner Level 1 and work your way all the way up.

Lots of developer-level VBA material is covered today, and that is why I generally say do not skip levels.

It is currently December 2024. So the closest retail version of Access to this one is going to be Access 2024. I am currently using an Access 365 subscription, which is what I recommend. That way you always have the latest and greatest version and you do not have to worry about upgrading.

The material covered in today's class, however, should go back at least to Access 2007, maybe earlier, but that is probably the earliest version I would say that it would work with.

If you have questions regarding the material covered in today's class, just scroll down to the bottom of the page that you are watching the lessons on and you can post your questions there. Also, take a minute to read through any other questions that may have been posted, as your question may have already been answered.

Make sure you subscribe to this page to get notified of any other questions or comments that are posted for this class. Also, make sure to visit my Access forum where you can post questions about anything else related to Access. You can join in conversations with me, the moderators, and other Access students.

Now, let's take a closer look at exactly what is covered in Access Developer 47.

In Lesson 1, we are going to learn how to create multiple instances of the same form. So if you are on your customer list form, for example, you can open up multiple copies of the customer form with different customers in it. Or you can open up multiple products or multiple orders or multiple whatever you want. We will learn about collections, how to create collections, and how to add objects to a collection.

In Lesson 2, we are continuing with multiple form instances. I am going to show you how to list all of the open forms, make sure the collection is initialized to avoid errors, and deal with closed forms. I will show you how to track forms with a key value. We are going to make our own key value. We are not going to use the built-in one. It is better. Trust me.

In Lesson 3, I am going to show you how to not open the same customer form twice, which could cause write errors. If you open up Richard Rost twice and edit one, then edit the second one, it could cause issues. So we will prevent that. Then we will see how to give a specific form focus. If you want to loop through the forms and find a specific one you can. I will show you how to cascade the forms when you open them, so if you open four in a row, they cascade nicely, just like pretty little windows should.

In Lesson 4, we are going to finish up working with collections and multiple form instances. We are going to start by killing the collection and resetting it if we detect that the user has closed all the forms. We want to keep our memory nice and tight. Then we are going to take all of our code and we are going to drop it into ChatGPT and see what it has to think about it, what suggestions it may make, things to do better, things I might have missed. So we will see what that is all about in just a minute.

Quiz

Q1. What is the main topic covered in Access Developer 47?
A. Opening multiple instances of the same form
B. Creating reports from queries
C. Linking tables from external data sources
D. Automating emails from Access

Q2. Which object type in Access will you learn to create and use for tracking form instances?
A. Table
B. Query
C. Collection
D. Macro

Q3. What is a benefit of using your own key value to track forms in a collection?
A. It prevents Access from crashing
B. It can be customized and is better than built-in options
C. It improves printing functions
D. It eliminates the need for VBA

Q4. What technique is taught to visually organize multiple open forms?
A. Maximizing all forms
B. Hiding unnecessary fields
C. Cascading the forms
D. Locking the forms in place

Q5. What problem can occur if you open the same customer form twice?
A. Duplicate printing
B. Loss of network connectivity
C. Write errors due to conflicting edits
D. Slow startup of Access

Q6. What should you do if all instances of the form have been closed?
A. Save the collection for later
B. Reset or kill the collection to free memory
C. Keep the collection open indefinitely
D. Export the collection to Excel

Q7. What version of Access is the course demonstrated in?
A. Access 2010
B. Access 365 subscription
C. Access 2000
D. Access 2016

Q8. What final step is shown for improving your code at the end of the class?
A. Importing into Excel for analysis
B. Posting the code on a forum for feedback
C. Submitting code to ChatGPT for suggestions
D. Running the code through the built-in debugger only

Q9. According to the video, what is recommended regarding which course level to start at?
A. Jump straight to Developer Level 47
B. Start at Beginner Level 1 and work up
C. Only watch Advanced topics
D. Skip all Beginner videos

Q10. What does the instructor recommend if you have questions about the class content?
A. Email him directly
B. Post questions at the bottom of the lesson page and check for other answers
C. Only ask questions in live sessions
D. Wait until the next class to ask

Answers: 1-A; 2-C; 3-B; 4-C; 5-C; 6-B; 7-B; 8-C; 9-B; 10-B

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Access Learning Zone is all about working with multiple instances of the same form in Microsoft Access, which can really come in handy if you ever need to view several records at the same time. For example, you might want to have four different customers open side by side, or perhaps you are managing orders and want to see the details for several products all at once. Rather than creating separate forms for each, Access gives us the power to open the same form multiple times, each showing different data.

To accomplish this, I will show you how to work with your own collections in Access. Collections allow us to keep track of each open form instance, and we will use key values to identify which instance represents which record. I will cover how to cascade the open forms so that when you open several, they are laid out neatly, one after the other, rather than being stacked directly on top of each other.

This material picks up where Access Developer 46 left off. While it is not a strict requirement to complete previous lessons beforehand, these courses do build on each other, and I recommend following along with the sequence if you want the full experience. As always, I encourage students to start at Beginner Level 1 and progress from there for the best learning outcome.

We will be covering advanced VBA topics in this lesson, and that is another reason I do not recommend skipping levels. Having a firm foundation will make understanding this material much easier.

I am currently using an Access 365 subscription, which I think is the best option since you always have the most current version. The concepts I present in this class should still work with versions going back as far as Access 2007, but that is the oldest version I would suggest using for this particular topic.

If you have questions about anything I go over today, you can always scroll to the bottom of the lesson page and post your questions there. Be sure to browse through the other questions, as you might find your answer among them. Subscribing to this page will ensure you stay updated on any new information or answers posted. Also, you can take advantage of my Access forum to ask questions on any topic you like, and join conversations with myself, the moderators, and other students.

Now, let me walk you through the specific content you'll find in Access Developer 47.

In Lesson 1, we will begin with the basics of creating multiple instances of the same form. For example, you could be looking at your customer list and decide to open several individual customer forms at once. This could work for products, orders, or any other type of record. I will introduce collections and how to use them to manage your open forms.

Lesson 2 will expand on that by showing you how to list all the currently open forms. You will learn the importance of initializing your collection to prevent errors and how to handle forms that get closed by the user. I will also show you how to assign your own key values to each form instance to make tracking easier, rather than relying on the built-in ones. Trust me, the custom solution works better.

Lesson 3 deals with some important potential problems, such as keeping users from opening the same customer twice, which could lead to editing conflicts. Imagine opening Richard Rost twice, editing both, and running into confusion or errors. We will prevent that. I will also cover how to bring a particular form to the front so you can give it focus, and demonstrate how to cascade multiple forms so that each new form opens slightly offset from the previous one, making them easier to manage.

Lesson 4 finishes up the topic of managing collections and multiple form instances. We will look at how to reset the whole collection if all the forms are closed, helping to keep your application running efficiently with minimal memory use. To wrap things up, we will take all our code and run it through ChatGPT to see what suggestions or improvements it comes up with and discuss any changes it recommends.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Opening multiple instances of the same form
Creating and managing collections in Access
Adding forms to a collection
Tracking form instances with a key value
Listing all open form instances
Initializing collections to avoid errors
Handling closed forms in collections
Preventing duplicate form instances for same record
Giving a specific form focus from a collection
Cascading multiple form windows when opened
Resetting and killing collections when forms are closed

Article

In this tutorial, we are going to explore how to open multiple instances of the same form in Microsoft Access using VBA. This technique is extremely useful if you want to work with, for example, several customers at once, compare information between different orders, or view multiple products at the same time. Rather than opening just one form and reusing it for different records, you can have several copies of the same form on screen, each showing a different record.

To achieve this, we will use VBA to create and manage multiple instances of a form. Each instance will be tracked using a collection, which is a special type of object in Access that allows you to group objects together in memory. We will also assign a unique key value to each form instance so you can easily identify and manipulate them.

For example, imagine you have a form called frmCustomer that displays customer information. Normally, if you click to open a customer, Access will just open one form and you will have to close it to open another record. With this technique, you can keep several customer forms open at once, each showing a different customer.

First, you will need to learn about collections in VBA. A collection is a group of objects that you can add to, remove from, and loop through. Collections are perfect for keeping track of several open forms. In our case, we will keep a collection in a public module that stores references to all the open form instances.

Here is an example of how you can declare a collection in a standard module:

Public colCustomerForms As Collection

Then, you will need to initialize the collection if it does not already exist. This step avoids errors if you try to use the collection before it has been set up. You can write a small procedure like this:

Sub InitializeCustomerForms()
If colCustomerForms Is Nothing Then
Set colCustomerForms = New Collection
End If
End Sub

When you open a new form for a customer, you will want to create a new instance of the form and add it to the collection. Each item in the collection will be keyed to a unique value, usually the primary key of the record being displayed, such as CustomerID. Here is how you would do that in VBA:

Sub OpenCustomerForm(CustomerID As Long)
Dim frm As Form_frmCustomer
Call InitializeCustomerForms
' Check if this customer is already open to prevent duplicates
Dim item As Variant
For Each item In colCustomerForms
If item.OpenArgs = CustomerID Then
item.SetFocus
Exit Sub
End If
Next item
' Open a new instance if not already open
Set frm = New Form_frmCustomer
frm.OpenArgs = CustomerID
frm.Visible = True
colCustomerForms.Add frm, CStr(CustomerID)
CascadeForms
End Sub

By using the collection key as the CustomerID, you prevent the same record from being opened more than once. If a form for a given customer is already open, the code will bring it to the front instead of opening a duplicate.

You will also need to make sure that when a customer form is closed, it gets removed from the collection. In your form's close event, you can add code like this:

Private Sub Form_Close()
On Error Resume Next
colCustomerForms.Remove CStr(Me.OpenArgs)
End Sub

This keeps your collection up to date and ensures that closed forms do not linger in memory.

To enhance usability, you can also make your forms cascade when they open. This means each new form appears slightly offset from the previous one, making them easy to see and switch between. You can create a procedure like this:

Sub CascadeForms()
Dim i As Integer
Dim item As Variant
Dim topPos As Integer, leftPos As Integer
topPos = 100
leftPos = 100
i = 0
For Each item In colCustomerForms
item.Move leftPos + (i * 30), topPos + (i * 30)
i = i + 1
Next item
End Sub

Collections are powerful because they allow you to keep track of all open instances of your forms and perform operations on them as a group or individually. For example, you can loop through them to close all open customer forms at once or to bring a specific form into focus.

One more thing to watch out for is keeping your memory clean. If a user closes all the open forms, it is a good idea to reset the collection. You can check if the collection is empty and set it to Nothing:

Sub CleanUpCustomerForms()
If Not colCustomerForms Is Nothing Then
If colCustomerForms.Count = 0 Then
Set colCustomerForms = Nothing
End If
End If
End Sub

You can call this procedure after removing a form from the collection in the Form_Close event.

This approach makes your Microsoft Access applications much more user friendly, especially in situations where users need to view and work with several records side by side. You avoid potential editing conflicts by not letting the same record open twice, you can cascade your forms for easy navigation, and you maintain control over the forms that are open through the collection.

If you want to take it a step further, you could even automate checking your code with tools such as ChatGPT to look for improvements or suggestions. However, mastering the technique of managing multiple form instances with collections will go a long way toward making your Access databases more flexible and professional. This technique works with recent versions of Access, including Access 2024 and Access 365, but it should also work in versions going back as far as Access 2007. If you are comfortable with VBA and have a good understanding of forms, collections, and working with object references, you should be able to implement this in your own projects right away.