Share via

Filter Records on Subform (continuous) using the Value in Combo Box on Parent Form

Anonymous
2012-03-21T21:39:44+00:00

I wish to filter records on subform (continuous) using the value in a combo box on data entry parent form, once the combo box has a selection, however, have no filter/link prior to this.  Essentially, the subform has all the lessons for the year.  This subform is "complimentary" in a manner of speaking, because it helps the person who is creating a new lesson on the parent form (data entry mode), because the person can avoid duplicating a lesson.

Here we go:

The parent form has a text box (for the name of the lesson), and the combo box is for the category (a pre-defined list of categories).  When teh name and category are entered, I wish to have the subform list only the lessons in that same category.

This won't work:

Private Sub ParentFormCategoryComboBox_AfterUpdate()

If Me.ParentFormCategoryComboBox IsNull

Then Do Not Filter Me.Subform

Else Me.Subform.[categoryID] =Me.ParentFormCategoryComboBox

End Sub

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2012-03-21T22:37:20+00:00

    In that case I'd use the AfterUpdate event of the combo box to set the subform's Filter property. Base the subform on a query showing all categories; in the combo box's AfterUpdate event put code like

    Private Sub cboCategory_AfterUpdate()

    On Error GoTo Proc_Error

    If IsNull(Me.cboCategory) Then

       Me.subformname.Form.Filter = ""

       Me.subformname.Form.FilterOn = False

    Else

      Me.subformname.Form.Filter = "[CategoryID]=" & Me.cboCategory

      Me.subformname.Form.FilterOn = True

    End If

    Proc_Exit:

       Exit Sub

    Proc_Error:

       MsgBox "Error " & Err.Number &  " in setting subform filter:" & vbCrLf & Err.Description

       Resume Proc_Exit

    End Sub

    20+ people found this answer helpful.
    0 comments

14 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2012-03-21T21:47:13+00:00

    Open the main form in design view.

    Click once on the subform to select it.

    Activate the Data tab of the Property Sheet.

    Set the Link Master Fields property to ParentFormCategoryComboBox

    Set the Link Child Fields property to CategoryID.

    No code needed!

    20+ people found this answer helpful.
    0 comments
  2. HansV 462.6K Reputation points MVP Volunteer Moderator
    2012-03-21T22:38:28+00:00

    OK, leave the Link Master Fields and Link Child Fields properties blank.

    Use the following code in the After Update event of the combo box:

    Private Sub ParentFormCategoryComboBox_AfterUpdate()

        With Me.Subform

            If IsNull(Me.ParentFormCategoryComboBox) Then

                .LinkChildFields = ""

                .LinkMasterFields = ""

            Else

                .LinkChildFields = "CategoryID"

                .LinkMasterFields = "ParentFormCategoryComboBox"

            End If

        End With

    End Sub

    3 people found this answer helpful.
    0 comments
  3. Anonymous
    2012-03-21T22:50:47+00:00

    Base the subform on a query which references the control in the parent form as a parameter, e.g.

    PARAMETERS Forms!ParentFormName!ComboBoxName LONG;

    SELECT *

    FROM SomeTable

    WHERE CategoryID = Forms!ParentFormName!ComboBoxName

    OR Forms!ParentFormName!ComboBoxName IS NULL;

    In the Current event procedure if the parent form, and the AfterUpdate event procedure of the combo box requery the subform:

    Me.SubformControlName.Requery

    Note that SubformControlName is the name of the control in the parent form's Controls collection which houses the subform, not the name of its source form object.

    But if the subform is to help avoid a duplicate lesson for the category in question being entered, why not validate the data entered in to the lesson text box in the parent form with code in its BeforeUpdate event procedure?  This would involve setting the return value of the Cancel argument to True, and opening a message box to inform the user, if (a) the category combo box is Null, or (b) the value entered in the text box already exists in the table in a row with the selected category in the combo box.  As a candidate key the lesson and categoryID columns in the table should of course be included in a unique index, either by making the two columns the composite primary key or otherwise creating a unique index to include the two columns.

    2 people found this answer helpful.
    0 comments
  4. Anonymous
    2012-03-21T22:29:50+00:00

    Not entirely....

    The subform's contents does not display when the properties are manually pre-set (predefined) in the properties box.  My goal is to have the subform list ALL the lessons in the lessons table UNTIL the category combo box is Updated (filled in).

    Thus:

    Private Sub ParentFormCategoryComboBox_AfterUpdate()

    If Me.ParentFormCategoryComboBox IsNull

    Then Do Not Filter Me.Subform

    Else Me.Subform.[categoryID] =Me.ParentFormCategoryComboBox

    End Sub

    1 person found this answer helpful.
    0 comments