Use Classes to Enhance List and Combo Boxes in Access 97

Access's bound combo and list boxes are both flexible and easy to use. Simply assign a query or table as the RowSource of the list box, and Access automatically fills the list based on the contents of the query.

Compared to Visual Basic, however, Access unbound list boxes are surprisingly limited. It is more difficult than you would think simply to add an item to a list box programmatically. With Visual Basic you can simply do:

Me!lisURLs.AddItem "http://www.microsoft.com"

This code appends the value to the end of the list.

In Access you have two choices when working with unbound list boxes.

The most flexible method is to create a complex "list fill callback function" which Access itself calls to populate the list. This function must conform to a rigid format of argument types, and must support several specific actions, such as providing the number or rows and columns, initialization and termination, and so forth. For information on using list fill callback functions see the Access 97 help topic titled "RowSourceType Property (User-Defined Function)".

A simpler method is to use a RowSourceType of "Value List." The value list is a text string of values delimited by a semi-colon. In order to add an item to a value list listbox at runtime, you might use the following code:

Me!lisURLs.RowSource = Me!lisURLs.RowSource & ";" & Me!txtURLs

This example takes the current value of the RowSource property of the list box, appends a ";" character, and then appends the new value, which in this case is contained in a text box called txtURLs.

This is easier, but still limited. There is no simple way to remove an item from the list, or to sort it. In order to modify the list you must completely replace it, being sure to keep all your semi-colons in the right place.

The first goal of the class module is to add new functionality to the standard Access listbox. Modeling the interface after that of Visual Basic, which many Access programmers may be familiar with, I want to create the following methods: AddItem, RemoveItem and Clear. These will be the supported "methods" of the class.

Because of the drawbacks of storing the list information in the RowSource property of the list box itself, the class should maintain its own list of values. In my example I use the Access collection object to store the values. This is the class's "instance data."

Finally, I want to create a wrapper around a standard list box, keeping all of its native functionality, while adding new capabilities. I create a pointer to an actual list box on a form, and perform all work through that pointer. The class thus "encapsulates" a standard list box.

To create an Access 97 class module you cannot simply press the "New" button on the Modules tab of the database container. This creates a standard module. To create a class module you must explicitly use the menu option Insert, Class Module. By default, Access gives it the name "Class1."

As with other Access objects, it is important to think carefully about the name of your class module. Unlike standard modules, where the name of the module itself is relatively unimportant, you actually refer to the name of the class module when you create instances of objects based on that module. I will call the class "clsUnboundList" since its function is to provide a wrapper around unbound list and combo boxes.

The first thing the class needs is a place to store its "instance data", or the actual list of items that will go into the list box or combo box. In the class module's Declarations section I create a variable which holds the collection:

Private mcolItems As Collection

The variable is declared Private, since I only want users to modify the data in the collection by means of the Additem, RemoveItem, and Clear methods, or by internal "helper" functions within the class itself, which I will discuss later.

Notice the difference between module-level variables declared in a standard module versus those in a class module. The variables in a standard module occur only once in the entire application. Module-level variables in a class module, on the other hand, are maintained separately for each "instance" of the class which is created in code. This allows each instance of the clsUnboundList class to maintain a separate list of data for its list box or combo box.

The next module-level variable which needed is a pointer to an actual list box or combo box on a form:

Private mControl As Control

In order to use the clsUnboundList class in an application, the user first must create a variable of the "clsUnboundList" type, and then "instantiate" it. The variable could be created anywhere, such as in a standard module, or in the code behind a form, but the instance variable must remain in scope for the entire time that the form is open. A convenient place to declare an instance variable for a class object is in the Declarations section of a form:

Option Compare Database
Option Explicit
Dim moURLs As clsUnboundList

The module-level variable named "moURLs" will remain valid until the form closes. When the form closes the variable goes "out of scope", and the objects created from the variable are automatically destroyed.

Creating the module-level variable in this way does not actually "instantiate" the variable. It is simply a pointer that has the potential to hold an object of the clsUnboundList type. You must actually instantiate the variable at some time after the form opens. In addition, you must tell the class which list box or combo box to "wrap" or encapsulate. In the case of this class, a convenient place to do this is in the OnOpen event of the form:

