Filter question

[Guest]

hello to all

My form has 2 comboboxes:cboCategory and cboPriceRange, with the important
columns being CategoryID and PriceRangeID respectively. The item showing in
cboCategory determines the choices available in cboPriceRange. All of this
works well.

Now, I want to use these 2 selections to filter my records to show ProductID
and ProductName when a button (cmdFilter) is clicked. It the user selects
Power tools and $100 - $200, I want to filter my records to show only
Products matching these criteria.

I'm new enough to coding that I can't seem to get the code for this button
right. It must also be able to filter the records properly where one or both
comboboxes are blank. Also, is it best to show the filtered product list in a
subform?

Much thanks for any help

 

[Marshall Barton]

My form has 2 comboboxes:RoomTypeIDand cboPriceRange, with the important
columns being CategoryID and PriceRangeID respectively. The item showing in
cboCategory determines the choices available in cboPriceRange. All of this
works well.

Now, I want to use these 2 selections to filter my records to show ProductID
and ProductName when a button (cmdFilter) is clicked. It the user selects
Power tools and $100 - $200, I want to filter my records to show only
Products matching these criteria.

I'm new enough to coding that I can't seem to get the code for this button
right. It must also be able to filter the records properly where one or both
comboboxes are blank. Also, is it best to show the filtered product list in a
subform?

Use code along these lines:

If Not IsNull(Me.cboCategory) Then
strWhere = " And CategoryID = " & Me.cboCategory
End If
If Not IsNull(Me.cboPriceRange) Then
strWhere = " And PriceRangeID = " & Me.cboPriceRange
End If

Me.Filter = Mid(strWhere, 6)
Me.FilterOn = True

For a more detailed example, see:
http://allenbrowne.com/ser-62.html