468,272 Members | 2,007 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,272 developers. It's quick & easy.

Query Form with optional criteria

I have a table [HIP-MSP-Filter] with the following text fields

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_&_review]![cbo_Status] Required Filter

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

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

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

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

[Forms]![frm_update_&_review]![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_group_type) Then
Me.FilterOn = False
Me.Filter = "group_type = """ & Me.cbo_group_type & """"
Me.FilterOn = True
End If
End Sub

Any help is greatly appreciated.
Jul 6 '09 #1
1 2299
344 Expert 100+
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 "fsubPostPregnancyDataEntry"
Expand|Select|Wrap|Line Numbers
  1. strSql="Select * from MyTable Where "
  2. strSql=strSQL & " group_Type='" & cboGroupType & "'"
  3. strSQL=strSQL & " AND nextfield=" & cboNextOne & "
  5. etc
  7. Me.fsubPostPregnancyDataEntry.Form.RecordSource = strSQL
  8. Me.fsubPostPregnancyDataEntry.Form.Enabled = True
Jul 8 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Steve | last post: by
1 post views Thread by Michael Israel | last post: by
4 posts views Thread by paii, Ron | last post: by
2 posts views Thread by Mark Roughton | last post: by
3 posts views Thread by bob laughland | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by zattat | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.