Private Sub Form_Open(Cancel As Integer)
  Set moURLs = New clsUnboundList
  Set moURLs.Control = Me!lisURLs
End Sub

The first "Set" statement uses the "New" keyword to create a new instance of the clsUnboundList class and assign it to the module-level moURLs variable in the form. When you instantiate a variable from a class, any code in that class's Initialize event is fired. In the case of the clsUnboundList class, I need to prepare the mcolItems collection variable in the class:

Private Sub Class_Initialize()
  Set mcolItems = New Collection
End Sub

An alternative way to instantiate a variable is to use the following syntax:

Dim moURLs As New clsUnboundList

When you use this method, you do not need to do an explicit "Set" statement to create a new instance. Any reference to the class's methods or properties automatically instantiates the variable. The drawback to this method is that Access must include additional overhead checking throughout your code to determine whether or not the variable has already been instantiated whenever you refer to the variable. Using the first method shown above makes your intentions clear, and is the recommended style.

The second "Set" statement shown in the form's OnOpen event passes a pointer to an actual list box on the form, called "lisURLs" into the class. It does this by setting the "Control" property of the class to the list box.

Access classes include three kinds of specialized procedures known as "property procedures." In addition to standard Function and Sub-type procedures, your classes can include Property Set, Get, and Let procedures. These procedures are used to assign or retrieve values to the properties of the class.

Property Get procedures are used to retrieve values from a class. Property Let procedures are use to set the value of a class's property. You can use the Get and Let procedures to validate input, or to provide additional processing as a result of setting or retrieving a value.

The clsUnboundList example does not require any Property Let or Get procedures, but these are important and useful constructs which you should be familiar with.

The clsUnboundList class does use a Property Set procedure. The Property Set procedure is used to assign an object to a variable in a class. In the example code shown above, in the OnOpen event of the form, the user assigns a particular list box to the Control property of the moURLs object:

Set moURLs.Control = Me!lisURLs

When this code executes, Access triggers the following Property Set procedure in the clsUnboundList class:

Public Property Set Control(Control As Control)
  If (Not TypeOf Control Is ListBox) And (Not TypeOf Control Is ComboBox) Then
    MsgBox "Please assign only list boxes or " & "combo boxes to this property"
  Else
    Set mControl = Control
    mControl.RowSourceType = "Value List"
  End If
End Property

Since I want the user to be able to use either a list box or a combo box with the clsUnboundList class, I declare the parameter to the Property Set Control procedure "As Control" rather than "As Listbox" or "As ComboBox". This makes the property procedure more flexible, but I must then use some additional error trapping to be sure that the user does not supply the wrong type of a control to the property, such as a text box or a command button. The Property Set procedure checks to ensure that the passed argument is a list box or combo box, and then assigns the passed argument to the class's private module-level "mControl" variable.

Notice that users outside the class aren't allowed to refer to the mControl object directly. They can only use the property procedures and other procedures that I provide through the class. Code within the class is able to refer to mControl directly however, as in the example above where the passed control's RowSourceType is explicitly set to "Value List."

Since mControl just contains a pointer to an actual list box or combo box, changing the RowSourceType of the mControl variable automatically has the effect of changing the same property on the real list box or combo box.

Most of the "user interface" to the clsUnboundList class is not provided through property procedures. Instead the class contains public procedures which can be accessed via an instance variable created from the class. This is how the AddItem, RemoveItem, and Clear "methods" of the class work.

The declaration for the AddItem method in the clsUnboundList class looks like this:

Public Sub AddItem(varItem As Variant)

The sub is made public so that code outside of the class itself can call the procedure. The argument varItem is a variant so that the user can pass strings, or numbers to the procedure without having to do an explicit type conversion.

The caller uses the AddItem method by referring to the object variable he instantiated earlier:

moURLs.AddItem "http://www.fmsinc.com"

Notice that this syntax fulfills the goal we originally established of making the Access listbox behave more like a Visual Basic unbound list box. You can repeatedly add items to the list box using this simple syntax instead of using a listfill callback function or maintaining your own value list.

How do the items actually get added? Look at the code in Listing 1.

