|
By Jim DeMarco view Bio
Demo File: Using Custom Collections in Microsoft Access download sample zipped MDB file Print this Page
By Jim DeMarco, Director of Application Development,
The VB/VBA Collection object has proven a useful tool for storing groups of things. They can be related objects or data, or they can be completely unrelated. If you find yourself in need of a place to collect objects of a particular type however the Collection object does not by default prevent you (or another developer) from dropping an item of a different type into your collection. For this “type-safe” collection you will need to design a custom collection class to handle these duties. In the .NET world Microsoft has provided developers access to type-safe Datasets. These provide us with the comfort of knowing that no one can add an item to the Dataset unless it conforms to the Dataset’s XML schema. When we access this Dataset it will contain only the items described in the schema and will not break our application. Using a custom collection class we can provide some of that type safety to our own in-memory objects.
What is a custom collection? Aren’t all collections “custom”? A custom collection is a collection that can only contain objects of a custom class of your design. Apple objects will be maintained in an Apples collection object, Person objects in a Persons collection, etc.
If you are familiar with the Collection object you know that it contains only a few built in methods for controlling the items stored within.
Collection Object Methods:
Add(Item, [Key], [Before], [After]) – adds an item to the collection.
Count – returns the number of items in the collection; returns 0 if the collection is empty
Item(Index) – returns an item from the collection
Remove(Index) – removes an item from the collection
These are the base methods we’ll work with in our custom collection class but as you see there are a few useful tools missing. A while back I co-wrote an article with list member Susan Harkins on extending the collection object by adding these tools. I won’t rehash that article but suffice it to say that we’ll be adding an Exists method that allows us to see if an item is already in a collection and a Clear method that allows us to empty the collection. But since we are designing a custom collection to deal with custom business objects we can also add properties and methods that relate to our collection of these objects. How about a custom collection called Expenses that contains objects from an Expense class? This collection class could have a property that returns the total value of all Expenses in the collection or a method that returns the total value of a particular type of expense.
Let’s start with the simple idea of contact tracking. We’ll first create a contact class, cContact. I’m assuming you have some familiarity with creating and using classes so I won’t be explaining the minutiae of the base cContact class.
The completed collection class
Add a new class module to your project (Insert | Class module) and name it cContact. Add the
following code:
|
Dim m_sFullName As String Dim m_sPhoneNumber As String Dim m_iRating As Integer ' Public Property Get FullName() As String FullName = m_sFullName End Property Public Property Let FullName(newFullName As String) m_sFullName = newFullName End Property Public Property Get PhoneNumber() As String PhoneNumber = m_sPhoneNumber End Property Public Property Let PhoneNumber(newPhoneNumber As String) m_sPhoneNumber = newPhoneNumber End Property Public Property Get Rating() As Integer Rating = m_iRating End Property Public Property Let Rating(newRating As Integer) m_iRating = newRating End Property |
This class tracks contact name and phone number as well as the contact rating (the value of the sales prospect) with 5 being the greatest prospect and 1 being the lowest. Since we want the ability to track more than one at a time we need to build a collection class that holds only cContact items and has properties and methods that relate to a collection of cContact items.
Well start by creating a new class and naming it cContacts. Then we’ll add a module level variable to hold our internal collection, m_PrivateCollection. This collection will drive most of the functionality of our collection class. Add the following code to the collection class module (if you’re pasting this into your code module watch for line wrapping):
|
Private m_PrivateCollection As Collection ' ' Add a new cContact item to the collection Public Function Add(FullName As String, PhoneNumber As String, _ & Rating As Integer) As cContact Dim newItem As New cContact Dim Key As Variant
With newItem .FullName = FullName .PhoneNumber = PhoneNumber .Rating = Rating Key = .FullName End With
' add to the private collection m_PrivateCollection.Add newItem, Key Set Add = newItem Exit_Function: Set newItem = Nothing Exit Function End Function Public Sub Remove(Index As Variant) m_PrivateCollection.Remove Index End Sub Function Item(Index As Variant) As cContact Set Item = m_PrivateCollection.Item(Index) End Function Property Get Count() As Long Count = m_PrivateCollection.Count End Property Private Sub Class_Initialize() Set m_PrivateCollection = New Collection End Sub Private Sub Class_Terminate() Set m_PrivateCollection = Nothing End Sub |
The Remove, Item, and Count methods wrap the VBA Collection class functionality without modification. You’ll notice a huge difference in the Add method however. Let’s take a look at the differences in the signature of the Collection object’s Add method and our cContacts collection class Add method.
|
oVBCollection.Add( Item, [Key], [Before], [After]) oContacts.Add(FullName, PhoneNumber, Rating) as cContact |
First and foremost from our collection class we are returning a cContact object vs. the VB Collection’s Variant. This allows us to assign a cContact item property values as we add them to a collection if need be (more on that later). Next look at the arguments being passed into the method. The VB Collection will take an Item of any type whereas our cContacts class wants values to fill the properties of a cContact item specifically. We’ve also removed the option to pass in the Key value as we’re assigning it internally in the Add method. (This is purely at your discretion. If you need more (or less) control over Key value add it as an optional argument to your Add method.)
I’d like to sidetrack for a moment to show you an alternate footprint for the collection class Add method. It is simply:
|
Add(Contact as cContact) |
Using this technique you could instantiate an object from the cContact class and pass it into the Add method. I don’t subscribe to this method often because it necessitates the creation and use of an additional variable (in this case of type cContact) causing us to write more code into our UI to use the collection. There will be many times where we write a collection class and plan on using aggregate functions of the class and should not need to create individual items before adding to the collection. If we need to pull an item out of the collection we can then create a variable of the base class type to hold that instance but only in that case.
Let’s write a simple routine to fill the collection with contact information and see that it works. Copy this code into a standard code module:
|
Private m_oContacts As cContacts ' Sub FillContacts() Set m_oContacts = New cContacts m_oContacts.Add "Jim DeMarco", "555-555-5555", 5 m_oContacts.Add "Bill Smith", "444-444-4444", 4 m_oContacts.Add "Jill Jones", "333-333-3333", 5 m_oContacts.Add "Harry Harris", "222-222-2222", 3 m_oContacts.Add "Mark Marx", "111-111-1111", 2
Debug.Print m_oContacts.Count Set m_oContacts = Nothing End Sub |
Set a break point on the Debug.print line, run the code (press F5), and type the following in the Debug Window (Ctrl + G):
?m_ocontacts.Item("harry harris").rating
The number 3 should appear. “So what?” you might ask. We can do that with the standard VBA Collection object. Now let’s add some functionality to the collection class.
As I mentioned earlier I have a couple of standard methods I add to any collection I’m using, Exists and Clear. Copy the following code into the cContacts class and then we’ll get to started putting the collection class to work:
|
' Remove all items from the collection Public Sub Clear() Set m_PrivateCollection = New Collection End Sub Function Exists(strKey As String) As Boolean 'returns true if the key value (not index) exists in the collection Dim objTemp As cContact On Error GoTo Exists_Err
Set objTemp = m_PrivateCollection.Item(strKey) Exists = True Exit_Function: Set objTemp = Nothing Exit Function Exists_Err: If Err.Number = 5 Then Exists = False Else 'further error handling here for other error types MsgBox "Error " & Err.Number & " " & Err.Description, vbOKOnly + vbExclamation, "Error" End If Resume Exit_Function End Function |
These are useful tools when dealing with a collection and as I mentioned are a standard part of my collection class template. In fact, now would be a good time to make a copy of this class and save it as a collection class template. The only method that will ever change is the Add method’s signature and property assignments within the method and the Item method’s return type. Once we have this framework in place we can begin writing methods specific to the collection.
I’d like to know how many contacts I have in my collection with a given rating. I added this to the class as a read-only Property but it could just as easily been defined as a Method (Function).
|
Public Property Get NumContactsWithRatingOf(Rating As Integer) As Long Dim lngReturn As Long Dim i As Integer Dim lngCount As Long lngCount = m_PrivateCollection.Count For i = 1 To lngCount If m_PrivateCollection.Item(i).Rating = Rating Then lngReturn = lngReturn + 1 End If Next i NumContactsWithRatingOf = lngReturn End Property Modify the FillContacts routine as follows, set a break point at the Debug.Print line, and run the code. Sub FillContacts() Dim i As Integer Set m_oContacts = New cContacts ‘…previous code removed for space For i = 5 To 1 Step -1 Debug.Print "Rating of " & i & ": " _ & m_oContacts.NumContactsWithRatingOf(i) Next i
Debug.Print m_oContacts.Count Set m_oContacts = Nothing End Sub |
The Debug Window should now display:
Rating of 5: 2
Rating of 4: 1
Rating of 3: 1
Rating of 2: 1
Rating of 1: 0
Great but now I’d like to see what contacts are in each group. We’ll add one more method to our class that returns a collection containing the contacts that have a given rating.
Type or copy the following code into the collection class module:
|
Public Function GetContactsWithRatingOf(Rating As Integer) As _ Collection Dim ocolReturn As Collection Dim i As Integer Dim lngCount As Long Set ocolReturn = New Collection lngCount = m_PrivateCollection.Count For i = 1 To lngCount If m_PrivateCollection.Item(i).Rating = Rating Then ocolReturn.Add m_PrivateCollection.Item(i) End If Next i
Set GetContactsWithRatingOf = ocolReturn Set ocolReturn = Nothing End Function Let’s modify the FillContacts routine and use this new method. Sub FillContacts() Dim i As Integer Dim ocolBestProspects As Collection Set m_oContacts = New cContacts ‘…previous code removed for space Set ocolBestProspects = m_oContacts.GetContactsWithRatingOf(5) Debug.Print "# Best Prospects: " & ocolBestProspects.Count For i = 1 To ocolBestProspects.Count Debug.Print ocolBestProspects.Item(i).FullName Next i
Debug.Print m_oContacts.Count Set ocolBestProspects = Nothing Set m_oContacts = Nothing End Sub |
We’ve added a variable to hold the collection returned by the cContacts GetContactsWithRatingOf method and we’re checking the count and displaying the full names of those contacts with a rating of 5. Run the code and take a look at the result in the Debug window.
# Best Prospects: 2
Jim DeMarco
Jill Jones
There is one last point to be made about the cContacts collection and the GetContactsWithRatingOf collection method. Each contains and can return a cContact item. In our last example we accessed the contact’s full name using an explicit declaration through the collection item:
|
ocolBestProspects.Item(i).FullName |
We can assign that item to a variable of cContact type and access the properties and methods directly. Let’s make one last change to the FillContacts routine to see this technique. We’ll add a variable of type cContact and assign it to the return of a call to a collection’s Item property (either collection, cContacts itself, or in this case the collection created in a call to GetContactsWithRatingOf). The final version of FillContacts now looks like this:
|
Sub FillContacts() Dim i As Integer Dim ocolBestProspects As Collection Dim oBestContact As cContact Set m_oContacts = New cContacts ‘…previous code removed for space Set ocolBestProspects = m_oContacts.GetContactsWithRatingOf(5) Debug.Print "# Best Prospects: " & ocolBestProspects.Count For i = 1 To ocolBestProspects.Count Debug.Print ocolBestProspects.Item(i).FullName Next i
'get the first contact item Set oBestContact = ocolBestProspects.Item(1) Debug.Print "The contact name is :" & oBestContact.FullName
Debug.Print m_oContacts.Count Set oBestContact = Nothing Set ocolBestProspects = Nothing Set m_oContacts = Nothing End Sub |
Note that this assignment is a just a pointer back to the cContact item stored in the collection. Any change to data here will be reflected within the collection and vice versa so use care when making changes.
We could also assign values to a cContact object when adding to a cContacts collection:
|
Dim oMyContact As cContact Dim oContacts As cContacts Set oContacts = New cContacts Set oMyContact = oContacts.Add(“Jim Smith”, “555-444-3333”, 4) Debug.Print oMyContact.FullName |
Validating Our Data
Now that we’ve got a working collection class you may have noted that we have not provided much in the way of data validation and custom error generation. You handle this at the base class level the same way you would normally. Let’s tighten up the Rating Property and limit the range of values that it can contain. I’d like to limit the range of values that can be stored in the Rating Property to a value from 1 to 5. Make the following changes to the Rating Property Let function:
|
Public Property Let Rating(newRating As Integer) If newRating >= 1 And newRating <= 5 Then m_iRating = newRating Else Err.Raise 10000, "cContact:Rating", "Invalid value" End If End Property |
We can test this out in the Debug window like this:
Set x = New cContact
x.Rating = 6
Set x = Nothing
Press Enter after typing each line to see the results of adding the validation check and raising the custom error. This code will be called from inside our collection class in the Add method when we set the properties of the internal cContact item:
|
Sub CheckRating() Set m_oContacts = New cContacts m_oContacts.Add "John Lennon", "123-456-7890", 6
Set m_oContacts = Nothing End sub |
Our default error message will appear when this code is run. Now we can add error handling to deal with this issue. In a standard code module add the following routine:
|
Sub CheckRating() On Error GoTo Err_Handle Set m_oContacts = New cContacts m_oContacts.Add "John Lennon", "123-456-7890", 6 Exit_Sub: Set m_oContacts = Nothing Exit Sub Err_Handle: If Err.Number = 10000 Then MsgBox "You have entered an invalid rating value", vbOKOnly, _ & "Rating" GoTo Exit_Sub End If End Sub |
If your custom error handler does not display some versions of Access require you to set “Break on Unhandled Errors” in the Tools | Options menu on the Advanced tab’s Error Handling section.
 |
We’ve created a collection of custom objects that has some of the type safety available to us in .NET technologies. By using a collection of custom objects we can write aggregate and filtering functionality into our collection class. Any data validations and error handling done in the base class are still accessible. We now have a very flexible method that allows us to work with custom objects en masse.
Why might you use this technique? Although it’s most useful in unbound situations (particularly if you do any VB development) I’ve also found uses from within any Access application. For example it’s useful as a form or report manager. Imaging not needing to copy and paste your IsLoaded function from your utility library any longer. Just ask your cForms.Exists method if frmFoo is there. Want to know how long a form has been open? Ask the cForms.TimeOpen method to tell you.
This technique has proven useful in data entry tools I’ve developed where we are taking income and expense information from families and need to summarize and total it. When it came time to move the functionality from Access to VB it required nothing more than copying the classes from one app to the other.
Happy coding!
JD
Please contact the author at Jdemarco@hshhp.org to ask questions or report errors.
May be distributed as long as the copyright remains.