Dealing with Collections and Arraylists in VBA/Access

I am trying to take a form, create a query from entries on the form, create an Arraylist from the query results and then compare that Arraylist to a Collection of objects to see if a member of the collection (with an attribute of same type as Arraylist) is on the Arraylist.  Is it possible to do this in Access?
Michael SullivanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Bill PrewIT / Software Engineering ConsultantCommented:
Without too many details, I'd say that is all doable.  Dictionary objects may be useful for the cross checking 'lists'.  As long as the number of items in a list isn't massive it sounds doable.  It's a fairly large solution, you may want to break this into smaller questions once you get some draft code and data details to share.


»bp
0
Michael SullivanAuthor Commented:
I started and have done a little bit  of the code.  I actually already wrote the whole thing in Java (using sqlite and SWT in Eclipse for the forms).  The data objects in VBA just seem "limited", but I have done a bit already.  I do appreciate the answer, as I didn't want to spend a whole bunch of time on this only to find that it ultimately isn't possible.  I will definitely be back with questions (and maybe some code) as I go.  Thanks VERY much for the advice.
0
Bill PrewIT / Software Engineering ConsultantCommented:
In addition to the native objects in VBA, you can use ArrayLists and Dictionaries form additional references.  In a late binding approach, things like this work and allow usages of these useful objects.

    Dim alist As Object
    Dim dict As Object
    Dim col As Collection
    
    Set alist = CreateObject("System.Collections.ArrayList")
    Set dict = CreateObject("Scripting.Dictionary")
    Dim col As Collection

Open in new window


»bp
0

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
Michael SullivanAuthor Commented:
Thanks for the advice.  I will be back if I run into issues.
0
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.