Full Version: Add entry for "all" to a list box
UtterAccess Forums > Microsoft® Access > Access Forms
markmcrobie
Subject says it all really, but basically I have a list box where I click an entry, then press a button to open a form that uses your selection to determine which records show on the 2nd form.
want to retain this, but have an entry for "All" at the top.
Cheers
MicroE
Check Out:
http://www.mvps.org/access/forms/frm0043.htm
markmcrobie
Sorry, I don't follow that, makes no sense to me.
My current list box RowSourceType is Table/Query, and the Row Source is:
SELECT DISTINCTROW [ProjectsWhichDontEqual].[Project] FROM [ProjectsWhichDontEqual];
if that helps.
Many thanks.
MicroE
Change your Row Source to:

SELECT DISTINCTROW [ProjectsWhichDontEqual].[Project] FROM [ProjectsWhichDontEqual] UNION Select "(All)" as Bogus From ProjectsWhichDontEqual
markmcrobie
Doesn't work - I don't get errors, and (All) appears at the top of the list, but when I select all then click my Ok button to open the 2nd form, it has no results.
licking another entry as normal then Ok, works as before.
fkegley
But you're still going to have to modify the Row Source for the 2nd form to deal with "(All)".
My suggestion would be two queries, one of which responds to "(All)", one which handles the other cases. You would then check the contents of the list box, if it is "(All)", run the query with no restrictions, if not, run the other one.
markmcrobie
Not sure I follow, I think I'll just give up!
MicroE
You could also recode your command button to something like:
CODE
If Me![Your Listbox] = "(All)" Then
        DoCmd.OpenForm "Your 2nd Form"
    Else
        DoCmd.OpenForm "Your 2nd Form", , , "[ProjectsWhichDontEqual]='" & Me![Your Listbox] & "'"
    End If
markmcrobie
Doesn't work :-(
dashiellx2000
You could also try using a Dynamic Query . This would allow you to not have to select anything in the combo box and that would translate to All in the query.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.