Getting started with filters.

[Jimmy]

I have two questions regarding using filters.

One: I would like to be able to filter the records, displayed in a form
based on several criteria using checkboxes or option buttons. The table has
a number of Yes/No fields that are used to describe an item. These include
things such as BlueColor, RedColor, etc. On the form I want to have a
seperate button for each attribute. As the user selects one of the buttons I
want to filter the list to include only those records that have that
attribute. This would be done most likely using docmd.applyfilter. The catch
is, the user might select BlueColor so the list should show only records
where BlueColor is True. Then they might select RedColor and I want the list
to to then show all records where BlueColor is True and RedColor is True.

Two: On the above described form is a button to Check or Uncheck all records
(each record has a field called PrintMe used for this purpose). How can I
set up the filtering so that Check all would only affect those records in
the filtered dataset?

I'd appreciate some input on where to begin. Please ask if something is
unclear.

[storrboy]

Jimmy wrote:

I have two questions regarding using filters.

One: I would like to be able to filter the records, displayed in a form
based on several criteria using checkboxes or option buttons. The table has
a number of Yes/No fields that are used to describe an item. These include
things such as BlueColor, RedColor, etc. On the form I want to have a
seperate button for each attribute. As the user selects one of the buttons I
want to filter the list to include only those records that have that
attribute. This would be done most likely using docmd.applyfilter. The catch
is, the user might select BlueColor so the list should show only records
where BlueColor is True. Then they might select RedColor and I want the list
to to then show all records where BlueColor is True and RedColor is True.

Two: On the above described form is a button to Check or Uncheck all records
(each record has a field called PrintMe used for this purpose). How can I
set up the filtering so that Check all would only affect those records in
the filtered dataset?

I'd appreciate some input on where to begin. Please ask if something is
unclear.

My preference to doing something like this is build a WHERE statement
and append it to a base query. I feel this makes the process more
flexible and limits results before a filter would be applied. I
usually start this process on either a form opening or in the click
event of a "Refresh" button. It would go something like this.

Dim SelPart as String
Dim WhrPart as String

If {confirm that a value has been selected} then
WhrPart=WhrPart & " ([fldName]='" & [controlname] & "') AND"
'Make sure the string is appropriate for the datatype
' proper quotes for strings, # around dates blah,blah
End If

'Do above for each control in criteria.
'Use a loop if many controls need to be checked

If WhrPart<>"" Then
'Remove trailing ANDs
WhrPart=Left(WhrPart,Len(WhrPart)-4)
WhrPart="WHERE(" & WhrPart & ")"
End If

Me.RecordSource=SelPart & WhrPart & ";"
Me.Requery