New List-Filling Options in Access 2002

This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

Andy Baron

Powerful list and combo boxes have always been an important part of Access. With Access 2002, they acquire even more power, as Andy Baron shows in this article.

Combo and list boxes have always been very full-featured in Access. Access developers take for granted capabilities like multiple columns and having control over combo box list width, which are unavailable in other development environments (unless you buy third-party controls or perform fancy API tricks). Not much has changed in the Access versions of these controls over the years, other than the addition of the Dropdown method for combo boxes—until now, that is! Access 2002 offers important new ways to populate your list controls.

The list-filling techniques discussed in this article are all alternatives to using queries based on persistent connections to your data. By reducing persistent connections, you improve your application's scalability. However, increasing scalability, as in these examples, is only one reason to use these techniques. They're also handy if you want to list items, like files or printer names, that aren't stored in a database. Without these techniques, you'd need to dump the data into a table and use a query based on that table as the row source of your list control, or use complex callback functions.

The sample application

The sample application that accompanies this article contains a form with several examples of combo and list boxes that demonstrate the list-filling capabilities of Access 2002 (see Figure 1).

The sample application gets its data by opening ADO recordsets against the SQL Server Northwind database. To establish a connection to the database, the code uses the accompanying Data Link file, Northwind.UDL. You can adjust the connection properties by double-clicking on this file and using the dialog box shown in Figure 2. If necessary, you can even change the connection to use the Jet provider and to point to Northwind.mdb, instead of using SQL Server.

Here's the code that opens a connection to the database when one is needed:

Public Function ConnectToNorthwind( _
 cnn As ADODB.Connection) As Boolean
Dim blnState As Boolean
  
If cnn Is Nothing Then
   Set cnn = New ADODB.Connection
End If
  
On Error Resume Next
If cnn.State = adStateOpen Then
   blnState = True
Else
   cnn.ConnectionString = _
     "File Name=" & CurrentProject.Path & _
     "\Northwind.UDL"
        
   cnn.Open
   If cnn.State = adStateOpen Then
      blnState = True
   Else
      blnState = False
   End If
End If
ConnectToNorthwind = blnState
End Function

The recordsets in the sample code use client-side cursors, and their connections to the database are closed immediately after the data is retrieved. The sample application doesn't maintain any persistent connections to the database, increasing scalability.

These "disconnected" list-filling techniques are useful in n-tier applications where data is supplied by middle-tier objects. If that data is packaged as ADO recordsets, then you can use code very similar to the sample code in this application. You'll also see an example of using XML data to create an ADO recordset. You're not required to rely on recordsets—you could use the MSXML parser, for example, to fill lists from XML data without using ADO recordsets at all. To do that, you'd use a value list as your row source type. Value lists are much improved in Access 2002.

Expanded value list capacity

As in prior versions of Access, you can populate combo and list boxes programmatically or in design view by setting the RowSourceType to "Value List" and assigning a delimited list of string values to the RowSource property. For example, you might assign "Male;Female" to the row source of a combo box used for entering a person's gender.

However, in previous versions of Access, this technique could only be used for very short lists, because the RowSource property was limited to strings containing a maximum of 2,048 characters. In Access 2002, that maximum has been increased to 32,768 (32K). This makes value lists much more practical, and, as a side benefit, it also enables you to use more complex SQL strings as row sources.

To create multicolumn controls based on value lists, set the number of columns in the control's Column Count property, and add a semicolon or comma delimiter between data for each column. For example, if you want to use a code of 1 for Male and 2 for Female, enter the values shown in Table 1 for the control's properties.

****

Table 1. Sample values for a value list.

Property name

Value

Column Count

2

Row Source Type

Value List

Row Source

1;Male;2;Female

You can use either semicolons or commas to delimit the list, or a combination of the two. If you use commas in the property window, Access will convert them to semicolons. However, when assigning a value list row source in code, you can use either or both characters.

So, what do you do if the values in your list contain commas or semicolons? For example, suppose you want a list of employees to appear as LastName, FirstName. You can accomplish this by enclosing your values in quotes. For example, the row source of a two-column value list containing employee IDs and names might look like this:

1,"Baron, Andy",2,"Chipman, Mary"

