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!
