Solved

Access dropdown filter add choice for "all"

Posted on 2012-04-05
9
390 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

790 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