ACC2: Format of Data in Text Portion of Combo Box Is Incorrect

• Microsoft Access version 2.0

SYMPTOMS

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

When you select a value from a combo box list, the selection that appears in the text portion of the combo box is not formatted as it appears in the combo box list.

For example, if you select a date that is formatted as a Medium Date in the table and in the combo box list (such as 10-May-91) the date appears in the text portion of the combo box in Short Date format (5/10/91).

CAUSE

Unlike a text box, a combo box does not have its own Format property. As a result, if the combo box is bound to a table field, the value that appears in the text portion of the combo box uses the format of the data as it is stored in the table, ignoring any format defined at the table or query level.

RESOLUTION

To correct this behavior, use the following steps to base the format for a value in a combo box on a query rather than on a table:

1. Base the combo box on a query.

2. Place the field you want to make the bound column in the combo box in both the first and second columns in the query grid.

3. Place all other fields you want to include in the combo box in the third and successive columns.

4. In the combo box, make the first column the bound column and hide it from view by setting the following properties for the combo box:

         BoundColumn:  1
         ColumnWidths: 0
   

In the query, you can set Format properties for the second column to display values using the formats you want.

An example of this technique is demonstrated in the "Steps to Reproduce Behavior" and "How to Correct the Behavior" sections later in this article.

STATUS

This behavior no longer occurs in Microsoft Access version 7.0 with the addition of the new Format property for combo boxes.

MORE INFORMATION

Steps to Reproduce Behavior

1. Open the sample database NWIND.MDB, and create a new table called MyTable as follows:

         Table: MyTable
         -----------------------
         Name: MyDate
            Data Type: Date/Time
            Format: Medium Date
   
   

2. Create a new query called MyQuery based on the Orders table. Include the Order Date field in the first column in the query grid.

3. Create a new form with the following property:

         RecordSource: MyTable
   

4. Create a combo box on the form with the following properties:

         Name: MyCombo
         ControlSource: MyDate
         RowSourceType: Table/Query
         RowSource: MyQuery
         ColumnCount: 1
         BoundColumn: 1
   

5. View the form in Form view.

6. Click the drop-down arrow on the combo box. Note that the dates in the combo box list appear in Medium Date format.

7. Select any date. Note that the date you selected appears in Short Date format in the text portion of the combo box.

How to Correct the Behavior

1. Change the MyQuery query to include the Order Date (bound column) field in both the first and second columns of the query grid.

   Note that you can format the second column in the query grid (the copy of the Order Date field) by using the right mouse button to click the second column and then choosing Properties on the menu that appears.

   If you do not specify a format, the field inherits the format defined for the field in the underlying table. In the case of the Order Date field, the format is Medium Date.

2. Change the MyCombo combo box to include the following property settings:

         ColumnCount: 2
         BoundColumn: 1
         ColumnWidths: 0 in; 1 in
   

   Note that this hides the first column, which is the bound column, by setting the width of the column to 0.

3. View the form in Form view.

4. Click the drop-down arrow on the combo box. Note that the dates in the combo box list appear in Medium Date format.

5. Select any date. Note that the date you select appears in Medium Date format in the text portion of the combo box.

REFERENCES