Include (All) a the combo box

I have a combo box which reads distinctly from a table. I would like to include the actual word (All) at the top of the list so when the users selecte it they will get all the information under that column selected. Then they will click on a button which will give them the report of (All) the data in that field. Which is the fastest way to get round that either in VBA or SQL code?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Well, the Northwind database has that exact piece of code in it, so you could look at that. A coworker of mine did that and was very pleased with the results. Another possibility (though it could get ugly :o)  ) would be to do something like this:

Public Function fncValueList ()

dim rs as recordset
Dim strList as string
strList = "All;"
set rs = currentdb.openrecordset("select distinct YourField from YourTable")
if not rs.eof then
do until rs.eof
   strList = strList & rs("YourField") & ";"

fncValueList = strList

End Function

This isn't quite as nice because you have to keep regenerating the drop-down list if users will be adding new values to the field in the table. Like I said, it could get ugly, but it would work. :o)

ramromconsultant Commented:
You could also use a union select:

select "All     " from one_row_table
union select distinct YourField from YourTable

Create a table named one_row_table; put one row in it, to provide a source for the first select.

I think this is also required: enough blanks after "All to create a column wide enough for the longest field value from yourtable.
The easiest way to do this is to select 2 columns for the property sheet of the combo box.  Once you do this, make the column widths box look like this: 0";1" this will make one column invisible to the user.  Once you do that, in the row source field of the property sheet, do the following.  Say your selections are  Red, Blue, Green, All.  You would type the source in as follows: "Red","Red","Green","Green","Blue","Blue",*,"All".  Make sure you set the criteria in your query to LIKE and not =.  Let me know if you need any more help.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

sdickens: I object to your answer on two grounds:

1. I don't think it really addresses what gsazeides is trying to do. If George were using a Value List for his combo box, yours would be a viable option. The query he is referring to is not the one *being run* using the combo box, but is the one that fills the combo box with values for the drop-down list.

2. Since two other experts had submitted comments that are at least as viable as your suggestion, prudence would have suggested that you submit yours as a comment, too. I notice you're fairly new, so I'll mention that that's the general way to work things around EE unless you are quite sure you have *the* answer the questioner will want.

Welcome to EE. It's always nice to have new experts contributing.

gsazeidesAuthor Commented:
I find ramroms answer very easy and clever but the only things it does it displays the actual word ( All ) in the combo box. That works brilliantly. Now, let say I have two combo boxes (cbo1 and cbo2) reading from different fields from one table. Lets say they read Town and Country

How can I open a report bound to those results if the selections on the combo boxes are:
   cbo1 = ( All ) AND cbo2 = Germany


   cbo1 = Germany AND cbo2 = ( All )

It must be sthg like

docmd.openreport "stDocName", ????????????????????????

Then problem is how can the word ( All ) take action on the report since it doesn't actually exist in the table with the data but in the table that has one row( which is unioned )
George: for ramrom's answer, I think what you'd have to do is write an if statement, like so:

If cbo1 = "All" then
   docmd.openreport "stDocName", , "Country = '" & cbo2 & "'"
   docmd.openreport "stDocName", , "Town = '" & cbo1 & "' and Country = '" & cbo2 & "'"
end if
This question was awarded, but never cleared due to the JSP-500 errors of that time.  It was "stuck" against userID -1 versus the intended expert whom you awarded.  This corrects the problem and the expert will now receive these points; points verified.

Please click on your Member Profile and select "View Question History" to navigate through any open or locked questions you may have to update and finalize them.  If you are an EE Pro user, you can also choose Power Search to find all your open questions.

This is the Community Support link, if help is needed.  Also a question is posted there specific to these changes that apply to the experts here.  Also, I am including the link to our All Topics, since many new ones have recently been added.
Thank you,
Moderator @ Experts Exchange
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.