Dynamically Filling Combo Boxes

by Patrick L. Steiner

Through the help of wizards or by manually specifying a few properties, you can easily create and populate a combo box in Access. The key properties you need to set are RowSourceType (Table/Query, Value List, or Field List) and RowSource (such as the name of a table or query).

This technique works fine as long as your combo box entries aren't dependent on other values that the user may change. But what if you want to update the values in the combo box, depending on other data on the form? For example, you may want to display a list of tasks that are available for a project. But if the user has the ability to alter the project, you need to dynamically change the values in your combo box. In this article, we'll show you how.

Setting RowSourceType

If you open the properties window for a combo box and click on the dropdown list for the RowSourceType property, you'll see these options:

•  Table/Query
  
  
• Value List
  
  
• Field List

To dynamically set the values in the combo box, you shouldn't choose any of these options. Instead, you'll write a VBA function and specify the values. We'll describe how this function works and use it in an example database.

Example database: time cards

We'll demonstrate the need for and use of dynamically changing combo boxes through a database that tracks time (hours) by project, task, and date. To do this, create the three tables described in Tables A, B, and C. Next, enter the sample data shown in Figures A and B for tables tblProjects and tblProjectTasks respectively.

Table A: tblProjects structure

Table B: tblProjectTasks structure

Table C: tblProjectHours structure

Time card data entry form

Now, create a form similar to the one shown in Figure C and name it frmProjectHours. Set the form's RecordSource property to tblProjectHours and its DefaultView property to Continuous. Table D lists the property settings for the individual controls in the form's Detail section.

Table D: frmProjectHours control properties

You need to add only one small bit of code behind the form. In the ProjectNbr combo box's OnChange event property, enter the following code:

Private Sub ProjectNbr_Change()

Me![TaskName].RowSourceType = "FillComboBox"
Me![TaskName] = Null

End Sub

You want to dynamically set the TaskName combo box's RowSourceType property anytime you change the ProjectNbr value. To do this, you trigger the code to run whenever the ProjectNbr changes—via the OnChange event for the ProjectNbr combo box. The values in the TaskName combo box are set by the FillComboBox function, as we'll describe in the next section. Unlike most other function calls, you don't need any calling arguments or parentheses when referring to FillComboBox—but the quotation marks are required around its name.

Function FillComboBox

Listing A contains the code for the FillComboBox function. It's basically one Select Case statement based on the input argument code. The calling argument list (as specified) is required for any function that dynamically sets the values of a combo box (refer to Microsoft's Online Help on the RowSourceType property).

Listing A: FillComboBox function

Function FillComboBox(fld As Control, _
  id As Variant, row As Variant, _
  col As Variant, code As Variant) As Variant

'-----------------------------------------------
' Purpose:  To fill in the rows of the combo box
' on the <frmProjectHours> based on the 
' [ProjectNbr] selected.
'-----------------------------------------------
' By:       Pat Steiner
' Date:     4/28/98
' Version:  1.0
'-----------------------------------------------

Dim iRow As Integer

Select Case code
  
  Case acLBInitialize       ' Initialize.
    FillComboBox = True
  
  Case acLBOpen             ' Open.
    FillComboBox = Timer    ' Unique ID.
  
  Case acLBGetRowCount      ' Get rows.
    FillComboBox = GetNumberOfFieldsSelect()
  
  Case acLBGetColumnCount   ' Get columns.
    FillComboBox = 1
  
  Case acLBGetColumnWidth   ' Get column width.
    FillComboBox = -1       ' Use default width.
  
  Case acLBGetValue         ' Get the data.
    iRow = Int(row)
    FillComboBox = SetFieldNamesSelect(iRow)

End Select

End Function

Recall that when you set the TaskName combo box's RowSourceType property in code, you specified no calling arguments. Normally, this omission would cause the function to generate an error. But the code works because Access automatically supplies these arguments when the function is called. As a matter of fact, Access will initiate many calls to your function, and in the process, set the values of the various calling arguments. (You can see this happen if you set a breakpoint in the function and watch it work via the Debug window).

Fortunately, you need to determine only two pieces of information for the FillComboBox function: the number of items in the combo box list and the items' actual values. To determine the number of items, you'll use the GetItemsCount function. This function reads the ProjectNbr value from frmProjectHours. Then, it creates a dynaset (query) for all records in tblProjectTasks that match the ProjectNbr value. The total number of matching records is the return value of the function. Listing B contains the code for the GetItemsCount function.