The sample application uses the ADO GetString method to transform a recordset into a semicolon-delimited list, with quotes surrounding each of the values. The Employees combo box shown in Figure 1 has a row source type of Value List, and it has this code in its Enter event procedure:

Private Sub cboEmployeeVL_Enter()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strEmployees As String
    
On Error GoTo HandleErr
  
If ConnectToNorthwind(cnn) Then
  Set rst = New ADODB.Recordset
  rst.CursorLocation = adUseClient
  rst.Open _
    Source:="SELECT EmployeeID," _
    & " LastName + ', ' + FirstName" _
    & " FROM Employees" _
    & " Order By LastName, FirstName", _
    ActiveConnection:=cnn, _
    CursorType:=adOpenStatic, _
    Options:=adCmdText
  Set rst.ActiveConnection = Nothing
  CloseAndReleaseConnection cnn
  strEmployees = rst.GetString(adClipString, _
    ColumnDelimeter:=""";""", RowDelimeter:=""";""")
    strEmployees = """" & _
    Left(strEmployees, Len(strEmployees) - 2)
  Me!cboEmployeeVL.RowSource = strEmployees
    
  rst.Close
Else 
  MsgBox "Couldn't connect to Northwind"
End If
ExitHere:
  CloseAndReleaseConnection cnn
  Set rst = Nothing
  Exit Sub
  
HandleErr:
  MsgBox Err & ": " & Err.Description, , _
    "Error in cboEmployeeVL"
  Resume ExitHere
End Sub

The only tricky part of the code is the doubling up of quotes inside of quotes. The value list string that this code produces begins like this:

"5";"Buchanan, Steven";"8";"Callahan, Laura";

The first column of the combo box, containing employee IDs, has a width of zero, so it's hidden. When you select an employee, the Orders list box, which is also shown in Figure 1, gets populated with a list of the orders taken by that employee, showing the order ID and order date. The code for this is a little simpler because there's no need to worry about the order IDs or dates including commas or semicolons. Without that worry, there's no need to enclose the values in quotes:

Private Sub cboEmployeeVL_AfterUpdate()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim strOrders As String
  
On Error GoTo HandleErr
If ConnectToNorthwind(cnn) Then
  strSQL = "SELECT OrderID, OrderDate " & _
   "FROM Orders " & _
   "WHERE EmployeeID = " & Me!cboEmployeeVL & _
   " ORDER BY OrderDate DESC"

  Set rst = New ADODB.Recordset
  rst.CursorLocation = adUseClient
  rst.Open _
    Source:=strSQL, _
    ActiveConnection:=cnn, _
    CursorType:=adOpenStatic, _
    Options:=adCmdText
  Set rst.ActiveConnection = Nothing
  CloseAndReleaseConnection cnn
  If rst.EOF Then
    rst.Close
    GoTo ExitHere
  End If
    
  strOrders = rst.GetString(adClipString, _
     ColumnDelimeter:=";", RowDelimeter:=";")
  Me!lboOrdersVL.RowSource = strOrders
  rst.Close
Else 
  MsgBox "Couldn't connect to Northwind"
End If
ExitHere:
  CloseAndReleaseConnection cnn
  Set rst = Nothing
  Exit Sub
  
HandleErr:
  MsgBox Err & ": " & Err.Description, , _
    "Error in cboEmployeeVL_AfterUpdate"
  Resume ExitHere
  Resume
End Sub

Here's the beginning of the string that this code produces:

11075;5/6/1998;11068;5/4/1998;

Both of these event procedures, as well as all of the others in this application, call a public sub to close and release the ADO connection:

Public Sub CloseAndReleaseConnection( _
 cnn As ADODB.Connection)
  If Not cnn Is Nothing Then
    If cnn.State = adStateOpen Then
      cnn.Close
      Set cnn = Nothing
    Else
      Set cnn = Nothing
    End If
  End If
End Sub

Using GetString is, of course, only one of many possible ways to construct a value list. If you're working with XML data, you could even create an XSL stylesheet that would transform an XML document into a delimited string suitable for use as a value list. In addition, Access 2002 provides two new methods for adding and removing items from value lists.

Editing value lists

If you've used Visual Basic forms or the forms package that's used with VBA in Word and Excel, then you've probably used the AddItem method to populate combo and list boxes. Access 2002 introduces AddItem and RemoveItem methods that are implemented as wrappers around value lists. To use these methods in Access, your combo or list box must have a Row Source Type of Value List, and these new methods simply append or remove items from the value list string.

The sample application includes two controls very similar to the ones shown in Figure 1, except that their code uses AddItem rather than GetString to construct the value lists. Here's the section of code that populates the employees combo box using the new AddItem method:

With Me.cboEmployeesAI
  Do Until rst.EOF
    .AddItem """" & rst!EmployeeID & """;""" _
       & rst!FullName & """"
    rst.MoveNext
  Loop
