<Add New> in Combo Box Not Displayed

[Patrick]

I have a combo box with the following as its row source:

SELECT -1 as AddNewChoice, "<add new client>" as Bogus FROM
[tblClients] UNION SELECT [tblClients].[intClientID],
[tblClients].[strName] FROM [tblClients] ORDER BY [strName];

When tblClients has records in it, <add new client> is displayed at the
top of the combo box as desired. However, if tblClients is empty, <add
new client> is not displayed. What is wrong here?

Thanks,
Patrick

 

[Rob Oldfield]

The reason is that, if tblClients is empty, then any select command based on
it is going to retrieve nothing. The easy way around it is just to replace
the first from clause with a table which is guaranteed to contain records,
or maybe just create a dummy table purely for the purpose.

 

[Guest]

Your 'order by' column does not exist in the first SELECT.

Dorian

 

[Patrick]

Thanks for the input. Here is the updated, working query:

SELECT -1 as AddNewChoice, "<add new client>" as Bogus FROM
[Switchboard Items] UNION SELECT [tblClients].[intClientID],
[tblClients].[strName] FROM [tblClients] ORDER BY [strName];

As you can see, the first SELECT query is using Switchboard Items as
its FROM because I know that this table will always have items in it.