Microsoft KB Archive/128197

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

Article ID: 128197

Article Last Modified on 7/5/2002

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q128197



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



SYMPTOMS
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:

 Base the combo box on a query. 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. Place all other fields you want to include in the combo box in the third and successive columns.  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 

Because the ColumnWidths property for column 1 is set to 0, Microsoft Access uses the second column in the query to display text in the text portion of the combo box.

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.

<div class="status_section">

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

<div class="moreinformation_section">

Steps to Reproduce Behavior
  Open the sample database NWIND.MDB, and create a new table called MyTable as follows:

<pre class="fixed_text">     Table: MyTable ---     Name: MyDate Data Type: Date/Time Format: Medium Date </li> Create a new query called MyQuery based on the Orders table. Include the Order Date field in the first column in the query grid.</li>  Create a new form with the following property:

<pre class="fixed_text">     RecordSource: MyTable </li>  Create a combo box on the form with the following properties:

<pre class="fixed_text">     Name: MyCombo ControlSource: MyDate RowSourceType: Table/Query RowSource: MyQuery ColumnCount: 1 BoundColumn: 1 </li> View the form in Form view.</li> Click the drop-down arrow on the combo box. Note that the dates in the combo box list appear in Medium Date format.</li> Select any date. Note that the date you selected appears in Short Date format in the text portion of the combo box.</li></ol>

How to Correct the Behavior
 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.</li>  Change the MyCombo combo box to include the following property settings:

<pre class="fixed_text">     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. </li> View the form in Form view.</li> Click the drop-down arrow on the combo box. Note that the dates in the combo box list appear in Medium Date format.</li> Select any date. Note that the date you select appears in Medium Date format in the text portion of the combo box.</li></ol>

<div class="references_section">