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

Last reviewed: May 14, 1997
Article ID: Q139039
The information in this article applies to:
  • Microsoft Access versions 2.0, 7.0, 97

SUMMARY

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

This article demonstrates how you can 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.

MORE INFORMATION

To include a blank row at the top of a combo box, follow these steps.

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb (or Nwind.mdb in version 2.0 or earlier). You may want to back up the Northwind.mdb (or Nwind.mdb) file and perform these steps on a copy of the database.

  1. Open the sample database Northwind.mdb (or Nwind.mdb in 2.0).

  2. Create the following query and save it as qryCustList:

          Query: qryCustList
          -------------------
          Type: Select Query
    

          Field: CustomerID
    
             Table: Customers
          Field: CompanyName
             Table: Customers
             Sort: Ascending
    
       NOTE: In Microsoft Access 2.0, there is a space in the Customer ID and
       the Company Name fields.
    
    

  3. On the View menu, click SQL View (or SQL in pre-97 versions) and 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];
    

          NOTE: In Microsoft Access 2.0, type a space in the Customer ID and
          the Company Name fields.
    

    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 a new form not based on a table or query and save it as Test1:

          Form: Test1
          ------------------------------
          Caption: TestForm
    

          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 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.

  8. Return to the cmbTest combo box list and enter "Test" (without the quotation marks). Press TAB to move to the text box control. Note that you receive the following error message, indicating that the LimitToList feature is enabled:

          In Microsoft Access 97 and 7.0:
    

             The text you entered isn't an item in the list.
    
          In Microsoft Access 2.0:
    
             The text you enter must match an entry in the list.
    
    

REFERENCES

For more information about union queries, type "Union Query," in the Office Assistant, click Search, and then click to view the appropriate topic.


Additional query words: kbhowto delete combo box notinlist
Keywords : FmsCmbo kberrmsg kbusage
Version : 2.0 7.0 97
Platform : WINDOWS
Hardware : X86
Issue type : kbhowto


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: May 14, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.