How to have an 'All' Option in a combo box

[dino_christou]

Can anyway point me in the right direction??

I have a combo box controlled by a table, the table has various columns within it. One is Employee name, I am using a combo box listing the employee names on a form which controls filtering for a report. Its fine if I or another user wants to select one employee name, but would also like the option of a 'All' criteria, is this possible?

Many Thanks in advance.

Dino

 

[Kiwiman]

I have the following in one of mine. Put in the row source of the combobox and adjsut accordingly. Putting the all in brackets, puts it at the top.

SELECT tblProducts.ProdID, tblProducts.Product FROM tblProducts; UNION SELECT "*", "(All)" FROM tblProducts;

 

[dino_christou]

Hi Kiwiman,

Thaks for the fast reply (again).
I tried to put your the same criteria as you have but keep getting an error

"Syntax error (missing operator) in query expression 'tblAgent Details. Notes Name"

I have the following in my row source:

SELECT tblAgent Details.Notes Name, tblAgent Details.Status FROM tblAgent Details; UNION SELECT "*", "(All)" FROM tblAgent Details;

Also I do not want to show Status as I am going to apply a filter for Yes/No (as with your previous help you gave to my other thread)

Many Thanks
Dino

 

[Kiwiman]

It is probable to do with the sapces in both the table name and the table fields - a real bad idea to have spaces as you will get into trouble somewhere along the way.

Your syntax should be something like:

SELECT [tblAgent Details].[Notes Name], [tblAgent Details].[Status] FROM [tblAgent Details]; UNION SELECT "*", "(All)" FROM [tblAgent Details];

similar to below:

SELECT [Test Data].[Test Field]
FROM [Test Data];

 

[dino_christou]

Thanks for the reply again.

I took your advice and removed the spaces from the table name and fields.
I then inserted a combo box via the wizard to get the simple list of agents, which is fine. When I then try tag the rest of the code on, I get another error message:

"The number of columns in the two selected tables or queries of a union query do not match"

This is what I have:

SELECT AgentDetails.NotesName FROM AgentDetails; UNION SELECT "*", "(All)" FROM AgentDetails;

Really not sure why I am struggling with this one sorry.

 

[Kiwiman]

In a union query the column count has to be the same for each union. If you note in the one above, you have one column in the first one and two in the second

SELECT tblAgentDetails.Notes Name, tblAgentDetails.Status FROM tblAgentDetails; UNION SELECT "*", "(All)" FROM tblAgent Details;

This approach relies on having two columns in your union - even if it is the same field appearing twice. Then you set your bound column and column widths property

 

[dino_christou]

I see my mistake now doh!

Thank you again Kiwiman.
I finally got there.

Cheers
Dino