End With

You still need to add extra quotes if your values might contain commas or semicolons, and you need to add a delimited set of values for each item if you need multiple columns. In other words, all AddItem does is append a string to the existing value list string.

The RemoveItem method isn't used in the sample application. RemoveItem enables you to delete a section of your value list by specifying the zero-based row index or the value in the bound column of that row. This is a bit handier than AddItem, because the code to do this manually would be a bit trickier to write.

There's no Clear method to go along with AddItem and RemoveItem. If you want to clear the contents of a value list, you must assign an empty string to the row source property. The code for the AddItem version of the Orders list box does this:

With Me.lboOrdersAI
  .RowSource = ""
  Do Until rst.EOF
   .AddItem rst!OrderID & ";" _
      & rst!OrderDate
   rst.MoveNext
  Loop
End With

If you've worked with the VB or VBA list controls, you may also have used their Column and List properties, which enable you to fill a control by assigning an array of values to the property. These properties weren't added to the Access controls, although the familiar Access Column property still enables you to read values stored in your combo box. The sample application includes examples of using the MS Forms combo and list boxes (the ones used in Word and Excel) on the Access form. In the examples, the ADO GetRows method transforms a recordset into an array, and the code assigns this array to the Column property of the control. This is a very efficient technique that isn't available in Access controls:

varList = rst.GetRows
cboMSF.Column = varList

Unfortunately, the missing Clear method, List property, and read/write Column property aren't the only shortcomings to the implementation of AddItem and RemoveItem in Access 2002. Performance is seriously flawed. Perhaps this will be fixed in a service pack, but using the original release of Access 2002 to run the sample application results in very noticeable delays when the controls are being filled by using AddItem. For this reason, be sure to test your application carefully before relying on these new methods in production code.

Despite the poor performance of AddItem for populating lists, however, the expansion of the size of value lists in Access 2002 is a very welcome feature. With just a little effort, you can easily write your own list-filling code for value lists, using GetString, XML techniques, or simple string concatenation, and you now have a very viable alternative to relying on database objects as row sources. Although the controls in the sample application are unbound, you can use value lists with bound controls (ones that have control sources) just as effectively.

Assigning recordsets to controls

In Access 2000, Microsoft made it possible for you to assign recordsets to forms, instead of setting the form's record source. Now in Access 2002, you can assign a recordset to a combo or list box, instead of setting the control's row source (you can also now assign properly shaped recordsets to Access reports).

For the types of list-filling needs illustrated in the sample application, this is clearly the most suitable new technique to use, and you'll find that it performs very well. Why bother with value lists when you can assign your recordset directly to the control? With this new capability, the only time you need to consider using a value list is if your data doesn't lend itself easily to being gathered in a recordset.

The sample application contains a generic procedure, LoadControlRst, that accepts a control, a source string, and a command type as parameters. The code opens a recordset against Northwind using the source string and the command type. In the sample form, the source strings are SQL statements, and the command type is adCmdText; but you could also use the name of a stored procedure with adcmdStoredProc as the command type. Once the recordset is created, it's assigned to the control

The LoadControlRst procedure was written to work with lookup data that doesn't change often—for example, lists of products or employees. Instead of going to the database each time the procedure needs to fill a list control, it first looks for a locally saved XML file. If it finds the file, it loads a recordset from the file. If it doesn't find the file, it connects to the database, opens a client-side recordset, disconnects, saves the recordset as XML, and assigns the recordset to the control. To refresh the XML files, you call a procedure that deletes the local XML file and reloads the control. Here's the opening of the LoadControlRst procedure:

