ACC2000: Combo Box in Continuous Form Shows Incorrect Data (208866)


The information in this article applies to:

  • Microsoft Access 2000
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.

MORE INFORMATION

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.
  1. Start Microsoft Access and open the sample database Northwind.mdb.
  2. 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
  3. 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.
  4. Create and save the following new macro:
       Macro Name: Refresh Product Combo

   Action         Arguments
   -----------------------------------------
   Requery       Control Name: Product Combo
  5. Create a new form with the following properties and controls based on the My Products table. Save the form as My Products Form.
       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
  6. View the form in Form view.
  7. 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.
  8. 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.
  9. 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.

REFERENCES

For additional information about the AutoLookup technique, click the article number below to view the article in the Microsoft Knowledge Base:

209672 ACC2000: Example of Row Fix-Up in the Northwind Orders Form

For more information about the autolookup technique, click Microsoft Access Help on the Help menu, type create an autolookup query that automatically fills in data in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.
Modification Type:MajorLast Reviewed:6/24/2004
Keywords:kbprb kbusage KB208866
©2004 Microsoft Corporation. All rights reserved.