Adding 'All' To combo Box

[Pat Coleman]

I have a combo box set up with a query as follows

SELECT VendorNames.NAME FROM VendorNames;

in the Row Source under the Data Tab

When the user selects a name, a report is displayed for that vendor.

However I would like to have to option to have an "all" selection so a
report for all vendors will open up.


I have followed instructions on Article ID : 210290


with no success.

The following do not work

SELECT VendorNames.NAME FROM VendorNames UNION Select Null as AllChoice ,
"(All)" as Bogus From VendorNames;

or

SELECT VendorNames.NAME FROM VendorNames UNION Select "(All)" as Bogus From
VendorNames;

Whats missing ?????

Thanks

 

[Douglas J. Steele]

SELECT VendorNames.NAME FROM VendorNames UNION Select "(All)" as Bogus From
VendorNames; should work (in fact, the As Bogus isn't even required.)

Do you get an error message? If so, what's the error? If you don't get an
error message, what happens, and what do you expect to happen instead?

One comment: Name isn't a good choice for a field name, as it's a reserved
word. You might consider renaming that column. If you cannot (or will not)
rename it, at least put square brackets around it. For a good discussion
about names to avoid in Access, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

 

[davecass]

SELECT VendorNames.NAME FROM VendorNames UNION Select "(All)" as Bogus From
VendorNames; should work (in fact, the As Bogus isn't even required.)

Do you get an error message? If so, what's the error? If you don't get an
error message, what happens, and what do you expect to happen instead?

One comment: Name isn't a good choice for a field name, as it's a reserved
word. You might consider renaming that column. If you cannot (or will not)
rename it, at least put square brackets around it. For a good discussion
about names to avoid in Access, see what Allen Browne has athttp://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)
















- Show quoted text -

I've only been using Access for about a year, so I don't know all the
tricks and unique attributes yet. In my experience with SQL in other
software, though, the two sides of a UNION statement need to have the
exactly same fields. Here's an example that works fine for me.

SELECT NAME FROM VendorNames UNION Select "(All)" as NAME FROM
VendorNames

I always want to insure that the "(All) statement is at the top, so
I'll include a second field to perform a sort...

SELECT Name, 1 AS Section FROM VendorNames UNION Select "(All)" as
Name, 0 AS Section FROM VendorNames ORDER BY Section, Name

 

[Douglas J. Steele]

It's not necessary that the names be the same, only that the data types be
the same.

SELECT [Name], 1 AS Section FROM VendorNames UNION Select "(All)", 0 FROM
VendorNames ORDER BY Section, Name

will work just as well. The resultant recordset picks up names from the
first subselect.

Your suggestion for sorting is a valid one. However, in this case, ( will
sort ahead of either a or A, so it's probably not necessary. Still, nothing
wrong with being cautious.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I've only been using Access for about a year, so I don't know all the
tricks and unique attributes yet. In my experience with SQL in other
software, though, the two sides of a UNION statement need to have the
exactly same fields. Here's an example that works fine for me.

SELECT NAME FROM VendorNames UNION Select "(All)" as NAME FROM
VendorNames

I always want to insure that the "(All) statement is at the top, so
I'll include a second field to perform a sort...

SELECT Name, 1 AS Section FROM VendorNames UNION Select "(All)" as
Name, 0 AS Section FROM VendorNames ORDER BY Section, Name

 

[Jeff Boyce]

Pat

As Douglas points out, we don't know what "with no success" means ... we
aren't there.

Are you saying that the SQL does not display "All" in your combobox?

Are you saying that you can't get a report that includes "All"?


--
More info, please ...

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Pat Coleman]

Many thanks for all the responses.

I was getting a 'ODBC - Call Failed'

SQL error code -104

as soon as I would click the drop down box on the Combo box in Form View

VendorNames is a query in Access that was quering a ODBC ( firebird )
databases connected to the MS Access database.

I have not had any problems with any other query and I have some tricky ones
working fine.

I set up an empty Table called VendorNameTable and appended the data from
VendorName in to it. It has one field called VendorName.


I changed the query in the combo box to :

SELECT VendorName FROM VendorNameTable UNION Select "(All)" as NAME FROM
VendorNameTable;

Now the combo box shows the (ALL) as the first item in the drop down list
when I click it in Form View.


However, my Vendor Query does not show any results when I Select (ALL) from
the combo Box.

If I select an individual Vendors Name, my Vendor Query works for that
Vendor.

I was Looking to Run my Vendor Query for all vendors in VendorNameTable
when the (ALL) was selected.

 

[John W. Vinson]

If I select an individual Vendors Name, my Vendor Query works for that
Vendor.

I was Looking to Run my Vendor Query for all vendors in VendorNameTable
when the (ALL) was selected.

Does your Vendor query check for "(ALL)"? Just including that in the rowsource
will not work - you need a criterion like

VendorName = Forms!yourform!yourcombo OR Forms!yourform!yourcombo = "(ALL)"

Perhaps you could post the SQL view of the vendor query if this doesn't make
sense or doesn't help.

John W. Vinson [MVP]

 

[Pat Coleman]

John

I added

OR Forms!yourform!yourcombo = "(ALL)"

to the query and we are working away now.

Thanks

 

[Klatuu]

Try this explanation:
http://www.mvps.org/access/forms/frm0043.htm

 

[Klatuu]

Here is a link that has a good explanation of adding All to a comb box:

http://www.mvps.org/access/forms/frm0043.htm

What they don't say in the instructions is how to construct the combo's row
source query to get the correct values. It would be something like:

SELECT Afield FROM SomeTable WHERE Afield LIKE IIf(forms!MyForm!MyCombo =
"(All)", "*", forms!MyForm!MyCombo);

 

[Klatuu]

You are mostly correct. Each union must have the same number and types of
fields, but the field names do not have to be the same.