How do I… Display multiple values in an Access value list control?
The title’s a mouthful, but don’t get too hung up on the terminology. The technique is simple, although not commonly implemented. You can populate a Microsoft Access list control with an explicit list of values and generally that list consists of one column, although you can display more. Creating a multi-column value list control is the easy part — using and updating it takes a bit more effort.
This blog post is also available in PDF form as a TechRepublic download, which includes the sample database mentioned.
The basics
The form in Figure A contains a typical value list control with two columns. Fortunately, it’s easy to create:
- In a blank form, insert a list box control. Name it lstMultcolumn.
- Set the Row Source Type property to Value List.
- Set the Row Source property to the following string: 1;Male;2;Female
- Set the Column Count property to 2.
- Set the Column Widths property to .5;.5
Figure A
The Row Source property specifies list values
By default, the control’s bound column is the first column. In this case, that column contains the values 1 and 2, which represent the descriptive text items, Male and Female, respectively. In a working database, you’d probably hide the first column.
With a value list control, the Column Count property forces additional columns, accordingly. In this case, there are four list items and two columns, so Access displays two values in each row. If you changed the Column Count property to 1, Access would display four items in a single column.
Access doesn’t care if you use commas (,) or semi-colons (;) to delimit the list values. If you use commas, Access will convert them to semi-colons for you. That can mean a little extra work if you need to include a literal comma in a list item. For instance, look at the Row Source property for the bottom list control in Figure B.
1;”Harkins, Susan”;2;”Harkins, William”
Figure B
The values in the value list control contain literal comma characters
Using a multi-column value list control
Populating multiple columns in a value list control is simple, if you do so manually. If you frequently update the list, you’ll want to automate that task using Visual Basic for Applications (VBA). It doesn’t matter whether you’re retrieving native or foreign data, the technique is the same. The only difference is how you connect to the source data.
To create the form in Figure C, insert a list box control into a form. Name the control lstShippers, set the Row Source Type property to Value List, and set the Column Count property to 3. (The Shippers table contains three columns of data.)
Figure C
VBA populates this value list control by retrieving data from a foreign source
In Design view, click the Code button to launch the form’s module and enter the code shown in Listing A.
Listing A
Private Sub Form_Open(Cancel As Integer)
'Populate list box control.
Dim cnn As ADODB.Connection
Dim strSQL As String
Dim rst As ADODB.Recordset
Dim strList As String
On Error GoTo ErrHandler
'Use DSN to Northwind.
'Modify connection and connection string as needed.
Set cnn = New ADODB.Connection
cnn.Open "DSN=NorthwindExample"
strSQL = "SELECT * FROM Shippers"
Set rst = New ADODB.Recordset
rst.Open strSQL, cnn
strList = rst.GetString(adClipString, , ";", ",")
Debug.Print strList
Me.lstShippers.RowSource = strList
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
Exit Sub
ErrHandler:
MsgBox Err.No & ": " & Err.Description, _
vbOKOnly, "Error"
Set rst = Nothing
Set cnn = Nothing
End SubUsing the form’s Open event forces Access to populate the list when the form opens. You don’t have to use this event, but it’s usually the simplest way to go.
The first important thing the code does is connect to a data source. Because there are so many possibilities, the example uses a Data Source Name (DSN), which simplifies the code. In this case, the DSN connects to Northwind, the sample database that comes with Access. In the real world, the data you need may be in a foreign format, such as SQL Server, Excel, or even a comma delimited text file. You’ll want to modify that part of the code if necessary.
Fortunately, a DSN is easy to create (in Windows XP):
- Choose Control Panel from the Windows Start menu.
- Double-click Administrative Tools.
- Double-click Data Sources (ODBC).
- In the User DSN tab, select MS Access Database and click Add.
- Choose Microsoft Access Driver (.mdb) and click Finish.
- Enter a name for the Data Source Name, such as NorthwindExample.
- Click the Select button in the Database section.
- Use the Directories control to locate the folder where Northwind is stored.
When Northwind.mdb appears in the Database control, double-click it. Windows will return to ODBC Microsoft Access Setup dialog as shown in Figure D. As you can see, it displays the path to Northwind.
Click OK twice.
Figure D
It’s easy to create and connect to a DSN
Using a DSN simplifies the example code so you can concentrate on the technique at hand instead of connecting to a data source. The next bit of code populates an ADODB Recordset object with all the data in the Shippers table. Modify the SQL SELECT statement as necessary to retrieve just the values you need.
The GetString method is the heart of this technique. There are a number of complex routines you could write to move through the recordset retrieving each value one by one. Instead, the GetString method does it with one simple statement.
This method returns the recordset as a string and uses the following syntax where rst is a Recordset object.
rst.GetString(stringformat, numrows, columndelimiter, rowdelimiter, nullexpr)
Table A defines the method’s parameters. By omitting a numrows value, the method converts the entire recordset. The delimiters perform the rest of the magic. Keep in mind that this routine offers only basic error handling. You’ll want to thoroughly test the function and include appropriate error handling.
Table A
|
Parameter |
Explanation |
stringformat |
A required enumerated constant that specifies how the recordset converts to a string. The only constant available is adClipString. |
numrows |
An optional parameter that specifies the number of rows to convert. If omitted, all rows are converted. |
columndelimiter |
An optional parameter that specifies the character to use between columns, with Tab being the default. |
rowdelimiter |
An optional parameter that specifies the end of a row, with Carriage Return being the default. |
nullexpr |
An optional parameter that specifies how to replace a null value, with the default being an empty string. |
Value list value
You might think automating a value list is a lot of fuss for nothing, but there are a couple of good reasons to automate this process:
- By automating the routine to pull the values directly from the data source each time you open the form, you get the most up-to-date data every time you use the form.
- There are other ways to automate the process, but they require a native table and a Table/Query list box. This method eliminates the need for another table, even if you create and destroy it as you go.
It’s too simple to ignore.
Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies. Her most recent book is “Mastering Microsoft SQL Server 2005 Express,” with Mike Gunderloy, published by Sybex. Other collaborations with Mike Gunderloy are “Automating Microsoft Access 2003 with VBA,” “Upgrader’s Guide to Microsoft Office System 2003,” “ICDL Exam Cram 2,” and “Absolute Beginner’s Guide to Microsoft Access 2003″ all by Que. Currently, Susan volunteers as the Publications Director for Database Advisors at http://www.databaseadvisors.com. You can reach her at ssharkins@gmail.com.
