I added the "AND" and did all the other steps you suggested, and it
still
isn't working when I push the command44 button (the Go button)
This is what comes up in the "immediate" box:
([SNM TYPE] = "LPRM") AND([ICA] = "Reactor Core"
It seems to be correct, SNM TYPE and ICA are my fields that the combo
boxes
select from, and "LPRM" and "Reactor Core" are the choices I selected
from
the drop down box. The Form still works and pulls up filtered results
if
I
select what I want, save it, go into Design view, and then exit Design
view
and go back to the normal view, but the Command44 button just doesn't
seem
to
work.
My code for the Go button is below, Is what was in the immediate window
what
you wanted for the debug.print? I'm not sure what you mean regarding
what
type of fields I have, maybe just that the data types are both "text"?
I
don't know if this will help, but both my combo boxes are unbound, have
"table/query" for the rowsourcetype, and this for the rowsource: SELECT
DISTINCT [SNM Data].[SNM Type] FROM [SNM Data] ORDER BY [SNM Type];
where
SNM
Data is my table and SNM Type is the field. The other Combo box has
the
other field name of course.
Option Compare Database
Option Explicit
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
Stop
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
Thank you so much for all your help! It is very much appreciated.
:
You are missing the AND at the end of each line that sets strWhere,
e.g.:
strWhere = strWhere & "([SNM TYPE] = """ & Me.Combo22 & """) AND "
Just above the "Me.Filter = strWhere" line, add:
Debug.Print strWhere
Stop
When it runs, open the Immediate Window (Ctrl+G.)
Look at what comes out, and see if it is right.
Press F8 To single-step through the rest of the procedure until it
ends.
Also, to ensure the code is correct, take these steps in the code
window:
1. Choose Compile on the Debug menu.
2. Make sure you have this line at the very top of the code module:
Option Explicit
3. Under Tools | Options (in the code window), on the General tab, set
Error
Trapping to 'Break on nhandled Errors', and uncheck the box 'Compile
on
Demand.'
If you are still stuck, post what comes out of the Debug.Print, and
tell
us
what type of field you have in your table (in design view) for
SNM_TYPE
and
ICA.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
This is the code I have, the Command44 button is the "go" button
which
should
run the filter, when I come out of design view, select from my cbo
and
push
it nothing happens
The reset button works though!
Any thoughts?
Option Compare Database
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 & """)
"
End If
If Not IsNull(Me.Combo24) Then
strWhere = strWhere & "([ICA] = """ & Me.Combo24 & """)
"
End If
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
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
:
Open the form in design view.
Right-click on the command button, and choose Properties.
On the Event tab of the Properties box, the On Click property is
set
to:
[Event Procedure]
Click the Build button (...) beside this.
Access opens the code window.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Ok, so I have figured out how to change your code to work for my
form,
but
I
don't know where to put the code. I've been trying to code
things
in
the
Macros or Modules tabs, but your "search2000" example didn't have
the
code
stored there. Where do you enter code in Access?
:
To remove the duplicates from your combo, add the word DISTINCT
to
the
RowSource.
The combo's RowSource property will probably be a query such as:
SELECT [MyField] FROM [MyTable] ORDER BY [MyField];
Change it like this
SELECT DISTINCT [MyField] FROM [MyTable] ORDER BY [MyField];
If you are doing this in query design view, you can set the
query's
Unique
Value property to Yes (in the Properties box.)
For an example of how to build a filter form, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
There's a downloadable example to pull apart and see how it
works.
It
does
involve using VBA code to build a string. It will be similar to
what
you
have done in Excel, though the idea of Null might be new for
you.
(A
field
is null if there's no value stored there.)
message
I have a table with about 8 fields displaying information
pertaining
to
5
different types of equipment (ex: "equipment type"; "serial
number";
"location"; "date adjusted"; "time adjusted")
I want to set up a form in which people from my company can
search
for
a
specific piece of equipment based on equipment type, location,
and
date
using
3 different combo boxes, and having the results displayed in a
table
in
a
subform.
one initial problem I had was when I tried linking the combo
box
to
the
"equipment type" field, but because there are reapeating
entries
(there
are
only 5 different types, and they all repeat ex: LPRM, APRM,
SRM,
IRM,
there
are about 20 LPRM, 10 APRM, etc) when I went to view my form
and
try
the
combo box, the available options were LPRM repeated 20 times,
etc.
I am very new to access and don't know how to link the combo
box
selection
to filter the subform data, especially if I'm typing in the
values
for
the
combo box to choose from instead of linking them to a field on
my
table.
I JUST learned what a query is, if that gives you an idea of
my
Access
level. I am very competent in Excel, and have done some
Macros
and
Visual
Basic before in Excel
I have Access 2003
