Programming Combo Box and List Box Controls in Microsoft Access, Part 2

David Shank
Microsoft Corporation

April 6, 2000

In last month's column, I talked about using the built-in settings of the RowSourceType property of a combo box or list box to specify the source of data for the control. This is a powerful and easy-to-implement solution for displaying data from your database -- but what if you want to display data about the database (a list of table names, for example), or data stored in an array, or data stored outside the database? You can set the RowSourceType property to the name of a custom function that provides any data you want to display in the control.

This month, I'll talk about how to create a custom function to fill a combo box or list box control with any data you want to display. Microsoft® Access calls the function several times when the control is initialized -- but the only thing you have to worry about is correctly creating the custom function so it knows how to respond when Access calls it.

Understanding the Basics

You can't just set the RowSourceType property to the name of any custom function you have created. Because of the special role such a function plays, and because Access calls the function repeatedly as the control is initialized, you must be sure that function adheres to a very specific structure. The function must accept five arguments. The first argument must be declared as type Control, and the remaining arguments must be declared as type Variant. The syntax of the function looks like this:

Function functionname**(**control As Control, id As Variant, row As Variant, column As Variant, code As Variant) As Variant

What Do the Function Arguments Mean?

You can name these arguments anything you want, but they are designed to provide the following information:

Because Access calls your function, it is Access that will ask for the information it needs, when it needs it. All you have to do is provide the answers to the questions Access will ask. The code argument described above is the means by which Access will ask its questions. You can determine the question being asked by testing for the intrinsic constant representing the information Access requires. You can think of the intrinsic constant as the question Access asks of your function, and for which your function must provide an answer.

What Do the Intrinsic Constants Mean?

The following table lists the intrinsic constants and the return values that your function should supply:

Notice that whenever a particular value (such as the number of columns) is required, returning Null or any invalid value causes Access to stop calling the user-defined function with that code. This also occurs if you return Null when acLBInitialize is called. In addition, when acLBGetValue or acLBGetFormat are requested, you can use the row and column arguments to identify specific values. This means that you must be able to access your data (by row and column) at any time. Since the function is called repeatedly, you can easily preserve the data for the control in a Static array variable that preserves its values between calls to the function.

What Does the Function Look Like?

Because the custom function has to provide specific answers to the questions Access asks about the data, the custom function should be written using the following template:

Function FunctionName(control As Control, id As Variant, _
                    row As Variant, column As Variant, _
                    code As Variant) As Variant
   Static intRows As Integer
   Static intColumns As Integer
   Static varDisplayData() As Variant
   Dim varRetVal As Variant

   Select Case code
      Case acLBInitialize
         ' Any necessary initialization code goes here.
         ' For example: determine number or rows and number
         ' of columns, save these values in the intRows and
         ' intColumns variables, and re-dimension the Static array 
         ' varDisplayData().

         varRetVal = True
      Case acLBOpen
         ' Return a unique id value here.
         varRetVal = Timer
      Case acLBGetRowCount
         ' Return the number of rows here.
         varRetVal = intRows
      Case acLBGetColumnCount
         ' Return the number of columns here.
         varRetVal = intColumns
      Case acLBGetColumnWidth
         ' Return the column widths here. 
         varRetVal = -1 
      Case acLBGetValue
         ' Return data to be displayed here. 
         varRetVal = varDisplayData(row, column)
      Case acLBGetFormat
         ' Return formatting information here.
      Case acLBEnd
         ' Perform any necessary clean up here.
   End Select
   FunctionName = varRetVal
End Function

The actual code you provide for each branch of the Select Case statement will vary depending on the specific requirements of your custom function, but the basic form of the function will always look like that shown here.

Setting the RowSourceType Property

The last thing to understand is how to properly specify a custom function in the RowSourceType property setting. Whether you set the property using the property sheet or using Visual Basic for Applications (VBA) code, you need to provide only the name of the custom function. The function name is entered without a preceding equal sign (=) and without the trailing pair of parentheses.

Custom Function Examples

The following sections contain some sample custom functions to illustrate how to implement the function template form described in the preceding section.

Filling a Control with a Calculated Value

The following function returns a list of the next four Mondays following today's date. To call this function from a combo box or list box control, you enter ListMondays as the RowSourceType property setting and leave the RowSource property setting blank. The control will display four rows of data in one column, using the default column width for the control.

Since acLBGetFormat and acLBEnd are not used in this example, no code needs to be provided for those conditions.

Function ListMondays(fld As Control, id As Variant _
                    Row As Variant, col As Variant, _
                    code As Variant) As Variant
   Dim intOffset As Integer
   Dim varRetVal As Variant
   Select Case code
      Case acLBInitialize
         ' Just return True here.
         varRetVal = True
      Case acLBOpen
         ' Use the Timer function to generate a unique value.
         varRetVal = Timer
      Case acLBGetRowCount
         ' Create space for 4 rows of data.
         varRetVal = 4
      Case acLBGetColumnCount
         ' Create a single column of data.
         varRetVal = 1
      Case acLBGetColumnWidth
         ' Use the control's default column width.
         varRetVal = -1
      Case acLBGetValue
         ' Use the row argument to generate different dates
         ' for different rows.
         intOffset = Abs((9 - Weekday(Now))Mod 7)
         varRetVal = Format(Now() + intOffset + 7 _
            * row, "mmmm d")
      Case acLBGetFormat
      Case acLBEnd
   End Select
   ListMondays = varRetVal
