Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. ACC2000: How to Include a Null Selection in a Combo Box List

ACC2000: How to Include a Null Selection in a Combo Box List

View products that this article applies to.

This article was previously published under Q208707
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

↑ Back to the top

Summary

This article demonstrates how to include a blank row at the top of a combo box list. This enables you to set the combo box's LimitToList property to Yes, and still be able to delete the contents of the combo box without triggering the NotInList event.

This feature is created by basing the contents of the combo box on a union query that adds a null row to the top of the query.

↑ Back to the top

More information

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

To include a blank row at the top of a combo box, follow these steps:
  1. Open the sample database Northwind.mdb.
  2. Create the following query, and then save it as qryCustList.
    Query: qryCustList
-------------------
Type: Select Query

Field: CustomerID
   Table: Customers
Field: CompanyName
   Table: Customers
   Sort: Ascending
  3. On the View menu, click SQL View, and then modify the SQL statement so that it reads as follows: 
    SELECT DISTINCTROW Customers.[CustomerID], Customers.[CompanyName]
FROM Customers
UNION SELECT Null, Null FROM Customers
ORDER BY Customers.[CompanyName];
    When you run this query, the combo box list is displayed with a top row of Null values, followed by the remainder of the customer list.
  4. Create the following new form, not based on any table or query, and then save it as Test1.
    Form: Test1
------------------------------
      
Combo box:
   Name: cmbTest
   RowSourceType: Table/Query
   RowSource: qryCustList
   LimitToList: Yes
Text Box:
   Name: Field1
  5. Open the Test1 form in Form view.
  6. Make a selection from the cmbTest combo box list, and then press TAB to move to the text box control. Note that this works as expected.
  7. Return to the cmbTest combo box list, press DELETE, and then press TAB to move to the text box control.

    NOTE: By pressing DELETE, you have actually selected the first row in the list's RowSource: qryCustList. If you click the expand arrow in the combo box, a blank space now appears at the top of the list.

↑ Back to the top

References

For more information about union queries, click Microsoft Access Help on the Help menu, type union query in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

↑ Back to the top

Keywords: KB208707, kbinfo, kbhowto

↑ Back to the top

Article Info
Article ID : 208707
Revision : 2
Created on : 6/29/2004
Published on : 6/29/2004
Exists online : False
Views : 273