Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2012
    Posts
    4

    Unanswered: Odd Behavior When Filtering A Subform By A Combo Box On The Main Form

    Hi all, I'm going to try to be as specific as possible, but due to the content of the database, I can't provide an actual copy.

    The ultimate goal here is to filter the subform's records by the combo box on the main form.

    I have a main form, FrmInput.
    On it there is a combo box, CmbCat.
    The source for cmbCat is a table containing the options "All" and possible contents of a column in the subform's recordset.
    The subform is control named SubDocs.
    The subform itself is named subFrmDocumentation.
    The subform control is sitting on a page control named PgDocumentation.

    Now, I've tried several methods, but the current one I've been attempting is as follows:
    I've set the Filter on subFrmDocumentation to: Cat = [Forms]![FrmInput]![CmbCat]
    Then on form load for FrmInput I do: SubDocs.Form.FilterOn = False
    and: CmbCat.value = "All"

    Then for changing values on CmbCat:
    Private Sub CmbCat_Change()

    If Me.CmbCat.Value = "All" Then
    SubDocs.Form.FilterOn = False
    Else
    SubDocs.Form.FilterOn = True
    End If

    End Sub

    This reads to me that the subform should be filtered by CmbCat whenever CmbCat is not "All". However, whenever the value of CmbCat is changed, the subform's data all dissappears (it still exists in the tables thankfully), leaving just the column headers. I've tried changing the recordset and changing the filter itself rather than just turning it on and off, and both have had the same result. Could someone point out what I'm doing wrong?

    Thanks in advance.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Set the filter dynamically in the CmbCat_Change event handler:
    Code:
    Private Sub CmbCat_Change()
    
        If Me.CmbCat.Value = "All" Then
           SubDocs.Form.FilterOn = False
        Else
           SubDocs.Form.Filter = "Cat = " & Me.CmbCat.Value
    '
    ' If cat is not numeric, use:
    '
           SubDocs.Form.Filter = "Cat = '" & Me.CmbCat.Value & "'"
           SubDocs.Form.FilterOn = True
        End If
    
    End Sub
    Otherwise you need to requery the subform.
    Have a nice day!

  3. #3
    Join Date
    Jan 2012
    Posts
    4
    Changed the CmbCat Change to:
    Private Sub CmbCat_Change()

    If Me.CmbCat.Value = "All" Then
    SubDocs.Form.FilterOn = False
    Else
    SubDocs.Form.Filter = "Cat = '" & Me.CmbCat.Value & "'"
    SubDocs.Form.FilterOn = True
    End If

    SubDocs.Form.Requery
    Me.Refresh
    End Sub

    Still having the same problem.

  4. #4
    Join Date
    Jan 2012
    Posts
    4
    Sorry, found the problem. I had the CmbCat bound to the index field, not the Data field. My fault and thanks for the attempted help.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Remove the filter and FilterOn definition in the Properties window of the subform.

    2. These lines are useless, the subform is requeried when you change its Filter and FilterOn properties, while Refresh concerns the parent form only:
    Code:
    SubDocs.Form.Requery
    Me.Refresh
    Have a nice day!

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •