filtering subforms

  • Thread starter Thread starter Lucy
  • Start date Start date
L

Lucy

I am looking to filter various fields in a subform based
on selections from a combo box in the main form. Entry in
a combo box should be optional and I want the subform (in
datasheet mode) to show all records initially. The combo
boxes should list all the options from a field and allow
filtering based on a selection
e.g. If there was a list of records and one of the fields
in the subform was artists, you would then be able to
select "David Bowie" and only David Bowie records would
be displayed. However you do not have to filter.

Can anyone help me by telling me how to do this??

Many thanks
 
I am looking to filter various fields in a subform based
on selections from a combo box in the main form. Entry in
a combo box should be optional and I want the subform (in
datasheet mode) to show all records initially. The combo
boxes should list all the options from a field and allow
filtering based on a selection
e.g. If there was a list of records and one of the fields
in the subform was artists, you would then be able to
select "David Bowie" and only David Bowie records would
be displayed. However you do not have to filter.
Click to expand...

Your combo box's "Rowsource" property should contain a select statement against
the same table from which your subform gets its records (use the "DISTINCT"
predicate to list only one of each value - the returned list will already be
sorted):

SELECT DISTINCT [Artist] FROM tblAlbums;

Make sure that the combo box's "Limit to List" property is set to "No". This
will allow you to clear the combo box to generate a "Null" value. Now, in the
combo box's "AfterUpdate" event procedure (in the "After Update" event field in
the properties sheet, select "[Event Procedure]" and then click on the build
{...} button to the right of that field) enter code similar to the following:

'**********EXAMPLE START
Dim strFilter As String
'Retrieve the combo's value
If Len(Trim(Me.Combo1.Value) & "") = 0 Then
strFilter = ""
Else
strFilter = "[Artist]=""" & Me.Combo1.Value & """"
End If

'Filter the subform
With Me.SubformControlName.Form
.Filter = strFilter
.FilterOn = True
End With
'**********EXAMPLE END

In the foregoing example, replace "Combo1" with the name of your combo box,
"Artist" with the name of the relevant field, "tblAlbums" with the name of your
table and "SubformControlName" with the name of the subform control on your main
form (not the name of the subform, itself (although both may share the same
name). An assumption has been made that the subform shows *all* records in the
source table so that the combo box will not be displaying values that will be
unavailable in the subform. If you have any additional questions (or information
that might alter how this is implemented), just post a reply to this message.
 
Back
Top