ACC: Combo Box in Continuous Form Shows Incorrect Data

ID: Q128158


The information in this article applies to:
  • Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97


SYMPTOMS

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

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.


MORE INFORMATION

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.
  1. Open the sample database Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0).


  2. 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 


  3. 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.


  4. Create the following new macro:
    
          Macro Name              Macro Action
          ------------------------------------
          Refresh Product Combo   Requery
    
          Refresh Product Combo Actions
          ------------------------------
          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:
             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 


  6. View the form in Form view.


  7. 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.


  8. 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.


  9. 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).



REFERENCES

For more information about the AutoLookup technique, please see the following article in the Microsoft Knowledge Base:

Q95643 ACC: Row Fix-up in the Northwind.mdb Order Form

For more information about AutoLookup, search the Help Index for "AutoLookup queries," or ask the Microsoft Access 97 Office Assistant.

Additional query words: blank

Keywords : kbusage FmsCmbo
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Issue type : kbprb


Last Reviewed: April 9, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.