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.
Back to the top
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 field names.
|
| 3. | On the View menu, click SQL View (or SQL in versions 7.0 or 2.0) 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 field names.
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 any 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.
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.
|
Back to the top
For more information about union queries, type "Union Query," in the
Office Assistant, click Search, and then click to view the appropriate
topic.
Back to the top
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.
Help and Support
