Microsoft KB Archive/208866

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

Article ID: 208866

Article Last Modified on 6/24/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q208866



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

This article applies only to a Microsoft Access database (.mdb).



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 changes for the query that the combo box is based on, 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 that you want in another control on the form.



Steps to Reproduce Behavior
The following example creates two synchronized combo boxes. The selection that you make in the first combo box determines the contents of the second combo box.  Start Microsoft Access and open the sample database Northwind.mdb.  Create a new table called My Products with the following properties:   Table: My Products -- Field Name: CategoryID Data Type: Text FieldSize: 4 Field Name: ProductID Data Type: Number FieldSize: Long Integer   Create and save the following new query:   Query: Product Combo Query --  Type: Select Field: ProductID Table: Products Field: ProductName Table: Products Field: CategoryID Table: Products Criteria: Forms![My Products Form]![Category Combo] Click to clear the check box in the Show row in the Category ID column.   Create and save the following new macro: <pre class="fixed_text">  Macro Name: Refresh Product Combo

Action        Arguments -  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 1 Name:         Category Combo 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 2 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> Create the first record by selecting Beverages in the Category Combo combo box and Chai in the Product Combo combo box. Click the New Record navigation button.</li> Create the second record by selecting Beverages in the Category Combo combo box and Chang in the Product Combo combo box. Use the Previous Record navigation button to go back to the first record, and notice that "Chai" still appears in the Product Combo box. Again, click the New Record navigation button.</li> In the third record, select Condiment in the Category Combo combo box. Note that if you click the Previous Record navigation button to look at the first and second records, "Chai" and "Chang" no longer appear in the Product Combo box.</li></ol>

<div class="references_section">