How to Use Collections in Microsoft Access VBA

Collections make up an important feature of Microsoft Access, because they allow you to make a list, or "collection" of items of various types with a key value for each one. This allows you to make a collection of strings, numbers, dates, and even objects. With your collection in memory, it is very simple, and much (much!) faster to retrieve these items on-the-fly in repetitive contexts that might be slow otherwise. Though arrays and collections have similar aspects, collections can be simpler and faster to implement in certain contexts.

Related Videos:
How to Use Recursive Functions in MS Access - Family Tree Example
VBA Mod Function with DoEvents: Prevent Not Responding Messages and Provide Feedback on Access Forms
How to Use Collections in Microsoft Access VBA
You are watching this video now!
How to Handle VBA Errors in MS Access
How to Use the Eval Function in Microsoft Access
How to Switch from One MS Access Database to Another

Interested in transforming your career or finding your next gig?

Want my team to do a project for you? Let's get to it!

Want to get access to premium content made just for you and have a chat with me? Find me on Patreon :

Demo of my BZ RDP Cloaker:

Want to work with me 1:1? Contact me today and book your free 20 min consultation!

Follow me on social media:

Get Microsoft Office including Access:

Got a YouTube Channel? I use TubeBuddy, it is awesome. Give it a try:

#dataengineering #msaccess #vba

[velbalakrishnan]

Excellent Teaching. Thumbs Up for U Sir. It is very usefull tips for me to grow in MSAccess VBA Programming.

[interestingamerican]

Thanks for that overview. I am learning Collections, using it to open forms in multiple instances and the keys are the Hwnd of the form.

[lascosasporestoslugares]

Excellent video sir! Thank you very much for your work. It is totally new for me.
The first collection that you populate is declared as "as New" Collection. During the exercise, you "DIMed" other collection variables "as Collection", without keyword "New".
What is the difference?

[satyabanukil]

Sir, Watched your videos on Arrays and Collection. Understand a little bit of it. But how to use them in Form level as all end users or data entry operators mostly work in forms.
2. Fews days ago you raised a question in fetching result from two fields by using code in form level reply to which has been submitted. If you get time, kindly look into that. Regards.

[adbs_ads]

Thanks for the video, but I'm very confused with what you're saying at 2:07. You seem to be pointing at the wrong arguments while you talk and I would like to know what the before and after really do in a practical way. Also, how should we Dim itm if we are using Option Explicit?

[artistryartistry]

Hi there, so in that "For Each itm in collectionk" statement, you don't have to first declare a variable for the "itm" counter?? I never knew that!!

[johnwayne]

If you add objects you can add also more then one value, in addition it allows you to change the object values! Changes cannot be done to collections filled with basic data types like strings etc.!

[jim]

Hi Sean, can the key be a date value? I’m wondering if possible to compare todays date to the key to display the item, either in a list box on a form or when a cmd button is clicked.

[mosjany]

How are you sir, , , , Kindly I am doing Invoicing db, , , , Now when it comes to payments, how can i knock off payments against Invoice, , to be able to get invoice balances if any?

[RungeCarl]

Please can you use "Option Explicit" in your code. It took me a while to understand what "itm" was.