How to add one more item to row source of combo box that is referenced to a field

[ali_sh]

Hello


I have created a search form including ComboBox that is used to search based of, and I referenced row source of that to a field of a table and I want to add "ALL" item to list. Is there any SQL code to do that like using "UNION" operator?
Thanks in advance

[ranman256]

if the rowsource is a query to a table, put a button beside the combo to open the source table,
the user adds the new entry,
close the table/query
click REFRESH ALL button in the top toolbar,
new item is in the list/combo.

[accesstos]

Is there any SQL code to do that like using "UNION" operator?

Of course!

SELECT "All"  FROM theTable 
UNION 
SELECT DISTINCT theField FROM theTable;

[ali_sh]

Of course!

SELECT "All"  FROM theTable 
UNION 
SELECT DISTINCT theField FROM theTable;

that worked and this is exactly what I want, thank you.

I change that to following code:

SELECT "All"  FROM theTable 
UNION 
SELECT DISTINCT Field_1 FROM theTable ORDER BY theTable.Field_2;

the part after UNION was working but when I added the new code line and UNION to the first, I got this error:

The ORDER BY expression (theTable.Field_2) includes fiels that are not selected by the uery. Only those fields reuested in the first query can be included in an ORDER BY expression.

I'll be grateful if you help me on this too.

[accesstos]

Happy to help!

The best way to keep the list sorted and the word "All" at the top, is to create a separate query with SQL:

SELECT DISTINCT Field_1 FROM theTable ORDER BY theTable.Field_2

and use its name in place of the "theTable". For example:

SELECT "All"  FROM theTable 
UNION 
SELECT DISTINCT Field_1 FROM theQuery;

Cheers,
John