The first thing the code in the AddItem procedure does is to check its private mcolItems collection to see if the item has previously been added. You can provide any behavior you want for your listbox AddItem routine. This procedure simply ignores duplicate values, but you could generate an error, or even allow duplicates if you like.

If the value passed does not already exist, then the following statement adds the value to the internal collection:

mcolItems.Add varItem

The Collection type is not new to Access 97. It first appeared in Access 95. I am using a collection to store the class's instance data, but this could just as easily have been done with an array, or some other method.

Adding the item to the collection is fine, but how does that get the updated list of items into the list box or combo box?

The AddItem procedure, and several other methods of the clsUnboundList class, make use of a simple "helper" function called ConcatRowSourceStrings (see Listing 2.) This function simply loops through the items in the mcolItems collection, building up a new RowSource string by separating the values with a semi-colon, and returns the entire string as the function return value. The AddItem method just uses the string returned from the function as the new RowSource of the encapsulated list box or combo box:

mControl.RowSource = ConcatRowSourceStrings()

Once again, since the private mControl variable has been set to the value of a real combo or list box on the form, changing one of its properties actually changes the same property on the original control. So setting the RowSource property of the private mControl variable also changes the RowSource property of the original listbox. Access automatically displays the updated list.

The RemoveItem method works similarly to the AddItem method. The basic idea is to remove the item from the class's private mColItems collection, and then recreate the RowSource string from the remaining values using the ConcatRowSourceStrings function.

The only wrinkle here is that I am trying to emulate Visual Basic's RemoveItem function, which takes a numeric index as its argument. Both VB's and Access's listbox items are indexed starting with zero, while collections are indexed starting with one. Therefore in order to make our RemoveItem method work like the VB method I must assume that the user is passing a zero-based index, and offset the value by one when referring to the one-based collection:

Public Sub RemoveItem(intItem As Integer)
  Dim intRemoveItem As Integer
  'to emulate VB's RemoveItem method, assume a zero-based index
  'rather than a 1-based index
  intRemoveItem = intItem + 1
  If intRemoveItem >= 1 And intRemoveItem <= mcolItems.Count Then
    mcolItems.Remove intRemoveItem
    mControl.RowSource = ConcatRowSourceStrings()
  End If
End Sub

Notice there is also a small amount of validation done here to ensure that the index item that is passed is actually a valid item number. The user can remove an item from the list box with the following code:

moURLs.RemoveItem 2

The Clear method is even simpler:

Public Sub Clear()
  Set mcolItems = Nothing
  Set mcolItems = New Collection
  mControl.RowSource = ""
End Sub

Rather than looping through the mcolItems collections and removing the items one at a time, this code simply recreates the collection from scratch and explicitly sets the mControl's RowSource property to a zero-length string. As with the AddItem and RemoveItem methods, using the Clear method in code is quite straightforward:

moURLs.Clear

So far we have simply been adding functionality from Visual Basic to Access's unbound list and combo boxes. What if we want to add completely new features? The beauty of using class modules to encapsulate standard Access objects is that you can add as much or as little new behavior as you like. You can customize the control to meet your particular needs. To get you started on customizing the clsUnboundList class I have added two completely new methods: RemoveSelected and Sort.

The RemoveSelected method lets you easily remove the item that is currently selected in a list box. If you are using a simple or extended multi-select listbox, the RemoveSelected method automatically removes all selected items. Using the RemoveSelected method is simple:

moURLs.RemoveSelected

The code for the RemoveSelected method is shown in Listing 3. This method must behave differently if the encapsulated control is a listbox than if the control is a combo box. Combo boxes do not have a MultiSelect property, and if you try to refer to it in code you will receive a run-time error. If the control is a listbox, the behavior must differ depending on if the MultiSelect property is set to zero (standard list box) or one or two (simple or extended multi-select styles.)]

The code for listboxes uses another "helper" function:

Private Sub RemoveFromCollection( varItem As Variant)
  Dim intCounter As Integer
  For intCounter = 1 To mcolItems.Count
    If mcolItems.Item(intCounter) = varItem Then
      mcolItems.Remove intCounter
      Exit For
    End If
  Next intCounter
End Sub

Notice that the RemoveFromCollection helper function is declared as a private procedure. Unlike the public methods of the clsUnboundList class, this procedure is only intended to be called from other procedures within the class itself. The code simply loops through the mcolItems collection and removes the value matching the varItem argument.

