Access Cookbook - Ken Getz [148]
Solution
Access list boxes (and combo boxes) in versions prior to Access 2002 didn't support the AddItem method that Visual Basic programmers are used to using. To make it easy for you to get bound data into list and combo boxes, the Access developers originally didn't supply a simple technique for loading unbound data. To get around this limitation, there are two methods you can use to place data into an Access list or combo box: you can programmatically build the RowSource string yourself, or you can call a list-filling callback function. Providing the RowSource string is easy, but it works in only the simplest of situations. A callback function, though, will work in any situation. This solution demonstrates both methods. In addition, this solution demonstrates using the AddItem method of ListBox and ComboBox controls, added in Access 2002.
One important question, of course, is why you would ever need either of the more complex techniques for filling your list or combo box. You can always pull data from a table, query, or SQL expression directly into the control, so why bother with all this work? The answer is simple. Sometimes you don't know ahead of time what data you're going to need, and the data's not stored in a table. Or perhaps you need to load the contents of an array into the control and you don't need to store the data permanently. Prior to Access 2002, you had no choice but to either create a list-filling callback function, or modify the RowSource property of the control yourself. Starting in Access 2002, you can also use the AddItem method to solve many list filling requirements.
The following sections walk you through using all three of the techniques for modifying the contents of a list or combo box while your application is running. The first example modifies the value of the RowSource property, given that the RowSourceType property is set to Value List. The second example covers list-filling callback functions. The final example shows how to use the AddItem method of the control.
Filling a list box by calling the AddItem method
Open the form frmAddItem in 07-05.MDB.
Change the contents of the list box by choosing either Days or Months from the option group on the left. Try both settings and change the number of columns to get a feel for how this method works. Figure 7-6 shows the form set to display month names in three columns.
Figure 7-6. The sample form, frmRowSource, displaying months in three columns
Filling a list box by modifying the RowSource property
Open the form frmRowSource in 07-05.MDB.
Change the contents of the list box by choosing either Days or Months from the option group on the left. Try both settings and change the number of columns, to get a feel for how this method works. Figure 7-6 shows the form set to display month names in three columns.
Filling a list box by creating a list-filling callback function
Open the form frmListFill in 07-05.MDB.
Select a weekday from the first list box. The second list box will show you the date of that day this week, plus the next three instances of that weekday. Figure 7-7 shows the form with Wednesday, March 14, 2001, selected.
Figure 7-7. Using list-filling callback functions to fill the lists on frmListFill
To use this method, set the control's RowSourceType property to the name of a function (without an equals sign or parentheses). Functions called this way must meet strict requirements, as discussed in the next section. Figure 7-8 shows the properties sheet for the list box on frmListFill, showing the RowSourceType property with the name of the list-filling function.
Figure 7-8. The properties sheet entry for the list-filling function
Discussion
This section explains the two methods for programmatically filling list and combo boxes. The text refers only to filling list boxes, but the same techniques apply to combo boxes. You may find it useful to open up the