Microsoft KB Archive/128158

= ACC: Combo Box in Continuous Form Shows Incorrect Data =

Article ID: 128158

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q128158



Moderate: Requires basic macro, coding, and interoperability skills.



SYMPTOMS
When you select a row in a combo box in a continuous form or a form that is open in Datasheet view, the text portion of the combo box in other records appears empty.



CAUSE
The behavior occurs under the following conditions:


 * The form is a continuous form, or it is open in Datasheet view so that you can view multiple records.
 * The combo box's RowSource property is set to a parameterized query to limit the number of rows in the combo box based on criteria in another field in the form.
 * The combo box criteria for the current record eliminate the rows that were selected in the other records.

Microsoft Access maintains only one query recordset for a combo box rather than one recordset for each combo box in each record.

When the criteria for the query that the combo box is based on change, rows that were selected in the other records may be eliminated from the recordset. As a result, the non-BoundColumn values are no longer available to be displayed.



RESOLUTION
Do not hide the BoundColumn field. If you want to see a different column, use the DLookup function or the AutoLookup technique to display the information you want in another control on the form.



Steps to Reproduce Behavior
The following example creates two synchronized combo boxes. The selection you make in the first combo box determines the contents of the second combo box.

 Open the sample database Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0).  Create a new table called My Products with the following properties:

     Table: My Products --     Field Name: Category ID         Data Type: Text FieldSize: 4 Field Name: Product ID        Data Type: Number FieldSize: Long Integer   Create the following new query:

     Query: Product Combo Query --     Type: Select Field: ProductID (or Product ID in versions 1.x and 2.0) Table: Products Field: ProductName (or Product Name in versions 1.x and 2.0) Table: Products Field: CategoryID (or Category ID in versions 1.x and 2.0) Table: Products Criteria: Forms![My Products Form]![Category Combo] Click the check box in the Show row in the Category ID column to clear the check box.   Create the following new macro:

<pre class="fixed_text">     Macro Name              Macro Action Refresh Product Combo  Requery

Refresh Product Combo Actions --     Requery Control Name: Product Combo </li>  Create a new form with the following properties and controls based on the My Products table. Save the form as My Products Form.

<pre class="fixed_text">     RecordSource: My Products

Combo box: Name:         Category Combo (Control Name in version 1.x)         ControlSource: Category ID         RowSourceType: Table/Query RowSource:    Categories ColumnCount:  2 BoundColumn:  1 ColumnWidth:  .2 in         Left:          1.1 Top:          0 After Update: Refresh Product Combo

Combo box: Name:         Product Combo ControlSource: Product ID        RowSourceType: Table/Query RowSource:    Product Combo Query ColumnCount:  2 ColumnWidth:  0 BoundColumn:  1 Left:         3.1 Top:          0

Detail Section: Height:       .17 </li> View the form in Form view.</li> In the first record, select Beverages (or BEVR in version 1.x) in the Category Combo combo box and Chai in the Product Combo combo box.</li> In the second record, select Beverages (or BEVR in version 1.x) in the Category Combo combo box and Chang in the Product Combo combo box.</li> In the third record, select Condiment (or COND in version 1.x) in the Category Combo combo box. Note that Chai and Chang now show the Category ID number instead of the text (in versions 1.x and 2.0, the information in the field disappears entirely).</li></ol>

<div class="references_section">