Report filter procedure

[sqlJunior]

I am using a form to filter a report. For this the form has:
An option group with 4 options.
4 x check boxes.

When an option or a check box is selected, the user then chooses the actual item from an associated combo box.

The following code in the reports OnOpen Event then filters the records:


Private Sub Report_Open(Cancel As Integer)

strFilter = ""
Select Case (Forms![Form1]![grpFilter1])

Case Is = 4
Me.Filter = Me.Filter & strFilter & "[ Field 1] = Forms![ Form1]![cbo1]"
Me.FilterOn = True
strFilter = " AND "

Case Is = 3
Me.Filter = Me.Filter & strFilter & "[ Field 2] = Forms![ Form1]![cbo2]"
Me.FilterOn = True
strFilter = " AND "

Case Is = 2
Me.Filter = Me.Filter & strFilter & "[ Field3] = Forms![ Form1]![cbo3]"
Me.FilterOn = True
strFilter = " AND "

Case Else
Me.Filter = Me.Filter & strFilter & "[ Field1] Like '*' & [Field 2] Like '*' & [Field 3] Like '*'"
Me.FilterOn = True
strFilter = " AND "

End Select



If (Forms![ Form1]![chk1]) = True Then
Me.Filter = Me.Filter & strFilter & "[ Field4] = Forms![ Form1]![cbo4]"
Me.FilterOn = True
strFilter = " AND "
End If

If (Forms![ Form1]![chk2]) = True Then
Me.Filter = Me.Filter & strFilter & "[ Field5] = Forms![ Form1]![cbo5]"
Me.FilterOn = True
strFilter = " AND "
End If

If (Forms![ Form1]![chk3]) = True Then
Me.Filter = Me.Filter & strFilter & "[ Field6] = Forms![ Form1]![cbo6]"
Me.FilterOn = True
strFilter = " AND "
End If

If (Forms![ Form1]![chk4]) = True Then
Me.Filter = Me.Filter & strFilter & "[ Field7] = Forms![ Form1]![cbo 7] OR [Field8] = Forms![Form1]![cbo7]"
Me.FilterOn = True
strFilter = " AND "
End If

End Sub


It works OK except for check box 4 (chk4).

chk4:
Combo 7 (cbo7) is a list of materials.
Field 7 and Field 8 show 2 pieces of material that are joined together (by the records key field). Field 7 and Field 8 are selected form the same material list in cbo7.

If a material is selected I need to show all the records it occurs in. For instance if a record has a material in Field 7 but not in Field 8 I still need to show the record. Hence the OR statement for chk4.

The trouble is it doesn't work.
It causes chk4 to override all the other filters. It returns records matching the chk4 combo but disregards the other check boxes and option group.

Can anyone suggest how I can write the code for chk4?
Or alternatively, perhaps they can suggest another way of writing the procedure completely.

 

[TwoOdd]

You might try putting parenthesis around the OR filter:
"([Field7] = Forms![ Form1]![cbo 7] OR [Field8] = Forms![Form1]![cbo7])"

TwoOdd
--------------
Good judgment comes from experience, and experience comes from bad judgment.
-- Barry LePatner

 

[sqlJunior]

Thanks TwoOdd,

That seems to have done the trick and I really appreciate your help.

I also seems to have a problem on clearing the report filter after opening. I am trying the following with the reports OnClose Event but I am not sure it's any good:

Private Sub Report_Close()
Me.Filter = ""
Me.FilterOn = False
End Sub

I'd certainly appreciate your advice on how to reset the report after an error on opening.

SqlJunior