Solved

Access dropdown filter add choice for "all"

Posted on 2012-04-05
9
389 Views
Last Modified: 2012-04-05
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?
0
Comment
Question by:etech0
  • 6
  • 3
9 Comments
 
LVL 75
ID: 37813884
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
 
LVL 10

Author Comment

by:etech0
ID: 37813937
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
 
LVL 75
ID: 37813979
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 75
ID: 37813998
Past the SQL into a UNION Query - in the query designer window ... then run it ...


1

2
0
 
LVL 75
ID: 37814003
The above is just to test the UNION query ... and confirm it shows the desired results ... 'ALL' s/b first in list.

mx
0
 
LVL 10

Author Comment

by:etech0
ID: 37814016
Hi!
I get an error:
"The number of columns in the two selected tables or queries of a union query do not match."
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 500 total points
ID: 37814043
OOPS .... sorry, thought I saw two columns:


SELECT DISTINCT CatWebWork2CopywritersQ.Username
FROM CatWebWork2CopywritersQ
UNION SELECT "<ALL>"
FROM CatWebWork2CopywritersQ
ORDER BY 1
0
 
LVL 10

Author Closing Comment

by:etech0
ID: 37814152
Great answer - thanks!
0
 
LVL 75
ID: 37814160
You are welcome ...

mx
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now