Combo Box Blank Selection At The Top

[OasisUnknown]

Hello All,

Is there a way to create a combo box that gets its values from a query that either
1. always displays all the values from the query even if filtered.
(basically I have a set of departments and each department is listed in the combo box at the start. When I select a department the query filters to only show that department and then my combo box adjusts (not intentionally) so when I go to select another department it only shows the one selected.)

I would like to avoid making another query just to populate the list when the query by default already has everything I need.

2. Is it possible to force a blank selection at the top of a combo box so I can select a department to filter and then just select the blank to un filter the query. (I could use this in several places in my database)

or

3. is the best option to just include a clear button that clears the filters off the query.

 

[xenou]

You can bring a blank (or something similar value to the top) if you have a query to source your form (*and* it can reasonably be sorted so that the required field sorts first in the list).

Example:

SELECT "(Choose a Department)" as Department FROM Dual
UNION ALL
SELECT Department from Departments
ORDER BY Department

In this case Dual is an otherwise useless table I create in my database, following an old Oracle convention.

The DUAL table is a special one-row table present by default in all Oracle database installations. It is suitable for use in selecting a pseudocolumn such as SYSDATE or USER. The table has a single VARCHAR2(1) column called DUMMY that has a value of 'X'.

How does the Oracle DUAL table work? - Database Administrators Stack Exchange

In my table Dual, I usually make the field Dummy a primary key and set a validation rule that it's value must be "X", so that it is not even possible to add more rows to the table.

For what it's worth, also usually right-clicking in a field brings up all the inbuilt filtering functionality that you need, so you don't need to do all this yourself necessarily.