Access Cookbook - Ken Getz [149]
Calling the AddItem method
Starting with Access 2002, you can add items to a ListBox or ComboBox control by simply calling the AddItem method of the control. (You can remove items from the control by calling its RemoveItem method, specifying the item number or text to remove.) This technique is by far the simplest and should be your first choice, given the option.
Selecting an option in the Fill Choice group runs the following code:
Private Sub grpChoice_AfterUpdate( )
Dim strList As String
Dim intI As Integer
Dim varStart As Variant
lstAddItem.RowSourceType = "Value List"
' Clear out the list.
lstAddItem.RowSource = vbNullString
lstAddItem.ColumnCount = 1
grpColumns = 1
Select Case Me.grpChoice
Case 1 ' Days
' Get last Sunday's date.
varStart = Now - WeekDay(Now)
' Loop through all the week days.
For intI = 1 To 7
lstAddItem.AddItem Format(varStart + intI, "dddd")
Next intI
Case 2 ' Months
For intI = 1 To 12
lstAddItem.AddItem Format(DateSerial(2004, intI, 1), "mmmm")
Next intI
End Select
Me.txtFillString = lstAddItem.RowSource
End Sub
This code starts by setting the RowSourceType property of the control to the text, "Value List":
lstAddItem.RowSourceType = "Value List"
This step is crucial: unless you've set the RowSourceType property correctly, either at design time or in your code, you won't be able to call the AddItem or RemoveItem methods.
Next, the code clears and resets the list's formatting:
lstAddItem.RowSource = vbNullString
lstAddItem.ColumnCount = 1
grpColumns = 1
Then, depending on the choice you've made, the code adds days of the week or months of the year to the ListBox control:
Select Case Me.grpChoice
Case 1 ' Days
' Get last Sunday's date.
varStart = Now - WeekDay(Now)
' Loop through all the week days.
For intI = 1 To 7
lstAddItem.AddItem Format(varStart + intI, "dddd")
Next intI
Case 2 ' Months
For intI = 1 To 12
lstAddItem.AddItem Format(DateSerial(2004, intI, 1), "mmmm")
Next intI
End Select
In order to verify that, under the covers, the code is simply manipulating the RowSource property for you, the example ends by displaying the RowSource property in a TextBox control on the form:
Me.txtFillString = lstAddItem.RowSource
WARNING
Beware that even though it appears that you're actually adding items to the control, what you're really doing is modifying the RowSource property of the control. As such, you're limited by the same restrictions as if you were setting the property manually (see the next section). Specifically, you're limited to the allowed size of the RowSource property, which was 2048 characters in Access 2002 (the size may be larger in your version of Access).
Modifying the RowSource property
If you're using Access 2002 or later, you won't want to use this technique. On the other hand, for earlier versions of Access, this can be a simple way to create unbound lists. If you set a list box's RowSourceType property to Value List, you can supply a list of items, separated with semicolons, that will fill the list. By placing this list in the control's RowSource property, you tell Access to display the items one by one in each row and column that it needs to fill. Because you're placing data directly into the properties sheet, you're limited by the amount of space available in the properties sheet (this value varies depending on the version of Access).
You can modify the RowSource property of a list box at any time by placing into it a semicolon-delimited list of values. The ColumnCount property plays a part, in that Access fills the rows first and then the columns. You can see this for yourself if you modify the ColumnCount property on the sample form (frmRowSource).
The sample form creates a list of either the days in a week or the months in a year, based on the value and option group on the form. The code that performs the work looks like this:
Select Case Me.grpChoice
Case 1 ' Days
' Get last Sunday's date.
varStart = Now - WeekDay(Now)
' Loop through