Combo box question 2

[ptrifile]

I have a combo box that is based on a query that lists people in a dept. Obviously when i change the dept number on the form the drop down changes to the appropriate list of employees. Is there a way to always include an option for "not listed" no matter what dept is chosen without adding a "not listed" to every dept in the table?

Thanks for any help!

Paul

 

[PHV]

Use an UNION query, eg:
SELECT people FROM yourTable WHERE dept=Forms!yourform!deptTextbox
UNION SELECT 'Not listed' FROM yourTable

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ptrifile]

Thanks PHV, worked wonderfully! Is there any way that i can have the "Not Listed" always appear last?

 

[Andrzejek]

How do you order people now? Do you have some kind of ID assigned to every person?

You could add to PHV's:[tt]
SELECT people,[blue] 0 As ID [/blue]
FROM yourTable
WHERE dept=Forms!yourform!deptTextbox
UNION SELECT 'Not listed', [blue]99 As ID [/blue]
FROM yourTable [blue]
Order By 2[/blue][/tt]

Have fun.

---- Andy

 

[ptrifile]

Thanks Andy. Currently I am simply ordering alphabetically and they each have an employee number. Not sure if that really answers your question or not. I also tried your suggestion but the only thing that comes up is "Not Listed" for every dept.

Thanks again!

Paul

 

[dhookom]

ptrifile,
Please post back with your SQL view and significant combo box properties. If you want ordering by people added, try:

SELECT people, 0 As ID 
FROM yourTable 
WHERE dept=Forms!yourform!deptTextbox
UNION SELECT 'Not listed', 99
FROM yourTable 
Order By 2, 1

Duane
Hook'D on Access
MS Access MVP

 

[ptrifile]

Duane, below is what you requested

This is from the query that works but is alpahbetical

SELECT name FROM tblbranchemployees WHERE payrolldept=Forms!frmmain!branch_number
UNION SELECT 'Not listed' FROM tblbranchemployees;

This is the one where only "Not Listed" is listed no matter what dept is chosen.

SELECT name, 0 As ID 
FROM tblbranchemployees 
WHERE dept=Forms!frmmain!branch_number
UNION SELECT 'Not listed', 99 As ID 
FROM tblbranchemployees
ORDER BY 2;

Thanks for your help!

 

[Andrzejek]

[tt]SELECT name FROM tblbranchemployees WHERE [red]payrolldept[/red]=Forms!frmmain!branch_number
UNION SELECT 'Not listed' FROM tblbranchemployees;

SELECT name, 0 As ID
FROM tblbranchemployees
WHERE [red]dept[/red]=Forms!frmmain!branch_number
UNION SELECT 'Not listed', 99 As ID
FROM tblbranchemployees
ORDER BY 2;[/tt]

Which one is it: payrolldept or dept?

Have fun.

---- Andy

 

[ptrifile]

Thank you very much Andy! That was the issue, I didnt have the correct name there. Man, today has been a long week. Thanks again Andy!