Query Form with optional criteria

[jeannin]

I have a table [HIP-MSP-Filter] with the following text fields
Status
group_type
query_hits
PLATFORM
Group_No_7
Group_No_3

I need to be able have a form which uses combo boxes as required and optional filter criteria and then displays the results in an updatable subform.

[Forms]![frm_update_&_re view]![cbo_Status] Required Filter

[Forms]![frm_update_&_re view]![ cbo_group_type] Optional Filter, cbo needs to be grouped

[Forms]![frm_update_&_re view]![ cbo_query_hits] Optional Filter, cbo needs to be grouped

[Forms]![frm_update_&_re view]![cbo_ PLATFORM] Optional Filter, cbo needs to be grouped

[Forms]![frm_update_&_re view]![cbo_Group_No_7] Optional Filter, cbo needs to be grouped

[Forms]![frm_update_&_re view]![cbo_Group_No_3] Optional Filter, cbo needs to be grouped

I have already tried the below which did nothing

Private Sub cbo_group_type_ AfterUpdate()
If IsNull(Me.cbo_g roup_type) Then
Me.FilterOn = False
Else
Me.Filter = "group_type = """ & Me.cbo_group_ty pe & """"
Me.FilterOn = True
End If
End Sub

Any help is greatly appreciated.

[Lysander]

If I understand this correctly, you are opening a form, with a sub-form.

The main form is used to select what filters to apply to the sub form.

What I do is have the sub form disabled, and then when all the combo boxes have been selected, change the recordsource of the sub form and enable it.

You could do this in the after_update event of the last combo box, or have a seperate button, say, 'Filters Selected' that applies the filters you want.

An example is below.
Subform is called "fsubPostPregna ncyDataEntry"

strSql="Select * from MyTable Where "
strSql=strSQL & " group_Type='" & cboGroupType & "'"
strSQL=strSQL & " AND nextfield=" & cboNextOne & "

etc
   
Me.fsubPostPregnancyDataEntry.Form.RecordSource = strSQL
Me.fsubPostPregnancyDataEntry.Form.Enabled = True