Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-25-2019, 07:49 PM   #1
jimbo92
Newly Registered User
 
Join Date: Oct 2019
Posts: 7
Thanks: 4
Thanked 0 Times in 0 Posts
jimbo92 is on a distinguished road
Need "Select All Option" in Combobox for Query Parameters

I've seen this question posted elsewhere, but I can't make any of the answers work (I'm a beginner with Access).

I have a combobox that lets you select an employee, which gives the selected employee name as the parameter for a query. I want to have a "Select All" option in the combobox that tells the query to use all employees. This is my current Row Source code for the combobox:

SELECT [Employee Directory].[ID], [Employee Directory].[Employee Name] FROM [Employee Directory] ORDER BY [Employee Name];

I've tried a couple different solutions from other threads--including adding UNION--but can't make it work. I also don't know if code needs to be added elsewhere as well. How do I make this work?

jimbo92 is offline   Reply With Quote
Old 10-25-2019, 08:51 PM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,410
Thanks: 58
Thanked 1,408 Times in 1,389 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Need "Select All Option" in Combobox for Query Parameters

Hi. Welcome to AWF! Yes, a UNION query is usually the correct approach for this. But if you're not sure how to implement it, then the simplest approach is to leave the control empty to represent a Select All choice.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 10-25-2019, 08:53 PM   #3
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,186
Thanks: 10
Thanked 226 Times in 214 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Need "Select All Option" in Combobox for Query Parameters

Saying I couldn't get anything to work doesn't help. You should post what you tried so as to give some insight as to the table(s) involved. Also, you can't do this with a query because passing "Select All" to it isn't going to give you all employees, is it? So is the problem how to get Select All in the list, or is it how to get the query to work if you choose that value?

__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
Old 10-26-2019, 12:11 AM   #4
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Need "Select All Option" in Combobox for Query Parameters

Code:
SELECT [Employee Directory].[ID], [Employee Directory].[Employee Name] 
FROM [Employee Directory] 
WHERE [Employee Directory].[Employee Name] = 
IIF([FORMS]![yourForm]![yourCombo] = "Select All", [Employee Name], 
[FORMS]![yourForm]![yourCombo])
ORDER BY [Employee Name];
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 10-26-2019, 07:37 AM   #5
jimbo92
Newly Registered User
 
Join Date: Oct 2019
Posts: 7
Thanks: 4
Thanked 0 Times in 0 Posts
jimbo92 is on a distinguished road
Re: Need "Select All Option" in Combobox for Query Parameters

Ok, so here's more info about what I've tried.

I can get an (All) option to appear in the ComboBox with this code in the Row Source for the ComboBox:

SELECT [Employee Directory].[ID], [Employee Directory].[Employee Name] FROM [Employee Directory] ORDER BY [Employee Name] UNION SELECT Null as AllChoice , "(All)" as Bogus FROM [Employee Directory];

I can't make the query work though. I assume the query doesn't know what "(All)" means unless given more instructions. The code that arnelgp provided makes sense to me if I'm understanding it correctly, but where do I put that code?
jimbo92 is offline   Reply With Quote
Old 10-26-2019, 08:01 AM   #6
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Need "Select All Option" in Combobox for Query Parameters

SELECT [Employee Directory].[ID], [Employee Directory].[Employee Name]
FROM [Employee Directory]
WHERE [ID] = IIF(Trim([FORMS]!yourFormName!comboName & "")="", [ID], [FORMS]!yourFormName!comboName)
ORDER BY [Employee Name]
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
jimbo92 (10-26-2019)
Old 10-26-2019, 08:05 AM   #7
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,186
Thanks: 10
Thanked 226 Times in 214 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Need "Select All Option" in Combobox for Query Parameters

he is off line at the moment so I'll assume it's OK to answer on his behalf. You said you had a query - he posted what your query ought to look like if you have the combo working. However, it doesn't look right to me. I interpret the IIF as "IF the combo value is "Select All" then use the employee name. IF not, use the combo value. There is no employee named Select All.

As I said, I don't think this can be done in a query because in the case of an actual name, you need a Where clause. When it's Select All, you can't use a Where clause. IMHO the query has to be built in code to use (or not) the Where clause.
EDIT - forgot to mention that if your combo bound field contains Null because of your Union query, then no employee has an ID value of Null. Untested, but I see that as being another block to using a query.

__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
The Following User Says Thank You to Micron For This Useful Post:
jimbo92 (10-26-2019)
Old 10-26-2019, 08:38 AM   #8
jimbo92
Newly Registered User
 
Join Date: Oct 2019
Posts: 7
Thanks: 4
Thanked 0 Times in 0 Posts
jimbo92 is on a distinguished road
Re: Need "Select All Option" in Combobox for Query Parameters

Ok, been trying to adapt arnelgp's code and no luck yet. Here is the exact code I have currently for ComboBox Row Source:

SELECT [Employee Directory].[ID], [Employee Directory].[Employee Name] FROM [Employee Directory] ORDER BY [Employee Name] UNION SELECT Null as AllChoice , "(All)" as Bogus FROM [Employee Directory];

and the code for the Query:

SELECT [Employee Expenses].[Entry Number], [Employee Expenses].[Account Name], [Employee Expenses].[Employee Name], [Employee Expenses].[Work Date], [Employee Expenses].[Hours Worked], [Employee Expenses].[Pay Per Hour], [Employee Expenses].[Amount Due], [Employee Expenses].Notes
FROM [Employee Expenses]
WHERE ((([Employee Expenses].[Employee Name])=[Forms]![Main Menu]![EmployeeEntered]) AND (([Employee Expenses].[Work Date]) Between [Forms]![Main Menu]![EmpStartDate] And [Forms]![Main Menu]![EmpEndDate])) OR ((([Forms]![Main Menu]![EmployeeEntered]) Is Null))
ORDER BY [Employee Expenses].[Employee Name], [Employee Expenses].[Work Date];

With arnelgp's code for the query, it prompts me to enter a parameter value and the query doesn't work.
jimbo92 is offline   Reply With Quote
Old 10-26-2019, 09:03 AM   #9
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,233
Thanks: 115
Thanked 3,072 Times in 2,790 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Need "Select All Option" in Combobox for Query Parameters

Last post was moderated. Posting this to trigger email notifications.

In the combo after update event, I suggest you use something like this

Code:
If Me.comboname.Column(1) = "All" Then
  'use query with no WHERE filter so all records are selected
Else
  'use query with WHERE filter for value in combo
End If
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)

