You are currently offline, waiting for your internet to reconnect

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

This article was previously published under Q105519
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
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 valueselected. The combo box or list box may have a table or query defined inits RowSource property that provides the list of items to be displayed inthe box. Because the data in the underlying RowSource property will varywith the addition or deletion of records, it is difficult to know what itemwill 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 tothe first row in the underlying list. The methods outlined in this articlework only if the combo box is bound to a field.

This article assumes that you are familiar with Access Basic and withcreating Microsoft Access applications using the programming tools providedwith Microsoft Access.
MORE INFORMATION

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!<ControlName> = Me!<ControlName>.ItemData(0)				
NOTE: When the field names in a combo box are used as column headings the syntax would be as follows:
Me!<ControlName> = Me!<ControlName>.ItemData(1)				

In Microsoft Access 2.0

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

In Microsoft Access Version 1.x

The first method below uses a user-defined Access Basic function, and thesecond 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 calledGetFirst() that can be used to find the first item in the underlyingtable or query. The function's result can be used by the DefaultValueproperty to automatically select the first item in the list.

To create the GetFirst() function, add the following lines to a new orexisting 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 ofthe field that is used as the BoundColumn property for the combo box. Thesecond argument is the name of the table or query specified in theRowSource property of the combo box.

The following example demonstrates how to use the GetFirst() function toautomatically select the first employee in the Salesperson combo box on theOrders 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.
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 whenused 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 inthe actual order in which they were entered in the database. Therefore, ifthe RowSource property of a combo box is a query that sorts the data by anon-indexed field, DFirst() may not return the expected value.

For example, if you change the DefaultValue property of the Salespersoncombo box on the Orders form to
   =DFirst("[Employee ID]", "Employee List")				
the item returned will be "Davolio, Nancy," which is not the first item inthe 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:
  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.
listbox combobox
Properties

Article ID: 105519 - Last Review: 01/18/2007 22:40:42 - Revision: 2.1

  • 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
  • kbhowto kbusage KB105519
Feedback