Solved

Access dropdown filter add choice for "all"

Posted on 2012-04-05
9
393 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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 - Microsoft MVP, Access and Data Platform) 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

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

717 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