Microsoft KB Archive/92897

{|
 * width="100%"|

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

 * }

Q92897

-

The information in this article applies to:


 * 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:

 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:

AfterUpdate: =NewOrder([MyOption]) For MyCombo, specify the following properties:

RowSourceType: Table/Query

RowSource: DeleteMe

ColumnCount: 2

Locked: True

Enabled: False

  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 (&quot;DeleteMe&quot;) On Error GoTo 0

If WhichSort = 1 Then Set Q = D.CreateQueryDef(&quot;DeleteMe&quot;,_                  &quot;SELECT [Product ID], [Category ID]_                   FROM Products ORDER BY [Product ID];&quot;) Else Set Q = D.CreateQueryDef(&quot;DeleteMe&quot;,_                  &quot;SELECT [Product ID], [Category ID]_                   FROM Products ORDER BY [Category ID];&quot;) End If

Q.Close D.Close

DoCmd Requery &quot;MyCombo&quot; 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

Technology :