If the listbox does not use one of the MultiSelect styles, then the RemoveSelected method simply removes the currently-highlighted item in the listbox:

RemoveFromCollection mControl.Value

If this is a MultiSelect listbox, then the code must delete each of the selected items in turn:

For Each varCurItem In mControl.ItemsSelected
  RemoveFromCollection mControl.ItemData(varCurItem)
Next varCurItem

The code for combo boxes is slightly different, and it illustrates an interesting point about using class methods. The class itself can use the same public methods as external callers:

Me.RemoveItem mControl.ListIndex

I have previously discussed the public RemoveItem method of the class, which takes a numeric zero-based index for a listbox or combo box and removes the corresponding one-based item from the private mcolItems collection. Here the RemoveSelected method calls the RemoveItem method on itself (using the Me syntax familiar to Access Basic programmers when used in code behind forms.) The ListIndex property of a combo box refers to the item that is currently selected in the list (the one we want to remove.) Since the ListIndex property is already zero-based, it fits in perfectly with the existing RemoveItem method. Since the method is available for others to use, we might as well use it ourselves!

Finally the Sort method takes an existing listbox or combo box and sorts its contents alphabetically. Using the Sort method in your code is as simple as can be:

moURLs.Sort

The Sort method makes use of a standard QuickSort function, called QSArray(), which is designed to work on arrays rather than collections. The Sort method writes the values in the mColItems collection into a temporary array, sorts the array, and then writes the sorted values back into the collection (see listing 5.) After sorting the collection, the RowSource of the encapsulated control is recreated with the ConcatRowSourceStrings function.

One thing to consider is which of these types of "helper" functions belong in the class itself, and which might be better off in a separate standalone module. The advantage of including the helper functions such as QSArray() directly in the class is that this makes the class totally self-sufficient. You can copy the class into any Access database and use it without having to worry about any required functions that might be found in other modules. The disadvantage is that the code is essentially duplicated for each instance of the class.

We have already seen simple examples of using AddItem, RemoveItem, Clear, and Sort in code. Except for the housekeeping business of declaring the instance variable, and assigning a control to it in the form's OnOpen event, using the class methods is quite transparent in your code. You can make use of the fairly complex additional behavior encapsulated in these simple-sounding methods to do a number of other interesting things.

For example, the sample database LBCLASS.MDB includes several sample forms illustrating some interesting techniques. One shows you how to create parallel list boxes on a form and transfer items from one to another the way the built-in Access "wizard" forms do. Another example shows how to simplify the OnNotInList code of an unbound combo box:

Private Sub cboAddItem_NotInList (NewData As String, Response As Integer)
  If MsgBox("The value " & Chr(34) & NewData & Chr(34) & " is not on the list. " & _
    "Would you like to add it?", vbQuestion + vbYesNo, "Combo Test") = vbYes Then
    moUnboundCombo.AddItem NewData
    Response = acDataErrAdded
  Else
    Response = acDataErrContinue
  End If
End Sub

Although the clsUnboundList class works well for relatively small amounts of data, there are limitations in its current design. Because I am making use of the control's standard RowSource property, the number of items that can fit in a list is limited to about 2000 bytes. I attempted to make a version of the clsUnboundList class that would provide its values using the Access listfill callback function method I discussed earlier, but Microsoft has confirmed that the RowSourceType property of a combo box or list box cannot refer to a property of a class. It must be a function in a standard module or in the code behind a form.

One drawback of encapsulating a control into an object variable is that it can sometimes be confusing to look at code that sometimes refers to the listbox or combo box itself, and sometimes refers to the a method or property of the object variable:

Dim strValue As String
strValue = Me!lisURLs.ItemData(0)
moURLs.AddItem "new item"

Me!lisURLs refers to the actual list box, while moURLs is the object variable of the clsUnboundList type which has encapsulated that list box. One solution is to create "shadow" properties and methods in the class which correspond to the standard properties in the list box. For example, you could create a Visible, or a Left or BackColor property for your class. Setting the property for the class object would simply pass the value on to the encapsulated control. Whether this is useful behavior for your class, or is just overkill, is something only you can decide.

