How to filter a form similarly to a table using two comboboxes.

[PeakH]

I have a continuous form that I would like to be able to filter, not too unlike a table, using combo boxes for two of the fields.



So far I have been able to make one filter work with a field that only has a couple options using the following as the On Change event:

Private Sub RoomFilter_Change()

    If Me.RoomFilter = "Flower" Then
        DoCmd.ApplyFilter , "[RoomName]='Flower'"
    ElseIf Me.RoomFilter = "Current Flower" Then
        DoCmd.ApplyFilter , "[RoomName]='Current Flower'"
    End If


End Sub

I also have a button to remove all filters and clear both combo box values.

The second combo box has 80 or so options and I have tried a few variations of the following with no luck:

Private Sub StrainFilter_Change()
Me.Filter = "StrainName ='" & Me.StrainFilter & "'"
Me.FilterOn = True
End Sub

When I try using the second combo box, all records are filtered out so nothing is displayed. I have also tried using DoCmd.ApplyFilter ,"StrainName ='" & Me.StrainFilter & "'" with the same result.

I am also assuming that I will have to somehow combine the events so that when one is changed, it adds the second filter to the first like you can do on a table.

I don't know much about VBA (though looking for classes/books) so I could use all the help I can get.

[pbaldy]

I'd use your second method, but use the after update event rather than the change event. The change event fires with every keystroke. For multiple controls, I'd create a form-level function and call it from both.

[aytee111]

As Pbaldy said, have a second sub which you call on the AfterUpdate of each of the comboboxes:

Private Sub SetFilters()
If IsNull(Me!RoomFilter) Or Me!RoomFilter="" Then
 If Isnull(Me!StrainFilter) Or Me!StrainFilter="" Then
  Me.FilterOn=False
  Me.Requery
  Exit Sub
 Else
  Me.Filter="StrainName='" & Me!StrainFilter & "'"
 End If
Else
 If Isnull(Me!StrainFilter) Or Me!StrainFilter="" Then
  Me.Filter="RoomName='" & Me!RoomFilter & "'"
 Else
  Me.Filter="RoomName='" & Me!RoomFilter & "' AND Strain='" & Me!StrainFilter & "'"
 End If
End If
Me.FilterOn=True
Me.Requery
End Sub

[PeakH]

Okay, I went with what you guys suggested and called the sub provided by aytee111.
So this is everything that I have coded on my form right now: (Still on the change event, but I'll go and change that)

Private Sub Command52_Click()
Me.FilterOn = False
Me.RoomFilter = ""
Me.StrainFilter = ""
End Sub




Private Sub Form_Load()
Me.FilterOn = False
Me.RoomFilter = ""
Me.StrainFilter = ""
End Sub


Private Sub RoomFilter_Change()


Call FilterList
        
End Sub


Private Sub StrainFilter_Change()
    
Call FilterList


End Sub


Private Sub FilterList()


    If IsNull(Me!RoomFilter) Or Me!RoomFilter = "" Then
        If IsNull(Me!StrainFilter) Or Me!StrainFilter = "" Then
            Me.FilterOn = False
            Me.Requery
            Exit Sub
        Else
            Me.Filter = "StrainName='" & Me!StrainFilter & "'"
        End If
    Else
        If IsNull(Me!StrainFilter) Or Me!StrainFilter = "" Then
            Me.Filter = "RoomName='" & Me!RoomFilter & "'"
        Else
            Me.Filter = "RoomName='" & Me!RoomFilter & "' AND StrainName='" & Me!StrainFilter & "'"
        End If
    End If
    
    Me.FilterOn = True
    
    Me.Requery
End Sub

The problem seems to be with the StrainFilter combo box.
Whenever I try to select something from there it filters out everything so there are no records left even when there should be.

Actually now that I'm thinking of it, it may have to do with the StrainName being bound to a StrainID number on another table. I'll check these before I go changing anything else.

[PeakH]

Ah hah!
The combo box was bound to the StrainID number. So I just changed it to be bound to the StrainName. It works great.

Thank you guys a lot!

[PeakH]

Oops, one more issue.

I just played around with it and a few of the strain names have apostrophes in them messing with the code. I get an error 3075: Syntax error (missing operator).
Is there an easy way around this? I'd like to keep the names as they are if possible mostly so they look good when printed.

[aytee111]

Use the ID's in the filter instead of the name, always safer.

[pbaldy]

You can work around the apostrophes:

Me.Filter="StrainName=" & Chr(34) & Me!StrainFilter & Chr(34)

[PeakH]

Yes! Thank you!

I really appreciate all your help.

I'm sure I'll be back soon