"All" as a combo box option

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.

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.

this is definitely pointing me in the right direction, thanks!
--
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.

What I have done in some situations when the data is relatively
limited, is the following

In the qurey that is actually retrieving the records, change the
criteria to be
like "*" & form![Formname]![comboxname] & "*"

instead of the normal form![Formname]![comboxname]

That way when there is NO value in the combobox it get all records.

Whereas using "ALL" requires logic to change the query when the ALL
condition occurs.

I don't believe that this would work if the bound informatioin is the
ID instead of the data in a combobox situation.

Where I use this mostly is when I have no positive control over the
data that is stored in the field that I am allowing the search on.

Ron

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.

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.

If the rowsource is a valuelist why are you not simply putting "(ALL)"
in there to start with?

I have "(All)" as a value in my field list but when I select it it doesn't
display any records at all. I know there is something I'm missing but I just
don't know what it is.
--
Jon M.


"Ron2006" wrote:

If the rowsource is a valuelist why are you not simply putting "(ALL)"
in there to start with?

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.

Presumably your SQL currently has something like:

WHERE Field = Forms!FormName!NameOfCombobox

as a criteria. You need to change that to

WHERE (Field = Forms!FormName!NameOfCombobox OR
Forms!FormName!NameOfCombobox = "(All)")

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


"Jon M." wrote in message
...
I have "(All)" as a value in my field list but when I select it it doesn't
display any records at all. I know there is something I'm missing but I
just
don't know what it is.
--
Jon M.


"Ron2006" wrote:

If the rowsource is a valuelist why are you not simply putting "(ALL)"
in there to start with?