There are other ways in which the clsUnboundList class could easily be enhanced. For example you could add a method to retrieve values from a table or query, or to write the values out to a table. You could allow for multi-column list boxes, or provide a "search" function.

I have barely touched on some of the new capabilities that Access's new class modules provide. For instance you can create class objects which contain collections of other objects. This lets you create essentially your own object hierarchy. I have been discussing the use of classes with unbound list and combo boxes, but there is no reason you couldn't encapsulate bound combo boxes, or text boxes, or even forms and reports! You could add custom validation rules, or specialized keystroke handling.

Once you start seeing the possibilities of using classes in your application, everything starts to seem like a candidate for the class treatment.

Jim Ferguson is a consultant and developer with Virginia-based FMS, Inc., maker of add-on products for Access developers. He is co-author with Dan Haught of the Microsoft Jet Database Engine Programmer's Guide, and is a multi-year recipient of Microsoft's Most Valuable Professional award for his support in Microsoft's on-line technical support forums.

Public Sub AddItem(varItem As Variant)
  Dim varTest As Variant
  Dim bFound As Boolean
  Dim intCounter As Integer
  If Not IsNull(varItem) Then
    If InStr(varItem, ";") Then
      MsgBox "Item may not contain " & "';' characters"
    Else
      For Each varTest In mcolItems
        If varTest = varItem Then
          bFound = True
        End If
      Next varTest
      If Not bFound Then
        mcolItems.Add varItem
        mControl.RowSource = ConcatRowSourceStrings()
      End If
    End If
  End If
End Sub

Private Function ConcatRowSourceStrings() As Variant
  Dim varItem As Variant
  Dim varResult As Variant
  For Each varItem In mcolItems
    varResult = varResult & varItem & ";"
  Next varItem
  If Len(varResult) Then
    varResult = Left(varResult, Len(varResult) - 1)
  End If
  ConcatRowSourceStrings = varResult
End Function

Public Sub RemoveSelected()
  Dim varCurItem As Variant
  Dim intCounter As Integer
  If TypeOf mControl Is ListBox Then
    If mControl.MultiSelect = 0 Then
      If mControl.ListIndex <> -1 Then
        RemoveFromCollection mControl.Value
      End If
    Else '1=Simple, 2=Extended
      For Each varCurItem In mControl.ItemsSelected
        RemoveFromCollection mControl.ItemData(varCurItem)
      Next varCurItem
    End If
    mControl.RowSource = ConcatRowSourceStrings()
  ElseIf TypeOf mControl Is ComboBox Then
    Me.RemoveItem mControl.ListIndex
  End If
End Sub

Private Sub QSArray(arrIn() As Variant, ByVal intLowBound As Integer, ByVal intHighBound As Integer)
  Dim intX As Integer
  Dim intY As Integer
  Dim varMidBound As Variant
  Dim varTmp As Variant

  If intHighBound > intLowBound Then
    varMidBound = arrIn((intLowBound + intHighBound) \ 2)
    intX = intLowBound
    intY = intHighBound
    Do While intX <= intY
      If arrIn(intX) >= varMidBound And arrIn(intY) <= varMidBound Then
        varTmp = arrIn(intX)
        arrIn(intX) = arrIn(intY)
        arrIn(intY) = varTmp
        intX = intX + 1
        intY = intY - 1
      Else
        If arrIn(intX) < varMidBound Then
          intX = intX + 1
        End If
        If arrIn(intY) > varMidBound Then
          intY = intY - 1
        End If
     End If
    Loop
    Call QSArray(arrIn(), intLowBound, intY)
    Call QSArray(arrIn(), intX, intHighBound)
  End If
End Sub

Public Sub Sort()
  Dim intCounter As Integer
  ReDim avaritems(1 To mcolItems.Count) As Variant
  For intCounter = 1 To mcolItems.Count
    avaritems(intCounter) = mcolItems.Item(intCounter)
  Next intCounter
  QSArray avaritems, 1, mcolItems.Count
  Set mcolItems = Nothing
  Set mcolItems = New Collection
  For intCounter = 1 To UBound(avaritems)
    mcolItems.Add avaritems(intCounter)
  Next intCounter
  mControl.RowSource = ConcatRowSourceStrings()
End Sub