Public Sub LoadControlRst( _
  ByRef ctl As Control, _
  strSource As String, _
  lngRowSourceCommandType As ADODB.CommandTypeEnum)
    
On Error GoTo Handle_Err
Const conProcName As String = _
    "LoadControlRst"
Dim strFile As String
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

This code generates the name that will be used for the XML file:

  strFile = CurrentProject.Path & "\" _
  & CurrentProject.Name & "_" & ctl.Name & ".XML"

With the preliminary work done, the code then creates a client-side recordset and either opens the XML or creates it:

    Set rst = New ADODB.Recordset
    rst.CursorLocation = adUseClient
    rst.CursorType = adOpenStatic
    rst.LockType = adLockReadOnly
    If Len(Dir(strFile)) > 0 Then
      rst.Open Source:=strFile, Options:=adCmdFile
    Else
      Set rst.ActiveConnection = cnn
      rst.Open Source:=strRowSource, _
       Options:=lngRowSourceCommandType
      rst.Save strFile, adPersistXML
      Set rst.ActiveConnection = Nothing
      CloseAndReleaseConnection cnn
    End If

In the previous code, if the XML file is found, the recordset is loaded from the XML file. If the file isn't found, the data is retrieved, and the resulting recordset is saved as an XML file. With the recordset now created, it's assigned to the control passed to the routine:

    Set ctl.Recordset = rst

The error handler for this routine has to handle finding the file already present when it saves the recordset (this could happen if someone saves it between the time we checked for it and when we saved it):

Handle_Err:
  Select Case Err.Number
    Case 58   ' File already exists.
      Kill strFile
      Resume
    Case Else
      Err.Raise Err.Number, _
        conProcName, Err.Description
  End Select

Figure 3 shows the part of the sample form that works with recordsets and XML. When you click the Refresh button next to the employees combo box, it runs this code:

Call RefreshControlRst( _
    Me.cboEmployeesXML, _
    "SELECT EmployeeID, " _
    & "LastName + ', ' + FirstName" _
    & " AS FullName" _
    & " FROM Employees" _
    & " Order By LastName, FirstName", _
    adCmdText)

Here's the RefreshConrolRst procedure that's called by the Refresh button click event procedure:

Public Sub RefreshControlRst(ctl As Control, _
 strRowSource As String, _
 lngRowSourceCommandType As ADODB.CommandTypeEnum)

Const conProcName = "RefreshControlRst"
On Error Resume Next
Kill CurrentProject.Path & "\" _
    & CurrentProject.Name & "_" & ctl.Name & ".xml"
Err.Clear
On Error GoTo Handle_Err
Call LoadControlRst(ctl, strRowSource, _
   lngRowSourceCommandType)

This example is somewhat complex, because of the use of XML files to store lookup data locally without making repeated calls to the database. But the part of the code that works with the new Recordset property of list controls is very straightforward—you simply assign your recordset to the property:

Set ctl.Recordset = rst

You can use disconnected recordsets, as the sample code does. However, you can't use recordsets that you create from scratch yourself. ADO enables you to create a new recordset, add field definitions to it, and then add rows of data, without ever connecting to a data source. This type of recordset can't successfully be assigned to an Access list control. If you're composing list data in code without ever making a connection to a data source, that would be a good place to use a value list.

List controls have always been among the most useful features in Access, and in Access 2002 they get even better. Value lists now can hold up to 32K characters, not the measly 2K that was supported previously. New AddItem and RemoveItem methods make it easier to edit value lists, but test their performance in your application carefully before you rely on them. You can also now fill a list control simply by assigning a recordset to the control's new Recordset property. One application of these new techniques is to build forms that don't require persistent connections to a database in order to display lookup data.

To find out more about Smart Access and Pinnacle Publishing, visit their website at http://www.pinpub.com/html/main.isx?sub=57

Note: This is not a Microsoft Corporation website. Microsoft is not responsible for its content.

This article is reproduced from the December 2001 issue of Smart Access. Copyright 2001, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. Smart Access is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-493-4867 x4209.