Adding a Value to the top of a combo box

[mshmyob]

Ok need a litle help on this one guys.

I have a combo box that displays value from a Category table and the selected value will be stored in my Contact table.

No problem there that part works fine.

What I would like to do is display a value called "Not Assigned" at the top of the combo box all the time. The value of "Not Assigned" is NOT stored in the Category table. I am trying to accomplish this without storing "Not Assigned" in the Category table.

If this can be done the value of CategoryID should be assigned the number 0 and stored in the Contact table.

Category table as 3 fields

CategoryID - Autonumber - PK
CategoryDesc - Text - ie: Client, Vendor, etc.
CategoryColour - Text - used to store a hex number

CategoryID is the FK in my Contact table

The combo box currently has the following properties:

Control Source - CategoryID

Row Source - SELECT [tContactCategory].[CategoryID], [tContactCategory]. [CategoryDesc] FROM tContactCategory ORDER BY [CategoryDesc];

Bound Column - 1
Column Count - 2
Column Widths - 0,1


Any ideas?

cheers,

[ADezii]

Just subscribing, will check in later.

[ADezii]

'To display Not Assigned (leading Space)
SELECT tContactCategory.CategoryID, tContactCategory.CategoryDESC
FROM tContactCategory
UNION
SELECT '0', ' Not Assigned'
FROM tContactCategory
ORDER BY tContactCategory.CategoryDESC;

'To display (Not Assigned) 
SELECT tContactCategory.CategoryID, tContactCategory.CategoryDESC
FROM tContactCategory
UNION
SELECT '0', '(Not Assigned)'
FROM tContactCategory
ORDER BY tContactCategory.CategoryDESC;

[NeoPa]

Try this. It brings in just one line to add to the results from [tContactCategory].

SELECT [CategoryID],[CategoryDesc]
FROM tContactCategory
UNION SELECT Max(0),Max('Not Assigned')
FROM tContactCategory
ORDER BY [CategoryDesc]

Let us know how you get on with these :)

[mshmyob]

Thanks guys I won't be able to try it until about Wednesday. This personal project has to be put on the back burner for paying projects (lol). I'll let you know.

cheers,

[mshmyob]

Thanks guys. I got a few minutes tonight to look at it.

Dezi your way was the way I tried originally but I reversed my Union query and it didn't work. Your way worked so I assumed I was originally on the right track and I made a slight change to your suggestion to allow any value in the CategoryDescription and not have to worry about putting leading spaces or brackets to force it on top. I created a new orderby (See below) that takes care of the ordering and is more flexible.

Neo I couldn't get yours to work but I haven't had much time to examine it. I will look around Wednesday when I get more time to analyse it.

Thanks guys.

SELECT tContactCategory.CategoryID, tContactCategory.CategoryDESC, 1 AS SortOrder FROM tContactCategory UNION Select '0' AS CategoryID, 'Not Assigned' AS CategoryDesc, 0 FROM tContactCategory ORDER BY  SortOrder, tContactCategory.CategoryDESC;
 

cheers,

[NeoPa]

I missed that you needed it at the top :( Is that what you meant when you said you couldn't get it to work?

Try this anyway and let me know :

SELECT [CategoryID],[CategoryDesc],1 AS [CatSort]
FROM tContactCategory
UNION SELECT Max(0),Max('Not Assigned'),Max(0)
FROM tContactCategory
ORDER BY [CatSort],[CategoryDesc]

You may have to adjust the ComboBox to allow for, but not show, the third column ([CatSort]), or even reorder the columns as you see fit.