ACC1x: Changing RowSource of a List Box or Combo Box at Run Time

• Microsoft Access versions 1.0, 1.1

SUMMARY

The RowSource property for a combo box or list box cannot be modified while the form is active. However, there is a workaround for changing the recordset that the object is bound to. This article discusses that workaround.

MORE INFORMATION

The workaround for this problem is to bind the object to a query, then manipulate the query's recordset underneath the object. For example, you would bind the object to a query called Query1, then create a new query called Query1 programmatically, while the object is active.

The following example illustrates this technique. In this example, there is a combo box bound to data from the Products table in the sample database NWIND.MDB. By selecting one of two check boxes, the order of records in the combo box will be changed:

1. Create a form with an option group control called MyOption, and a combo box called MyCombo.

2. Add two check box controls to MyOption and specify the following property:

         AfterUpdate: =NewOrder([MyOption])
   

3. For MyCombo, specify the following properties:

         RowSourceType: Table/Query
         RowSource: DeleteMe
         ColumnCount: 2
         Locked: True
         Enabled: False
   

4. Add the following function to a module in the database:

   NOTE: In the following sample code, an underscore (_) is used as a line-continuation character. Remove the underscore when re-creating this code in Access Basic.

         Option Explicit
         Function NewOrder (WhichSort%)
            DoCmd Hourglass True
            Dim D As Database, Q As QueryDef
            Set D = CurrentDB()
   
            Screen.ActiveForm!MyCombo.Enabled = True
            Screen.ActiveForm!MyCombo.Locked = False
   
            On Error Resume Next
            D.DeleteQueryDef ("DeleteMe")
            On Error GoTo 0
   
            If WhichSort = 1 Then
               Set Q = D.CreateQueryDef("DeleteMe",_
                      "SELECT [Product ID], [Category ID]_
                      FROM Products ORDER BY [Product ID];")
            Else
               Set Q = D.CreateQueryDef("DeleteMe",_
                      "SELECT [Product ID], [Category ID]_
                      FROM Products ORDER BY [Category ID];")
            End If
   
            Q.Close
            D.Close
   
            DoCmd Requery "MyCombo"
            DoCmd Hourglass False
         End Function
   
   

5. Open the form, select one of the check boxes, and open the combo box. Note the order of the records in the combo box.

6. Select the other check box and open the combo box again. Note that the records' order has changed.