Microsoft KB Archive/105519

= ACC: How to Make a Combo Box Default to First Item in List =

Article ID: 105519

Article Last Modified on 1/18/2007

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q105519



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



SUMMARY
When you move to a new record on a form that has a combo box or a list box, the combo box will be blank or the list box will not have any value selected. The combo box or list box may have a table or query defined in its RowSource property that provides the list of items to be displayed in the box. Because the data in the underlying RowSource property will vary with the addition or deletion of records, it is difficult to know what item will appear at the top of the list when the form is used.

This article describes how to force a list box or combo box to default to the first row in the underlying list. The methods outlined in this article work only if the combo box is bound to a field.

This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access.



In Microsoft Access 95 and 97
In Microsoft Access 95 and 97, you can use the ItemData method to cause a list box or combo box to default to any row. To have the first row selected automatically, add the following code to the OnCurrent event of a form: Me! = Me!.ItemData(0) NOTE: When the field names in a combo box are used as column headings the syntax would be as follows: Me! = Me!.ItemData(1)

In Microsoft Access 2.0
In Microsoft Access 2.0, you can use the ItemData method to cause a list box or combo box to default to any row. To have the first row selected automatically, set the box's DefaultValue property as follows:   =[].[ItemData](0) NOTE: When the field names in a combo box are used as column headings the syntax would be as follows:

=[].[ItemData](1)

In Microsoft Access Version 1.x
The first method below uses a user-defined Access Basic function, and the second method uses the built-in DLookUp function to display the first value in the list automatically.

Method 1:

The following example demonstrates a sample Access Basic function called GetFirst that can be used to find the first item in the underlying table or query. The function's result can be used by the DefaultValue property to automatically select the first item in the list.

To create the GetFirst function, add the following lines to a new or existing module: Option Explicit

Function GetFirst (BoundColName As String, RowSource As String) Dim DB As Database Dim DS As Dynaset

Set DB = CurrentDB Set DS = DB.CreateDynaset(RowSource)

On Error Resume Next DS.MoveFirst If Err = 0 Then GetFirst = DS(BoundColName)

End Function Note that the first argument of the GetFirst function is the name of the field that is used as the BoundColumn property for the combo box. The second argument is the name of the table or query specified in the RowSource property of the combo box.

The following example demonstrates how to use the GetFirst function to automatically select the first employee in the Salesperson combo box on the Orders form in the sample database NWIND.MDB:  Open the Orders form in Design view. Select the Salesperson combo box. Display the property sheet by choosing Properties from the View menu. Set the DefaultValue property to the following expression:

=GetFirst("Employee ID", "Employee List")

</li> View the form in Form view.</li> From the Records menu, choose Data Entry.</li></ol>

Note that the combo box automatically displays "Buchanan, B.L."

Differences Between GetFirst and DFirst:

The GetFirst function is similar to the built-in DFirst aggregate (totals) function. However, DFirst may return unexpected results when used to find the first item in a list.

If the underlying table or query is indexed, the value returned by DFirst will be the first indexed record. Otherwise, DFirst will return items in the actual order in which they were entered in the database. Therefore, if the RowSource property of a combo box is a query that sorts the data by a non-indexed field, DFirst may not return the expected value.

For example, if you change the DefaultValue property of the Salesperson combo box on the Orders form to <pre class="fixed_text">  =DFirst("[Employee ID]", "Employee List") the item returned will be "Davolio, Nancy," which is not the first item in the combo box, but the first indexed item in the Employees table.

Method 2:

This method uses the DLookUp function to look up the first record in the list. The expression will be the field referred to in the BoundColumn property (or the ControlSource property) of the combo box or list box. The domain will be the same table or query that the combo box or list box uses as its RowSource property. The optional criteria will not be used so that the DLookUp function will return the first record.

The following example demonstrates how to use DLookUp to automatically select the first employee in the Salesperson combo box on the Orders form in the sample database NWIND.MDB:

<ol> Open the Orders form in Design view.</li> Select the Sales Person combo box. View the property sheet by choosing Properties from the View menu.</li> Set the DefaultValue property to the following expression:

=DLookUp("[Employee ID]","Employee List")

</li> View the form in Form view.</li> From the Records menu, choose Data Entry.

Note that the combo box automatically displays "Buchanan, B.L."</li></ol>

<div class="references_section">