End Function

The property sheet for the combo box control that calls this function looks like this:

Filling a Control with Disk File Information

The next example uses a static array to store the names of the databases in the current directory. To call this function, you enter ListMDBs as the RowSourceType property setting and leave the RowSource property setting blank. The control will display a row of data for each database found (up to 50) in one column, using the default column width for the control.

Function ListMDBs(fld As Control, id As Variant _
                    Row As Variant, col As Variant, _
                    code As Variant) As Variant
   Static strMDBNames(50) As String
   Static intEntries As Integer
   Dim varRetVal As Variant

   Select Case code
      Case acLBInitialize
         ' Use the Dir function to fill the static array with
         ' the names of the databases found on in the current 
         ' directory. If none are found then varRetVal = 0
         ' and the procedure terminates.
         intEntries = 0
         strMDBNames(intEntries) = Dir("*.mdb")
         Do Until strMDBNames(intEntries) = "" _
            Or intEntries >= 50
            intEntries = intEntries + 1
            strMDBNames(intEntries) = Dir
         Loop
         VarRetVal = intEntries
      Case acLBOpen
         ' Use the Timer function to generate a unique value.
         varRetVal = Timer
      Case acLBGetRowCount
         ' There should be a row for each found file.
         varRetVal = intEntries
      Case acLBGetColumnCount
         ' Create a single column of data.
         varRetVal = 1
      Case acLBGetColumnWidth
         ' Use the control's default column width.
         varRetVal = -1
      Case acLBGetValue
         ' Get the database name from the array.
         varRetVal = strMDBNames(row)
      Case acLBGetFormat
      Case acLBEnd
         ' All finished so clear out the array.
         Erase strMDBNames
   End Select
   ListMDBs = varRetVal
End Function

Notice that in this function the array is filled with data in the acLBInitialize case, and the count of found files is saved in the intEntries variable. The value of intEntries is later used to specify the number of rows when the acLBGetRowCount case occurs. The values in the static array are later accessed when the acLBGeValue case occurs, using the row argument supplied by Access as the index into the array for each data item. Notice also that if the Dir function finds no matching files in the current directory, intEntries will equal 0. If 0 (or Null) is returned when acLBInitialize is called, Access simply stops calling your function, and no data is displayed in the control.

Filling a Control with Database Objects

It should be apparent by now that the real key to this function is getting data into the static array in the acLBInitialize case, and then accessing that array in the acLBGetValue case. The following example fills a combo box with the names of all the tables in the current database. With the exception of the portions of sample code shown here, this function would be identical to the function shown in the previous example. In addition, you would have to set a reference to the Microsoft ADO Extensions 2.1 for DDL and Security (ADOX) type library.

The final example uses a static array to store the names of the tables in the current database. To call this function, enter ListTables as the RowSourceType property setting and leave the RowSource property setting blank. The control will display a row of data for each table in the database (up to 256) in one column, using the default column width for the control.

Function ListTables(fld As Control, id As Variant _
                    Row As Variant, col As Variant, _
                    code As Variant) As Variant
   Dim cat As New ADOX.Catalog
   Dim tbl As ADOX.Table
   Static tbls(256) As String
   Static intEntries As Integer
   Dim varRetVal As Variant
   varRetVal = Null
   Select Case code
      Case acLBInitialize
         cat.ActiveConnection = CurrentProject.Connection
         intEntries = 0
         For Each tbl In cat.Tables
            If tbl.Type == "TABLE" Then
               If tbl.Name <> "dtproperties" Then
                  tbls(intEntries = tbl.Name
                  intEntries = intEntries + 1
               End If
            End If
      varRetVal = intEntries

   ' the remaining code would be the same as
   ' in the previous example.

   ListTables = varRetVal
End Function

Just as in the previous example, the acLBInitialize case is used to get the data you want to display in the control and preserve it so that it will be available during later calls to the function for the acLBGetCalue case.

Once you understand the format for the custom function, it is easy to see how this alternative means of filling a combo box or list box control can be quite powerful.

Where to Get More Info

Here are some additional resources you can use to explore working with Microsoft Access:

• For more information on developing solutions using Microsoft Access see the Office Developer site's list of recommended articles (https://msdn.microsoft.com/office).
• As always, check in regularly at the Office Developer site for information and technical articles (https://msdn.microsoft.com/office) on Office solution development.

David Shank is a Programmer/Writer on the Office team specializing in developer documentation. Rumor has it he lives high in the mountains to the east of Redmond and is one of the few native northwesterners still living in the northwest.