"All" as a combo box option

What's the actual SQL of the query where you're trying to refer to the combo
box?

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


"NukeEng85" wrote in message
...
Douglas, I did what you said, and still no results, thank you for trying!

"Douglas J. Steele" wrote:

You can't just add a row ALL to your combo box and expect queries based
on
that combo box to know what ALL means.

Assuming you've currently got a criteria of

Forms![NameOfForm]!NameOfCombo]

in your query, change that to:

Forms![NameOfForm]!NameOfCombo] OR (Forms![NameOfForm]!NameOfCombo] =
"ALL")

That assumes, of course, that the bound column of the combo box returns
ALL.
If you have it returning something else, such a number or a Null, you'll
need something like:

Forms![NameOfForm]!NameOfCombo] OR (Forms![NameOfForm]!NameOfCombo] = 0

or

Forms![NameOfForm]!NameOfCombo] OR (Forms![NameOfForm]!NameOfCombo] IS
NULL)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"NukeEng85" wrote in message
...
I have the exact same problem as Jon has, I go to select "ALL" and
nothing
comes up. I'm not using a value list, my combo box pulls it's data
from a
field in a table. I have SELECT DISTINCT [SNM Data].[ICA] FROM [SNM
Data]
UNION SELECT "ALL" as Null From [SNM Data]
ORDER BY [SNM Data].[ICA]; in my row source. any thoughts?

"Jon M." wrote:

I got the error to go away there was a labeling issue I had to
address.
Now
my code is exactly as it should be, so it seems. However when I
select
(All)
it still does not display all the records for that area, in fact it
doesn't
show me any records. I know I'm missing something but I don't know
what
it
is. Any suggestions? Thanks in advance.
--
Jon M.


"Klatuu" wrote:

On which line is the error occuring?
--
Dave Hargis, Microsoft Access MVP


"Jon M." wrote:

I tried to use this code but I am getting an error message when I
open the
form. The message is Compile Error: Method or data member not
found.
My
code is correct I think it is:
Private Sub Form_Open(Cancel As Integer)
With Me.OfficeLoc
.RowSourceType = "Value List"
.RowSource = "(All);" & .RowSource
End With
End Sub

OfficeLoc is the name of my control box. Any suggestions what I'm
doing
wrong?
--
Jon M.


"Maurice" wrote:

Take a look here maybe this will help you on your way...

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

hth
--
Maurice Ausum


"Jon M." wrote:

I have a form with 3 combo boxes. One for Building, Dept.,
and
Floor#. They
retreive employee records and display them on a subform. I
would
like to
have an "All" option in each of the combo boxes that will
display
all the
records for that field.
For example I could then choose Building A, Banking Dept, and
"all" in the
last combo box and see all employees in Building A in the
banking
dept, on
every floor.

Or, "All", Banking, 3rd Floor and I would see every employee
in
the banking
dept on the 3rd floor in each of the buildings. Does that
make
sense?
As always any help is greatly appreciated!
--
Jon M.

Have you tried creating an AfterUpdate event on your combo box with an IF
statement that would test whether the value of the combo was "All" or
something else? Basically, it would look something like this:

IF Me.NameofCombo = "All" or whatever value corresponds to it THEN
Run SQL to display all records
ELSE
Run SQL to display selected records
END IF


--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda Grayson


"NukeEng85" wrote:

Douglas, I did what you said, and still no results, thank you for trying!

"Douglas J. Steele" wrote:

You can't just add a row ALL to your combo box and expect queries based on
that combo box to know what ALL means.

Assuming you've currently got a criteria of

Forms![NameOfForm]!NameOfCombo]

in your query, change that to:

Forms![NameOfForm]!NameOfCombo] OR (Forms![NameOfForm]!NameOfCombo] = "ALL")

That assumes, of course, that the bound column of the combo box returns ALL.
If you have it returning something else, such a number or a Null, you'll
need something like:

Forms![NameOfForm]!NameOfCombo] OR (Forms![NameOfForm]!NameOfCombo] = 0

or

Forms![NameOfForm]!NameOfCombo] OR (Forms![NameOfForm]!NameOfCombo] IS NULL)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"NukeEng85" wrote in message
...
I have the exact same problem as Jon has, I go to select "ALL" and nothing
comes up. I'm not using a value list, my combo box pulls it's data from a
field in a table. I have SELECT DISTINCT [SNM Data].[ICA] FROM [SNM Data]
UNION SELECT "ALL" as Null From [SNM Data]
ORDER BY [SNM Data].[ICA]; in my row source. any thoughts?

"Jon M." wrote:

I got the error to go away there was a labeling issue I had to address.
Now
my code is exactly as it should be, so it seems. However when I select
(All)
it still does not display all the records for that area, in fact it
doesn't
show me any records. I know I'm missing something but I don't know what
it
is. Any suggestions? Thanks in advance.
--
Jon M.


"Klatuu" wrote:

On which line is the error occuring?
--
Dave Hargis, Microsoft Access MVP


"Jon M." wrote:

I tried to use this code but I am getting an error message when I
open the
form. The message is Compile Error: Method or data member not found.
My
code is correct I think it is:
Private Sub Form_Open(Cancel As Integer)
With Me.OfficeLoc
.RowSourceType = "Value List"
.RowSource = "(All);" & .RowSource
End With
End Sub

OfficeLoc is the name of my control box. Any suggestions what I'm
doing
wrong?
--
Jon M.


"Maurice" wrote:

Take a look here maybe this will help you on your way...

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

hth
--
Maurice Ausum


"Jon M." wrote:

I have a form with 3 combo boxes. One for Building, Dept., and
Floor#. They
retreive employee records and display them on a subform. I would
like to
have an "All" option in each of the combo boxes that will display
all the
records for that field.
For example I could then choose Building A, Banking Dept, and
"all" in the
last combo box and see all employees in Building A in the banking
dept, on
every floor.

Or, "All", Banking, 3rd Floor and I would see every employee in
the banking
dept on the 3rd floor in each of the buildings. Does that make
sense?
As always any help is greatly appreciated!
--
Jon M.