"All" as a combo box option

J

Jon M.

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!
 
R

Ron2006

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
 
J

Jon M.

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?
 
R

Ron2006

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

Jon M.

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.
 
J

Jon M.

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.
 
D

Douglas J. Steele

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)")
 
N

NukeEng85

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?
 
B

BruceM

You already have this link
http://www.mvps.org/access/forms/frm0043.htm

In your case there is just one column in the Row Source, so the Select Null
as AllChoice line from the line does not need to be in your SQL, if I
understand the situation correctly.

The problem here seems to be that you are selecting something as Null. It
may go something like this in your case:
SELECT [ICA] FROM [SNM Data]
UNION SELECT "(ALL)" as SelectAll From [SNM Data]
ORDER BY [ICA];

The Parentheses around All should assure that it goes to the top of the list
(special characters are sorted in front of letters and numbers).

This should set the Row Source using a Union Query.

The example using the Open code assumes the existence of a Value List rather
than a query as the Row Source. You need a value list in the combo box
properties for that system to work. You could create a value list in code,
I suppose, but that isn't covered in the code example, and I don't see that
it would help.

The next consideration is what happens after the selection is made. What is
the After Update code for the combo box?

NukeEng85 said:
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. said:
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.
 
D

Douglas J. Steele

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

(no e-mails, please!)


NukeEng85 said:
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. said:
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.
 
N

NukeEng85

I did what you said and still no results, the after update code is as below:

Private Sub Combo24_AfterUpdate()

End Sub

Private Sub Command44_Click()

Dim strWhere As String 'The criteria string.
Dim lngLen As Long

