Using (ALL) in combo boxes.

[Homegrownandy]

I've been looking at/trying several methods to have the ability to show all results in a combo box.



So far I do have the option in the combo box to select all. I can also select my other options individually (individual selections work).

But when I select all, no results are returned.

This is the SQL:

SELECT [ProjectT].[Project_ID], [ProjectT].[Job_Type] FROM ProjectT UNION Select Null as AllChoice, "(All)" as Bogus From ProjectT ORDER BY Project_ID;

do I need a table called bogus for this to work? (which I do have)

If so, what needs to be on it?

Hopefully someone can help, cheers.

[Mahendra1000]

Dear Andy,

The Posts in this thread might help you. Am not sure about it

https://www.accessforums.net/forms/h...box-55366.html

[Homegrownandy]

Thanks for the post mate, but I have tried everything suggested there.. Possibly incorrectly..

[orange]

see this from mvps

[JamesDeckert]

show your sql

No, you don't need a bogus table just reference ProjectT. You are not getting any real info from it, so why make/reference another table

[Homegrownandy]

Hi James. I was following tutorials that said to reference a bogus table. I don't know the reason why.

Orange, This is one I have looked at previously. Following that I do get the all option, but when I requery with all selected I return no results.

Ill try this one again from the start and post any updates.

Edit: No luck

SELECT Project_ID, Job_Type FROM ProjectT UNION Select Null as AllChoice, "(All)" as Bogus From ProjectT ORDER BY Project_ID;

Again all is there as an option. But when clicked no text shows in the combo box. It's blank and no results are generated.

[JamesDeckert]

How are you getting your results to show?
There are two pieces to the puzzle.
1. Have combobox which shows options.
2. Uses combobox selection to filter data.
It looks like you have step 1 working, how are you accomplishing step 2?
It is in step 2 code that you look for "(All)" and do a query which returns all records.

[Homegrownandy]

the combo box selection returns a value from a table/query.

this is is the query:

SELECT JobT.Scheduled_Start, SiteT.Site_Name, ProjectT.Job_Type, BatchT.Batch_Number, ProjectT.Project_ID, JobT.Job_ID
FROM ProjectT INNER JOIN (BatchT INNER JOIN (SiteT INNER JOIN JobT ON SiteT.Site_ID = JobT.[Site_ID]) ON BatchT.[Batch ID] = JobT.Batch_ID) ON ProjectT.Project_ID = BatchT.Project_ID
WHERE (((JobT.Scheduled_Start) Between Date() And Date()+14) AND ((ProjectT.Project_ID) Like [Forms]![SchedulerMenuF]![Project_Combo]))
ORDER BY JobT.Scheduled_Start;

the reason its set to "like" is because the default value is * which does return all records. But then after the user selects an option in the combo box, the form must be closed and opened to get all again.

This is why I'm trying to get an 'all' option.

[Homegrownandy]

James (or anyone else):

https://www.accessforums.net/sample-...tml#post296181

here if you click job scheduler there is a combo box on the form that comes up that filters the results. This is where I'm trying to get the all to function.

Thanks.

Edit: Its the after update event that needs VBA code. I thought the union query was pulling together the results before. Orange explained to me where I was going wrong.

-Still have no idea what code I need xD

[orange]

I just posted a sample database showing use of All.

[Paul H]

SELECT Project_ID, Job_Type FROM ProjectT UNION Select Null as AllChoice, "(All)" as Bogus From ProjectT ORDER BY Project_ID;

I'm assuming this is your rowsource for your combo box. I've been using something very close to this which works with the "Like" statement.

SELECT Project_ID, Job_Type FROM ProjectT UNION SELECT "*" , "(All)"  From ProjectT ORDER BY Project_ID;

Is this what you're looking for?