Listing B: GetItemsCount function

Function GetItemsCount() As Integer

'-----------------------------------------------
' Purpose:  To get the number of fields for the 
' [ProjectNbr] specified on current record of
' <frmProjectHours>
'-----------------------------------------------
' By:       Pat Steiner
' Date:     4/30/98
' Version:  1.0
'-----------------------------------------------

Dim DB As Database
Dim RS As Recordset
Dim F As Form
Dim fld As Field
Dim iItems As Integer
Dim iProjectNbr As Long
Dim sSQL As String

'---------------------------
' Initialize local variables
'---------------------------
GetItemsCount = 0
iItems = 0

On Error GoTo Err_GetItemsCount

Set DB = CurrentDb()
Set F = Forms![frmProjectHours]

If Not IsNull(F![ProjectNbr]) Then
  iProjectNbr = F![ProjectNbr]
Else
  Exit Function
End If

sSQL = "Select * From tblProjectTasks Where 
?ProjectNbr="
sSQL = sSQL & Str$(iProjectNbr) & _
  " Order By TaskNbr;"
Set RS = DB.OpenRecordset(sSQL, dbOpenDynaset)

If Not RS.EOF Then
  RS.MoveLast
  iItems = RS.RecordCount
End If

RS.Close

GetItemsCount = iItems

Exit Function

'=================
Err_GetItemsCount:
'=================
Exit Function

End Function

You also need to determine the actual value for each item in your combo box. The SetItemValues function, shown in Listing C, does this for you. It receives the calling argument iRow, which is the number of the item being set. For instance, if your combo box has two items, SetItemValues will be called twice: first with an iRow of 1 and a second time with an iRow value of 2. In each case, the function creates a dynaset based on frmProjectHours's ProjectNbr value. SetItemValues reads each record of the dynaset and sets the function's return value equal to the TaskName whose record number matches iRow—that is, iRow equals 1, you use record 1, and so on.

Listing C: SetItemValues function

Function SetItemValues(iRow As Integer) _
  As String

'-----------------------------------------------
' Purpose:  To set the field name of the "Nth" item
' as specified by [iRow].
'-----------------------------------------------
' By:       Pat Steiner
' Date:     4/30/98
' Version:  1.0
'-----------------------------------------------

Dim DB As Database
Dim RS As Recordset
Dim F As Form
Dim iRowCounter As Integer
Dim iProjectNbr As String
Dim sSQL As String

'---------------------------
' Initialize local variables
'---------------------------
SetItemValues = "-Unknown-"
iRowCounter = 0

On Error GoTo Err_SetItemValues

Set DB = CurrentDb()
Set F = Forms![frmProjectHours]

If Not IsNull(F![ProjectNbr]) Then
  iProjectNbr = F![ProjectNbr]
Else
  Exit Function
End If

sSQL = "Select * From tblProjectTasks Where 
?ProjectNbr="
sSQL = sSQL & Str$(iProjectNbr) & _
  " Order By TaskNbr;"
Set RS = DB.OpenRecordset(sSQL, dbOpenDynaset)

Do Until RS.EOF
  iRowCounter = iRowCounter + 1
  If iRowCounter = (iRow + 1) Then
    SetItemValues = RS![TaskName]
  End If
  RS.MoveNext
Loop

RS.Close
Exit Function

'=================
Err_SetItemValues:
'=================
Exit Function

End Function

Using the example program

Open frmProjectHours in form view mode. Now, enter some test data like that shown in Figure D. Note that after you choose a project and move to the TaskName combo box, the form displays only those tasks valid for the selected project. For instance, in the third record in Figure D, we've selected the project Develop Sales Database, which has a ProjectNbr value of 1. You entered seven tasks for this project into tblProjectTasks (Figure B). If you look at the TaskName combo box that's open in Figure D, you'll see only 7 tasks are available—not the full 11 tasks you entered into tblProjectTasks.

Conclusion

As we demonstrated in this article's example, the dynamic combo box can be a useful element in your database applications. Such a combo box adds integrity to your data-entry forms by eliminating inappropriate entries.

Copyright © 1998, ZD Inc. All rights reserved. ZD Journals and the ZD Journals logo are trademarks of ZD Inc. Reproduction in whole or in part in any form or medium without express written permission of ZD Inc. is prohibited. All other product names and logos are trademarks or registered trademarks of their respective owners.