Adding * to a combo box

  • Thread starter Thread starter ReportSmith
  • Start date Start date
R

ReportSmith

I know I've seen a post for this issue, but can't find it.

I have a combo box that works just fine, but I would like to add a "*" at
the top (or wherever) to select all values in the combo box (this is a
workaround solution for a listbox problem...but it works great). This will
get passed to a parameter query via VBA.

Thanks in advance for the assistance.
 
I know I've seen a post for this issue, but can't find it.

I have a combo box that works just fine, but I would like to add a "*" at
the top (or wherever) to select all values in the combo box (this is a
workaround solution for a listbox problem...but it works great). This will
get passed to a parameter query via VBA.

Thanks in advance for the assistance.
Click to expand...

How about "<All>" at the top of the list?

Note that the symbols <> surround the word "All". That is to have the
list sort with the word <All> at the top of the list before any other
"A" listing.

First add "All" to the drop down list.

The below assumes your combo is just one column.

Select YourTable.[FieldName] From YourTable Union Select
"<ALL>" from YourTable Order by [FieldName];

The abode will display ....

<All>
OptionA
OptionB
etc...
Then if you are using the [FieldName] to filter data on,
change the query criteria to:

Like
IIf([forms]![FormName]![ComboName]="<ALL>","*",[forms]![FormName]![ComboName)

Change the table and field names to your actual table and field names.
The form must be open when the query is run.
 
I know I've seen a post for this issue, but can't find it.

I have a combo box that works just fine, but I would like to add a "*" at
the top (or wherever) to select all values in the combo box (this is a
workaround solution for a listbox problem...but it works great). This will
get passed to a parameter query via VBA.

Thanks in advance for the assistance.
Click to expand...

How about "<All>" at the top of the list?

Note that the symbols <> surround the word "All". That is to have the
list sort with the word <All> at the top of the list before any other
"A" listing.

First add "All" to the drop down list.

The below assumes your combo is just one column.

Select YourTable.[FieldName] From YourTable Union Select
"<ALL>" from YourTable Order by [FieldName];

The abode will display ....

<All>
OptionA
OptionB
etc...
Then if you are using the [FieldName] to filter data on,
change the query criteria to:

Like
IIf([forms]![FormName]![ComboName]="<ALL>","*",[forms]![FormName]![ComboName)

Change the table and field names to your actual table and field names.
The form must be open when the query is run.
 
Thanks to both replies. It was exactly what I was looking for. Other than
taking a while to run the SQL Union statement, it works like I needed it. I
originally had a list box with lots of code behind it, but couldn't get it to
work properly. I finally scrapped that idea (it was taking too long to
debug) and this one was what I came up with.

Thanks again.

fredg said:
I know I've seen a post for this issue, but can't find it.

I have a combo box that works just fine, but I would like to add a "*" at
the top (or wherever) to select all values in the combo box (this is a
workaround solution for a listbox problem...but it works great). This will
get passed to a parameter query via VBA.

Thanks in advance for the assistance.
Click to expand...

How about "<All>" at the top of the list?

Note that the symbols <> surround the word "All". That is to have the
list sort with the word <All> at the top of the list before any other
"A" listing.

First add "All" to the drop down list.

The below assumes your combo is just one column.

Select YourTable.[FieldName] From YourTable Union Select
"<ALL>" from YourTable Order by [FieldName];

The abode will display ....

<All>
OptionA
OptionB
etc...
Then if you are using the [FieldName] to filter data on,
change the query criteria to:

Like
IIf([forms]![FormName]![ComboName]="<ALL>","*",[forms]![FormName]![ComboName)

Change the table and field names to your actual table and field names.
The form must be open when the query is run.
Click to expand...
 
Thanks to both replies. It was exactly what I was looking for. Other than
taking a while to run the SQL Union statement, it works like I needed it. I
originally had a list box with lots of code behind it, but couldn't get it to
work properly. I finally scrapped that idea (it was taking too long to
debug) and this one was what I came up with.

Thanks again.

fredg said:
I know I've seen a post for this issue, but can't find it.

I have a combo box that works just fine, but I would like to add a "*" at
the top (or wherever) to select all values in the combo box (this is a
workaround solution for a listbox problem...but it works great). This will
get passed to a parameter query via VBA.

Thanks in advance for the assistance.
Click to expand...

How about "<All>" at the top of the list?

Note that the symbols <> surround the word "All". That is to have the
list sort with the word <All> at the top of the list before any other
"A" listing.

First add "All" to the drop down list.

The below assumes your combo is just one column.

Select YourTable.[FieldName] From YourTable Union Select
"<ALL>" from YourTable Order by [FieldName];

The abode will display ....

<All>
OptionA
OptionB
etc...
Then if you are using the [FieldName] to filter data on,
change the query criteria to:

Like
IIf([forms]![FormName]![ComboName]="<ALL>","*",[forms]![FormName]![ComboName)

Change the table and field names to your actual table and field names.
The form must be open when the query is run.
Click to expand...
 
One trick to use in the union statement is to use a table with only a few
records for the ALL part. I often have a one record table that stores
settings. If I have that I use it.

Using DISTINCT in the "main" query and UNION ALL might be faster then just
using UNION to return unique values.

Select DISTINCT YourTable.[FieldName] From YourTable
UNION ALL
Select "<ALL>" from VerySmallTable
Order by [FieldName];

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Thanks to both replies. It was exactly what I was looking for. Other than
taking a while to run the SQL Union statement, it works like I needed it. I
originally had a list box with lots of code behind it, but couldn't get it to
work properly. I finally scrapped that idea (it was taking too long to
debug) and this one was what I came up with.

Thanks again.

fredg said:
I know I've seen a post for this issue, but can't find it.

I have a combo box that works just fine, but I would like to add a "*" at
the top (or wherever) to select all values in the combo box (this is a
workaround solution for a listbox problem...but it works great). This will
get passed to a parameter query via VBA.

Thanks in advance for the assistance.
Click to expand...
How about "<All>" at the top of the list?

Note that the symbols <> surround the word "All". That is to have the
list sort with the word <All> at the top of the list before any other
"A" listing.

First add "All" to the drop down list.

The below assumes your combo is just one column.

Select YourTable.[FieldName] From YourTable Union Select
"<ALL>" from YourTable Order by [FieldName];

The abode will display ....

<All>
OptionA
OptionB
etc...
Then if you are using the [FieldName] to filter data on,
change the query criteria to:

Like
IIf([forms]![FormName]![ComboName]="<ALL>","*",[forms]![FormName]![ComboName)

Change the table and field names to your actual table and field names.
The form must be open when the query is run.
Click to expand...
Click to expand...
 
One trick to use in the union statement is to use a table with only a few
records for the ALL part. I often have a one record table that stores
settings. If I have that I use it.

Using DISTINCT in the "main" query and UNION ALL might be faster then just
using UNION to return unique values.

Select DISTINCT YourTable.[FieldName] From YourTable
UNION ALL
Select "<ALL>" from VerySmallTable
Order by [FieldName];

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Thanks to both replies. It was exactly what I was looking for. Other than
taking a while to run the SQL Union statement, it works like I needed it. I
originally had a list box with lots of code behind it, but couldn't get it to
work properly. I finally scrapped that idea (it was taking too long to
debug) and this one was what I came up with.

Thanks again.

fredg said:
I know I've seen a post for this issue, but can't find it.

I have a combo box that works just fine, but I would like to add a "*" at
the top (or wherever) to select all values in the combo box (this is a
workaround solution for a listbox problem...but it works great). This will
get passed to a parameter query via VBA.

Thanks in advance for the assistance.
Click to expand...
How about "<All>" at the top of the list?

Note that the symbols <> surround the word "All". That is to have the
list sort with the word <All> at the top of the list before any other
"A" listing.

First add "All" to the drop down list.

The below assumes your combo is just one column.

Select YourTable.[FieldName] From YourTable Union Select
"<ALL>" from YourTable Order by [FieldName];

The abode will display ....

<All>
OptionA
OptionB
etc...
Then if you are using the [FieldName] to filter data on,
change the query criteria to:

Like
IIf([forms]![FormName]![ComboName]="<ALL>","*",[forms]![FormName]![ComboName)

Change the table and field names to your actual table and field names.
The form must be open when the query is run.
Click to expand...
Click to expand...
 
Thanks. (I already had the DISTINCT statement).

John Spencer MVP said:
One trick to use in the union statement is to use a table with only a few
records for the ALL part. I often have a one record table that stores
settings. If I have that I use it.

Using DISTINCT in the "main" query and UNION ALL might be faster then just
using UNION to return unique values.

Select DISTINCT YourTable.[FieldName] From YourTable
UNION ALL
Select "<ALL>" from VerySmallTable
Order by [FieldName];

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Thanks to both replies. It was exactly what I was looking for. Other than
taking a while to run the SQL Union statement, it works like I needed it. I
originally had a list box with lots of code behind it, but couldn't get it to
work properly. I finally scrapped that idea (it was taking too long to
debug) and this one was what I came up with.

Thanks again.

fredg said:
On Mon, 18 May 2009 07:04:02 -0700, ReportSmith wrote:

I know I've seen a post for this issue, but can't find it.

I have a combo box that works just fine, but I would like to add a "*" at
the top (or wherever) to select all values in the combo box (this is a
workaround solution for a listbox problem...but it works great). This will
get passed to a parameter query via VBA.

Thanks in advance for the assistance.
How about "<All>" at the top of the list?

Note that the symbols <> surround the word "All". That is to have the
list sort with the word <All> at the top of the list before any other
"A" listing.

First add "All" to the drop down list.

The below assumes your combo is just one column.

Select YourTable.[FieldName] From YourTable Union Select
"<ALL>" from YourTable Order by [FieldName];

The abode will display ....

<All>
OptionA
OptionB
etc...
Then if you are using the [FieldName] to filter data on,
change the query criteria to:

Like
IIf([forms]![FormName]![ComboName]="<ALL>","*",[forms]![FormName]![ComboName)

Change the table and field names to your actual table and field names.
The form must be open when the query is run.
Click to expand...
Click to expand...
Click to expand...
 
Thanks. (I already had the DISTINCT statement).

John Spencer MVP said:
One trick to use in the union statement is to use a table with only a few
records for the ALL part. I often have a one record table that stores
settings. If I have that I use it.

Using DISTINCT in the "main" query and UNION ALL might be faster then just
using UNION to return unique values.

Select DISTINCT YourTable.[FieldName] From YourTable
UNION ALL
Select "<ALL>" from VerySmallTable
Order by [FieldName];

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Thanks to both replies. It was exactly what I was looking for. Other than
taking a while to run the SQL Union statement, it works like I needed it. I
originally had a list box with lots of code behind it, but couldn't get it to
work properly. I finally scrapped that idea (it was taking too long to
debug) and this one was what I came up with.

Thanks again.

fredg said:
On Mon, 18 May 2009 07:04:02 -0700, ReportSmith wrote:

I know I've seen a post for this issue, but can't find it.

I have a combo box that works just fine, but I would like to add a "*" at
the top (or wherever) to select all values in the combo box (this is a
workaround solution for a listbox problem...but it works great). This will
get passed to a parameter query via VBA.

Thanks in advance for the assistance.
How about "<All>" at the top of the list?

Note that the symbols <> surround the word "All". That is to have the
list sort with the word <All> at the top of the list before any other
"A" listing.

First add "All" to the drop down list.

The below assumes your combo is just one column.

Select YourTable.[FieldName] From YourTable Union Select
"<ALL>" from YourTable Order by [FieldName];

The abode will display ....

<All>
OptionA
OptionB
etc...
Then if you are using the [FieldName] to filter data on,
change the query criteria to:

Like
IIf([forms]![FormName]![ComboName]="<ALL>","*",[forms]![FormName]![ComboName)

Change the table and field names to your actual table and field names.
The form must be open when the query is run.
Click to expand...
Click to expand...
Click to expand...
 
If you use the UNION conjunction then there is no reason to use DISTINCT in
the query. UNION removes duplicates from the entire set of records - just as
distinct does.

I also goofed since if I use UNION ALL, I do need to either use Distinct on
both of the queries or use UNION and let it do the work.


Select DISTINCT YourTable.[FieldName] From YourTable
UNION ALL
Select DISTINCT "<ALL>" from VerySmallTable
Order by [FieldName];

Or


Select YourTable.[FieldName] From YourTable
UNION
Select "<ALL>" from VerySmallTable
Order by [FieldName];

You could use the first version I posted if VerySmallTable had just one record
in it. Or if you limited it to one record by using a where clause.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Thanks. (I already had the DISTINCT statement).

John Spencer MVP said:
One trick to use in the union statement is to use a table with only a few
records for the ALL part. I often have a one record table that stores
settings. If I have that I use it.

Using DISTINCT in the "main" query and UNION ALL might be faster then just
using UNION to return unique values.

Select DISTINCT YourTable.[FieldName] From YourTable
UNION ALL
Select "<ALL>" from VerySmallTable
Order by [FieldName];

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Thanks to both replies. It was exactly what I was looking for. Other than
taking a while to run the SQL Union statement, it works like I needed it. I
originally had a list box with lots of code behind it, but couldn't get it to
work properly. I finally scrapped that idea (it was taking too long to
debug) and this one was what I came up with.

Thanks again.

:

On Mon, 18 May 2009 07:04:02 -0700, ReportSmith wrote:

I know I've seen a post for this issue, but can't find it.

I have a combo box that works just fine, but I would like to add a "*" at
the top (or wherever) to select all values in the combo box (this is a
workaround solution for a listbox problem...but it works great). This will
get passed to a parameter query via VBA.

Thanks in advance for the assistance.
How about "<All>" at the top of the list?

Note that the symbols <> surround the word "All". That is to have the
list sort with the word <All> at the top of the list before any other
"A" listing.

First add "All" to the drop down list.

The below assumes your combo is just one column.

Select YourTable.[FieldName] From YourTable Union Select
"<ALL>" from YourTable Order by [FieldName];

The abode will display ....

<All>
OptionA
OptionB
etc...
Then if you are using the [FieldName] to filter data on,
change the query criteria to:

Like
IIf([forms]![FormName]![ComboName]="<ALL>","*",[forms]![FormName]![ComboName)

Change the table and field names to your actual table and field names.
The form must be open when the query is run.
Click to expand...
Click to expand...
Click to expand...
 
If you use the UNION conjunction then there is no reason to use DISTINCT in
the query. UNION removes duplicates from the entire set of records - just as
distinct does.

I also goofed since if I use UNION ALL, I do need to either use Distinct on
both of the queries or use UNION and let it do the work.


Select DISTINCT YourTable.[FieldName] From YourTable
UNION ALL
Select DISTINCT "<ALL>" from VerySmallTable
Order by [FieldName];

Or


Select YourTable.[FieldName] From YourTable
UNION
Select "<ALL>" from VerySmallTable
Order by [FieldName];

You could use the first version I posted if VerySmallTable had just one record
in it. Or if you limited it to one record by using a where clause.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Thanks. (I already had the DISTINCT statement).

John Spencer MVP said:
One trick to use in the union statement is to use a table with only a few
records for the ALL part. I often have a one record table that stores
settings. If I have that I use it.

Using DISTINCT in the "main" query and UNION ALL might be faster then just
using UNION to return unique values.

Select DISTINCT YourTable.[FieldName] From YourTable
UNION ALL
Select "<ALL>" from VerySmallTable
Order by [FieldName];

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Thanks to both replies. It was exactly what I was looking for. Other than
taking a while to run the SQL Union statement, it works like I needed it. I
originally had a list box with lots of code behind it, but couldn't get it to
work properly. I finally scrapped that idea (it was taking too long to
debug) and this one was what I came up with.

Thanks again.

:

On Mon, 18 May 2009 07:04:02 -0700, ReportSmith wrote:

I know I've seen a post for this issue, but can't find it.

I have a combo box that works just fine, but I would like to add a "*" at
the top (or wherever) to select all values in the combo box (this is a
workaround solution for a listbox problem...but it works great). This will
get passed to a parameter query via VBA.

Thanks in advance for the assistance.
How about "<All>" at the top of the list?

Note that the symbols <> surround the word "All". That is to have the
list sort with the word <All> at the top of the list before any other
"A" listing.

First add "All" to the drop down list.

The below assumes your combo is just one column.

Select YourTable.[FieldName] From YourTable Union Select
"<ALL>" from YourTable Order by [FieldName];

The abode will display ....

<All>
OptionA
OptionB
etc...
Then if you are using the [FieldName] to filter data on,
change the query criteria to:

Like
IIf([forms]![FormName]![ComboName]="<ALL>","*",[forms]![FormName]![ComboName)

Change the table and field names to your actual table and field names.
The form must be open when the query is run.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top