Microsoft KB Archive/92897
ACC1x: Changing RowSource of a List Box or Combo Box at Run Time
The information in this article applies to:
- Microsoft Access versions 1.0, 1.1
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.
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:
- Create a form with an option group control called MyOption, and a combo box called MyCombo.
- Add two check box controls to MyOption and specify the following property:
- For MyCombo, specify the following properties:
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
- Open the form, select one of the check boxes, and open the combo box. Note the order of the records in the combo box.
- 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
Issue type : kbinfo
Last Reviewed: November 4, 2000