If Not IsNull(Me.Combo22) Then
strWhere = strWhere & "([SNM TYPE] = """ & Me.Combo22 & """) AND "
End If


If Not IsNull(Me.Combo24) Then
strWhere = strWhere & "([ICA] = """ & Me.Combo24 & """) AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
Debug.Print strWhere


Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub





Private Sub Command43_Click()
'Purpose: Clear all the search boxes in the Form Header, and show all
records again.
Dim ctl As Control

'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acDetail).Controls
Select Case ctl.ControlType
Case acComboBox
ctl.Value = Null
End Select
Next

'Remove the form's filter.
Me.FilterOn = False
End Sub



BruceM said:
You already have this link
http://www.mvps.org/access/forms/frm0043.htm

In your case there is just one column in the Row Source, so the Select Null
as AllChoice line from the line does not need to be in your SQL, if I
understand the situation correctly.

The problem here seems to be that you are selecting something as Null. It
may go something like this in your case:
SELECT [ICA] FROM [SNM Data]
UNION SELECT "(ALL)" as SelectAll From [SNM Data]
ORDER BY [ICA];

The Parentheses around All should assure that it goes to the top of the list
(special characters are sorted in front of letters and numbers).

This should set the Row Source using a Union Query.

The example using the Open code assumes the existence of a Value List rather
than a query as the Row Source. You need a value list in the combo box
properties for that system to work. You could create a value list in code,
I suppose, but that isn't covered in the code example, and I don't see that
it would help.

The next consideration is what happens after the selection is made. What is
the After Update code for the combo box?

NukeEng85 said:
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. said:
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.


:

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


:

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.


:

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

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

hth
--
Maurice Ausum


:

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!
 
N

NukeEng85

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

Douglas J. Steele said:
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

(no e-mails, please!)


NukeEng85 said:
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. said:
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.


:

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


:

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.


:

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

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

hth
--
Maurice Ausum


:

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!
 
N

NukeEng85

I did it this way, and it works! I would still prefer the user to be able to
select "all" instead of just leaving it blank, but I'll take what I can get :)
 
B

BruceM

Define "No results." Do you don't get (All) to show up in the combo box?
Nothing happens when you select from the combo box?

You have posted no After Update code for Combo24. I don't know what there
is for Combo 22. However, if you select (All) from Combo 24, then run the
code, strWhere is looking for a record in which ICA is (All). The
Debug.Print line should show you the SQL string. You can view it after the
code has run by Pressing Ctrl + G and looking in the Immediate window.

If you use a two-column row source you can set the first column to Null with
an adaptation of the two-column code shown in the link. If you use a
one-column Row Source you will need to test for (All), maybe something like:

If Nz(Me.Combo24,"(All)") <> "(All)" Then
strWhere = strWhere & "[ICA] = """ & Me.Combo24 & """ AND "
End If

Again, check the string as you go to be sure it is what it should be.


NukeEng85 said:
I did what you said and still no results, the after update code is as
below:

Private Sub Combo24_AfterUpdate()

End Sub

Private Sub Command44_Click()

Dim strWhere As String 'The criteria string.
Dim lngLen As Long

If Not IsNull(Me.Combo22) Then
strWhere = strWhere & "([SNM TYPE] = """ & Me.Combo22 & """) AND "
End If


If Not IsNull(Me.Combo24) Then
strWhere = strWhere & "([ICA] = """ & Me.Combo24 & """) AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
Debug.Print strWhere


Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub





Private Sub Command43_Click()
'Purpose: Clear all the search boxes in the Form Header, and show all
records again.
Dim ctl As Control

'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acDetail).Controls
Select Case ctl.ControlType
Case acComboBox
ctl.Value = Null
End Select
Next

'Remove the form's filter.
Me.FilterOn = False
End Sub



BruceM said:
You already have this link
http://www.mvps.org/access/forms/frm0043.htm

In your case there is just one column in the Row Source, so the Select
Null
as AllChoice line from the line does not need to be in your SQL, if I
understand the situation correctly.

The problem here seems to be that you are selecting something as Null.
It
may go something like this in your case:
SELECT [ICA] FROM [SNM Data]
UNION SELECT "(ALL)" as SelectAll From [SNM Data]
ORDER BY [ICA];

The Parentheses around All should assure that it goes to the top of the
list
(special characters are sorted in front of letters and numbers).

This should set the Row Source using a Union Query.

The example using the Open code assumes the existence of a Value List
rather
than a query as the Row Source. You need a value list in the combo box
properties for that system to work. You could create a value list in
code,
I suppose, but that isn't covered in the code example, and I don't see
that
it would help.

The next consideration is what happens after the selection is made. What
is
the After Update code for the combo box?

NukeEng85 said:
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?

:

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.


:

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


:

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.


:

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

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

hth
--
Maurice Ausum


:

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!
 
N

NukeEng85

sorry, nothing happens when I select from the combo box, (ALL) does show up

BruceM said:
Define "No results." Do you don't get (All) to show up in the combo box?
Nothing happens when you select from the combo box?

You have posted no After Update code for Combo24. I don't know what there
is for Combo 22. However, if you select (All) from Combo 24, then run the
code, strWhere is looking for a record in which ICA is (All). The
Debug.Print line should show you the SQL string. You can view it after the
code has run by Pressing Ctrl + G and looking in the Immediate window.

If you use a two-column row source you can set the first column to Null with
an adaptation of the two-column code shown in the link. If you use a
one-column Row Source you will need to test for (All), maybe something like:

If Nz(Me.Combo24,"(All)") <> "(All)" Then
strWhere = strWhere & "[ICA] = """ & Me.Combo24 & """ AND "
End If

Again, check the string as you go to be sure it is what it should be.


NukeEng85 said:
I did what you said and still no results, the after update code is as
below:

Private Sub Combo24_AfterUpdate()

End Sub

Private Sub Command44_Click()

Dim strWhere As String 'The criteria string.
Dim lngLen As Long

If Not IsNull(Me.Combo22) Then
strWhere = strWhere & "([SNM TYPE] = """ & Me.Combo22 & """) AND "
End If


If Not IsNull(Me.Combo24) Then
strWhere = strWhere & "([ICA] = """ & Me.Combo24 & """) AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
Debug.Print strWhere


Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub





Private Sub Command43_Click()
'Purpose: Clear all the search boxes in the Form Header, and show all
records again.
Dim ctl As Control

'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acDetail).Controls
Select Case ctl.ControlType
Case acComboBox
ctl.Value = Null
End Select
Next

'Remove the form's filter.
Me.FilterOn = False
End Sub



BruceM said:
You already have this link
http://www.mvps.org/access/forms/frm0043.htm

In your case there is just one column in the Row Source, so the Select
Null
as AllChoice line from the line does not need to be in your SQL, if I
understand the situation correctly.

The problem here seems to be that you are selecting something as Null.
It
may go something like this in your case:
SELECT [ICA] FROM [SNM Data]
UNION SELECT "(ALL)" as SelectAll From [SNM Data]
ORDER BY [ICA];

The Parentheses around All should assure that it goes to the top of the
list
(special characters are sorted in front of letters and numbers).

This should set the Row Source using a Union Query.

The example using the Open code assumes the existence of a Value List
rather
than a query as the Row Source. You need a value list in the combo box
properties for that system to work. You could create a value list in
code,
I suppose, but that isn't covered in the code example, and I don't see
that
it would help.

The next consideration is what happens after the selection is made. What
is
the After Update code for the combo box?

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?

:

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.


:

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


:

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.


:

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

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

hth
--
Maurice Ausum


:

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!
 
B

BruceM

"Nothing shows up" because the code does not allow it to show up. The code
as written is using the value (All) in the filter string. Your brief answer
does not explain whether or not you tried anything different from what has
already proven not to work.

NukeEng85 said:
sorry, nothing happens when I select from the combo box, (ALL) does show
up

BruceM said:
Define "No results." Do you don't get (All) to show up in the combo box?
Nothing happens when you select from the combo box?

You have posted no After Update code for Combo24. I don't know what
there
is for Combo 22. However, if you select (All) from Combo 24, then run
the
code, strWhere is looking for a record in which ICA is (All). The
Debug.Print line should show you the SQL string. You can view it after
the
code has run by Pressing Ctrl + G and looking in the Immediate window.

If you use a two-column row source you can set the first column to Null
with
an adaptation of the two-column code shown in the link. If you use a
one-column Row Source you will need to test for (All), maybe something
like:

If Nz(Me.Combo24,"(All)") <> "(All)" Then
strWhere = strWhere & "[ICA] = """ & Me.Combo24 & """ AND "
End If

Again, check the string as you go to be sure it is what it should be.


NukeEng85 said:
I did what you said and still no results, the after update code is as
below:

Private Sub Combo24_AfterUpdate()

End Sub

Private Sub Command44_Click()

Dim strWhere As String 'The criteria string.
Dim lngLen As Long

If Not IsNull(Me.Combo22) Then
strWhere = strWhere & "([SNM TYPE] = """ & Me.Combo22 & """)
AND "
End If


If Not IsNull(Me.Combo24) Then
strWhere = strWhere & "([ICA] = """ & Me.Combo24 & """) AND
"
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
Debug.Print strWhere


Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub





Private Sub Command43_Click()
'Purpose: Clear all the search boxes in the Form Header, and show
all
records again.
Dim ctl As Control

'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acDetail).Controls
Select Case ctl.ControlType
Case acComboBox
ctl.Value = Null
End Select
Next

'Remove the form's filter.
Me.FilterOn = False
End Sub



:

You already have this link
http://www.mvps.org/access/forms/frm0043.htm

In your case there is just one column in the Row Source, so the Select
Null
as AllChoice line from the line does not need to be in your SQL, if I
understand the situation correctly.

The problem here seems to be that you are selecting something as Null.
It
may go something like this in your case:
SELECT [ICA] FROM [SNM Data]
UNION SELECT "(ALL)" as SelectAll From [SNM Data]
ORDER BY [ICA];

The Parentheses around All should assure that it goes to the top of
the
list
(special characters are sorted in front of letters and numbers).

This should set the Row Source using a Union Query.

The example using the Open code assumes the existence of a Value List
rather
than a query as the Row Source. You need a value list in the combo
box
properties for that system to work. You could create a value list in
code,
I suppose, but that isn't covered in the code example, and I don't see
that
it would help.

The next consideration is what happens after the selection is made.
What
is
the After Update code for the combo box?

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?

:

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.


:

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


:

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.


:

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

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

hth
--
Maurice Ausum


:

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!
 
D

Douglas J. Steele

NukeEng85 said:
I did it this way, and it works! I would still prefer the user to be able
to
select "all" instead of just leaving it blank, but I'll take what I can
get :)

Ron2006 said:
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.

Except those that are Null.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top