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

• Microsoft Access versions 1.0, 1.1, 2.0

SUMMARY

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

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. For more information on Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x, or the "Building Applications" manual, Chapter 3, "Introducing Access Basic," in version 2.0.

MORE INFORMATION

Microsoft Access Version 2.0

In Microsoft Access version 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:

   =[<MyCombo>].[ItemData](0)

         =[<MyCombo>].[ItemData](1)

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

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:

1. Open the Orders form in Design view.

2. Select the Salesperson combo box. Display the property sheet by choosing Properties from the View menu.

3. Set the DefaultValue property to the following expression:

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

4. View the form in Form view.

5. From the Records menu, choose Data Entry.

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

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

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:

1. Open the Orders form in Design view.

2. Select the Sales Person combo box. View the property sheet by choosing Properties from the View menu.

3. Set the DefaultValue property to the following expression:

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

4. View the form in Form view.

5. From the Records menu, choose Data Entry.

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

REFERENCES

For more information about the ItemData method, search for "ItemData," and then "ItemData Method" using the Microsoft Access Help menu.

Microsoft Access "User's Guide," version 1.0, Chapter 9, "Designing Forms," pages 233-242

Microsoft Access "User's Guide," version 1.1, Chapter 9, "Designing Forms," pages 237-246

Microsoft Access "Language Reference," versions 1.0 and 1.1, pages 131-132, 139-140, and 144-146