Access dropdown filter add choice for "all"

Hi!
I have an Access dropdown box that's based on a distinct list of all records in the query that is the datasource for that form.
I want that dropdown box to serve as a filter on the data that will show up in the form. (It's a continuous form.)
How do I do this?

Also, I'd like one of the choices to be "ALL", and to not filter the data, but to show all records.
How can I accomplish this?
LVL 10
etech0Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
OOPS .... sorry, thought I saw two columns:


SELECT DISTINCT CatWebWork2CopywritersQ.Username
FROM CatWebWork2CopywritersQ
UNION SELECT "<ALL>"
FROM CatWebWork2CopywritersQ
ORDER BY 1
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
For starters, here is how you get ALL - two examples:

SELECT DISTINCT CLng(EmpID), 2 FROM tblEmp
UNION SELECT "ALL", 1 FROM tblEMP
ORDER BY 2;


SELECT EmpName, 5 FROM tblEmp
UNION SELECT "<Add New>", 0 FROM tblEMP
UNION SELECT "-----------", 1 FROM tblEMP
UNION SELECT "<Select All>", 2 FROM tblEMP
UNION SELECT "-----------", 3 FROM tblEMP
ORDER BY 2;

Then, you need to handle the "ALL" case in the Combo box's AfterUpdate event ... to do what ever.

mx
0
 
etech0Author Commented:
I'm having a hard time understanding your code. Do I need to put both sections in?
Here is what I have so far:

SELECT DISTINCT CatWebWork2CopywritersQ.Username FROM CatWebWork2CopywritersQ ORDER BY CatWebWork2CopywritersQ.Username;

Open in new window

0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
No ... the 2nd example was just an expanded example showing what 'can be done' - if you ever need more options.


SELECT DISTINCT CatWebWork2CopywritersQ.Username
FROM CatWebWork2CopywritersQ
UNION SELECT "<ALL>", "------------"
FROM CatWebWork2CopywritersQ
ORDER BY 2

Note: The orderby 2 means the 2nd column ... shortcut notation.

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Past the SQL into a UNION Query - in the query designer window ... then run it ...


1

2
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
The above is just to test the UNION query ... and confirm it shows the desired results ... 'ALL' s/b first in list.

mx
0
 
etech0Author Commented:
Hi!
I get an error:
"The number of columns in the two selected tables or queries of a union query do not match."
0
 
etech0Author Commented:
Great answer - thanks!
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
You are welcome ...

mx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.