Last edited by isladogs; 10-26-2019 at 09:42 AM.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
jimbo92 (10-26-2019)
Old 10-26-2019, 09:20 AM   #10
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Need "Select All Option" in Combobox for Query Parameters

what is the Combo name and the Bound Column number?
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 10-26-2019, 10:24 AM   #11
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,364
Thanks: 13
Thanked 4,120 Times in 4,052 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Need "Select All Option" in Combobox for Query Parameters

How about:

http://www.theaccessweb.com/queries/qry0001.htm
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
jimbo92 (10-26-2019)
Old 10-26-2019, 11:09 AM   #12
jimbo92
Newly Registered User
 
Join Date: Oct 2019
Posts: 7
Thanks: 4
Thanked 0 Times in 0 Posts
jimbo92 is on a distinguished road
Re: Need "Select All Option" in Combobox for Query Parameters

Quote:
Originally Posted by arnelgp View Post
what is the Combo name and the Bound Column number?
ComboBox name is: EmployeeEntered

The Bound Column is: 2
jimbo92 is offline   Reply With Quote
Old 10-26-2019, 11:23 AM   #13
jimbo92
Newly Registered User
 
Join Date: Oct 2019
Posts: 7
Thanks: 4
Thanked 0 Times in 0 Posts
jimbo92 is on a distinguished road
Re: Need "Select All Option" in Combobox for Query Parameters

Quote:
Originally Posted by isladogs View Post
Last post was moderated. Posting this to trigger email notifications.

In the combo after update event, I suggest you use something like this

Code:
If Me.comboname.Column(1) = "All" Then
  'use query with no WHERE filter so all records are selected
Else
  'use query with WHERE filter for value in combo
End If
I've gotten a slight variation of this to work! Thank you so much! I was trying to simplify it, but what I have on the form is a combobox that selects employee, then two date picker fields for start and end date. Then I press a button that generates a report. So I created two queries like you recommended I think--one for if "All" is selected and one for if a single employee is selected. Then I added an IF macro On Click for the button that opens the query/report.

It all seems to be working for now. Thanks everyone for being so helpful!
jimbo92 is offline   Reply With Quote
Old 10-26-2019, 11:24 AM   #14
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,233
Thanks: 115
Thanked 3,072 Times in 2,790 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Need "Select All Option" in Combobox for Query Parameters

Glad to hear you've got it working. We were all pleased to assist.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Old 10-26-2019, 11:42 AM   #15
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Need "Select All Option" in Combobox for Query Parameters

can still be simplified:

SELECT [Employee Expenses].[Entry Number], [Employee Expenses].[Account Name], [Employee Expenses].[Employee Name], [Employee Expenses].[Work Date], [Employee Expenses].[Hours Worked], [Employee Expenses].[Pay Per Hour], [Employee Expenses].[Amount Due], [Employee Expenses].Notes
FROM [Employee Expenses]
WHERE ((([Employee Expenses].[Employee Name])=IIF([Forms]![Main Menu]![EmployeeEntered]="(All)", [Employee Expenses].[Employee Name], [Forms]![Main Menu]![EmployeeEntered]) AND (([Employee Expenses].[Work Date]) Between [Forms]![Main Menu]![EmpStartDate] And [Forms]![Main Menu]![EmpEndDate]))
ORDER BY [Employee Expenses].[Employee Name], [Employee Expenses].[Work Date];

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
select query return "text" instead of "boolean value" alain.roger Queries 1 03-26-2015 12:21 PM
Changing How Combobox Functions Based on Form's Editing Mode? ("Add" vs. "Edit") pitt_ph Forms 6 09-25-2012 08:07 PM
Adding "All" to a SELECT DISTINCT combobox hockey8837 Forms 16 01-13-2011 12:50 PM
possible to add a "select none" option to a combobox drop menu?? killyridols Forms 1 08-05-2009 10:39 AM
How to Select "Null" in combobox filter? CrystalSurfer Forms 2 11-16-2006 09:49 AM




All times are GMT -8. The time now is 10:25 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World