Filter Report with multiple check boxes and Option Group

[sqlJunior]

I have 4 check boxes and associated combo boxes on a form which filter a report.

I now have these working fine thanks to the help of KenReay who gave me the following code, which is placed in the report module on the OnOpen event:

strFilter = ""
If (Forms![xyz]![chk1]) = True Then
Me.Filter = "[item1] = Forms![xyz]![cbo1]"
Me.FilterOn = True
strFilter = " AND "
End If

If (Forms![xyz]![chk2]) = True Then
Me.Filter = Me.Filter & strFilter2 & "[item2] = Forms![xyz]![cbo2]"
Me.FilterOn = True
strFilter = " AND "
End If

If chk3 End If etc
If chk4 End If etc

However, I now need to incorporate an option group also. I am using the following code for this on another form:

If (Forms![xyx]![optGrp]) = 2 Then
Me.Filter = "[itemA] = Forms![xyz]![cboA]"
Me.FilterOn = True

ElseIf (Forms![xyz]![optGrp]) = 3 Then
Me.Filter = "[itemB] = Forms![xyz]![cboB]"
Me.FilterOn = True

ElseIf (Forms![xyz]![optGrp]) = 4 Then
Me.Filter = "[itemC] = Forms![xyz]![cboC]"
Me.FilterOn = True
Else
Me.FilterOn = False
End If


How can I filter all of these items in the reports OnOpen event?
Would apprecite some more help if your still there KenReay/anyone.

 

[KenReay]

Hi

It is just more of the same, but I think it is more readable to use SELECT CASE, this does not detract from the basic idea, it is just the style

strFilter = ""
If (Forms![xyz]![chk1]) = True Then
Me.Filter = "[item1] = Forms![xyz]![cbo1]"
Me.FilterOn = True
strFilter = " AND "
End If

If (Forms![xyz]![chk2]) = True Then
Me.Filter = Me.Filter & strFilter2 & "[item2] = Forms![xyz]![cbo2]"
Me.FilterOn = True
strFilter = " AND "
End If

If chk3 End If etc
If chk4 End If etc

SELECT CASE (Forms![xyx]![optGrp])
Case = 2
Me.Filter = Me.Filter & strFilter & "[itemA] = Forms![xyz]![cboA]"
Me.FilterOn = True
strFilter = " AND "

Case = 3
Me.Filter = Me.Filter & strFilter & "[itemB] = Forms![xyz]![cboB]"
Me.FilterOn = True
strFilter = " And "

Case = 4
Me.Filter = me.Filter & strFilter & "[itemC] = Forms![xyz]![cboC]"
Me.FilterOn = True
Case Else
Me.FilterOn = False
End Select




Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[sqlJunior]

Hi KenReay,
Thanks for coming back again.
However, I seem to be having 2 problems with the code.

All the 4 option selections and all of the 4 check boxes work fine independently.

However, the 'Case Else Me.FilterOn = False' on the option group, seems to override the check boxes. Consequently, if no option group item is selected then any check boxes selected are ignored and all records are returned. Aside from this any selected single check box will work correctly with a selected option group item and return the filtered records accordingly.

The 2nd problem is that an error is returned if I select 2 check boxes and an option group item - see below:

Syntax Error (Missing Operator) in Query '([item3] = Forms![xyz][cbo4]" = Forms![xyz]![cbo4] AND [ItemB] = Forms![xyz]![cboB])'

Note that Access seems to lose an operator and mixes up the first check box (item3) with the second check box (cbo4). Items and cbo's in the error messages vary depending on which check boxes are selected for the filter.

I've double checked the code and it's Ok and as said above all the check boxes and options work fine when used individually.

Any idea what can be wrong?

 

[sqlJunior]

It seems the second problem was due to the combo boxes retaining information from the previous filter.

After the DoCmd.OpenReport I am now clearing the combos with the following:
Me![cbo1] = Null
Me![cboA] = Null
etc
etc

All the filters now seem to work together with the exception of when no Option Group is selected - see 1st problem above.

 

[KenReay]

Hi

OK, so just take out the:

Case Else
Me.FilterOn = False


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[KenReay]

Hi

But

You cannot have an option group where no item is selected, the Option group always has a value, are you sure you have an option group and not just 4 buttons in a box?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[sqlJunior]

Sorry KenReay,
The 4th option is the groups default and returns all records.

I've set Case Else to the following:
Case Else
Me.Filter = Me.Filter & strFilter & "[itemA] Like '*' & [itemB] Like '*' & [itemA] Like '*'"
Me.FilterOn = True

This seems to be working and as yet I've not found it to cause an error.

If you think the final piece of code (Case Else) should be written in a better way please let me know. Otherwise, thanks again for all your help.

SqlJunior