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

ID: Q92897


The information in this article applies to:


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.


The key to changing the recordset is in Step 4. By using different SELECT statements for the CreateQueryDef() methods, you can create any kind of query.


Keywords          : kbusage FmsCmbo 
Version           : 1.0 1.1
Platform          : WINDOWS 
Issue type        : kbinfo 

Last Reviewed: March 12, 1999