Filtering with multiple combos problem

  • Thread starter Thread starter RipperT
  • Start date Start date
R

RipperT

I have searched and searched on this topic and can't find a resource that
addresses specifically what I need. I need to filter a form using 4 conbo
boxes. I need them to filter records on the fly, that is, as the values are
selected in them. I have found many posts that explain how to restrict the
combo box values based on other combo boxes, that I can swing; I have found
posts that explain how to filter using one combo box, but not multiple combo
boxes. Does anyone know of a resource that explains in detail what I need to
do?

Many thanks

Rip
 
Rip,

For the example below I'll assume you have three combos, cbo1 through
cbo3, imposing filters on fields Field1 through Field3 respectively in
the form's recordsource. My approach involves constructing a filter
sting and imposing it on the form every time a change is made in one of
the three combos; this is accomplished by means of a sub in the form's
own module, which is called from each combo's On Change event.

Private Sub Apply_Filter()
If Not IsNull(Me.cbo1) Then
fltr = "Field1 = '" & Me.cbo1 & "'"
End If
If Not IsNull(Me.cbo2) Then
If Len(fltr) > 1 Then fltr = fltr & " AND "
fltr = "Field2 Like '" & Me.cbo2 & "*'"
End If
If Not IsNull(Me.cbo3) Then
If Len(fltr) > 1 Then fltr = fltr & " AND "
fltr = "Field3 = " & Me.cbo3
End If
Me.FilterOn = True
End Sub

In the example code above I have assumed Field1 and Filed2 to be Text,
and Field3 to be numeric, so I display the syntax for both cases. Also,
I am searching for an exact match in Field1, and any value starting with
the selection in cbo2 if Field2 (use of * wildcard).

HTH,
Nikos
 
All you need is a single line of code like:

Apply_Filter()

i.e. the sub name. Remember, the sub must be in the form's own module,
not in a general one.

HTH,
Nikos
 
Thanx for the reply. I'm not sure what you mean by 'form's own module, not
in a general one'. I am opening the forms property sheet, clicking On Apply
Filter and using code builder to get this:

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
If Not IsNull(Me.cboInstitution) Then
fltr= "Institution = '" & Me.cboInstitution & "'"
End If
If Not IsNull(Me.cboHousingUnit) Then
fltr = "HousingUnit = '" & Me.cboHousingUnit & "'"
End If
If Not IsNull(Me.cboCellNo) Then
If Len(fltr) > 1 Then fltr = fltr & " AND "
fltr = "CellNo = " & Me.cboCellNo
End If
If Not IsNull(Me.cboBunk) Then
fltr = "Bunk = '" & Me.cboBunk & "'"
End If
Me.FilterOn = True
End Sub

If I use:

Apply_Filter

in the cbo After_update event, I get an error that says: "sub or function
not defined."

If I add the parentheses:

Apply_Filter()

I get a compile error that says "expected: ="

I'm just not good enough at coding to know how to resolve this. Thanx for
the help.

Rip
 
Rip,

Sorry, I wasn't detailed enough. The sub's name misled you into putting
the code in the wrong event, so pls remove it. Then select each of the
four combos in turn, and repeat the following:
* select the combo's On Change property
* select Code Builder
* In the VBA editor screen, put:
Apply_Filter()
in between the event sub's first and last line, that is inserted
automatically.

The module where you have put these calls is the form's own module. If
you go back to the VBA editor, you will notice on the left hand side
(project explorer( that the selected module is called Form_YourFormname,
and its icon is a small form icon, as opposed to general modules under
node "Modules" further down, if you have any, which have a different
icon. So, while in the form's module, scroll down to the end and paste
the sub there:

Private Sub Apply_Filter()
If Not IsNull(Me.cboInstitution) Then
fltr= "Institution = '" & Me.cboInstitution & "'"
End If
If Not IsNull(Me.cboHousingUnit) Then
If Len(fltr) > 1 Then fltr = fltr & " AND "
fltr = fltr & "HousingUnit = '" & Me.cboHousingUnit & "'"
End If
If Not IsNull(Me.cboCellNo) Then
If Len(fltr) > 1 Then fltr = fltr & " AND "
fltr = fltr & "CellNo = " & Me.cboCellNo
End If
If Not IsNull(Me.cboBunk) Then
If Len(fltr) > 1 Then fltr = fltr & " AND "
fltr = fltr & "Bunk = '" & Me.cboBunk & "'"
End If
Me.FilterOn = True
End Sub

(I have made a couple of corrections).
This is a sub called Apply_Filter (might just as well call it "xyz",
wouldn't make a difference) which has nothing to do with the form's On
Apply Filter event, which you were misled into. This event fires after
the execution of the last line of code above, but this is of no use to
you here.

HTH,
Nikos
 
Nikos,

I sure appreciate the help. I've done as you've instructed, but each time I
type Apply_Filter() and try to move to the next line or close out of the
editor, the compiler complains:

Compile error:
Expected: =

I can get out of the editor and use the form, but soon as I change the value
in one of the cbo's, it quits and reports a syntax error. No idea how to
resolve.

Again, thank you for taking time to help.

Rip
 
My mistake, you probably need to remove the () from the end of the name
of the sub at calling it.

Nikos
 
Sorry for the bother. I really appreciate your taking the time to help, but
there is still a problem. No more error messages, but the combos are simply
not filtering the records in the form. It opens to record one and there is
stays. Is fltr a reserved word in VB? I'm thinking maybe I should be using
Me.Filter or something? Shooting in the dark here, because I don't know
code.

Thanx again,

Rip
 
Rip,

No, fltr is not a reserved keyword. It is used as a local variable here,
whereas Me.Filter references a form property, so different animals.

I don't know what else to suggest. If you want me to have a look at your
database, you are welcome to mail it to me, making sure you convert it
to A2K format if it is in a later version. If you do, pls try to keep it
small, by (a) removing the bulk of the data, if masses of it already in,
(b) compacting, and (c) zipping, if possible.

Regards,
Nikos
 
Nikos,

I will email it to you; it's actually a skeleton version of a DB I am
developing at work, so very small.

Your original post said that we would create a filter string and impose it
on the form. Where exactly is that imposition happening in the code?

Thanx again.

Rip
 
Rip,

Your original post said that we would create a filter string and impose it
on the form. Where exactly is that imposition happening in the code?
Click to expand...
Absolutely right! That's exactly the part (line) I missed out in my
"corrected" version of the code. There should be a line to assign the
filter expression to the form's Filter property:

Me.Filter = fltr

right before the Me.FilterOn = True. I have added that to the database I
sent you back.

Regards,
Nikos
 
I had tried adding that very line earlier (Me.Filter = fltr) in that very
place, but it produced a run time error:

"You cannot assign a value to this object"

Yours works, tho. What's different? Any idea?

Can't thank you enough!

Rip
 
Rip,

Welcome! Glad to have helped.

I got the same problem before I realized I had to check the combos for
zero length strings; Null didn't do it, so fltr ended up with invalid
syntax. The error message is absolutely misleading, though, like it
happens all the time in Access.

Regards,
Nikos
 
Back
Top