Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2013
    Posts
    2

    Unanswered: Filters based on Form comboxes selection

    Hello guys,

    I have a main form named "frm_Search" with two combos "Month" and "Owner" that should filter corresponding fields records in each one of the 4 tabs inside the main form. Once updated the combo I remove all filters and then filter only the field associated to that combo if the other combo is empty or using both of the filters if both combos have values. I know I do something wrong because it's not working as explained above but have no clue of what might have gone wrong. Code's inside "Private Sub Combo17_AfterUpdate()" & "Private Sub Combo42_AfterUpdate()" in the DB attached here.

    Any help would be most welcome

    Thanks and regards,
    Stefan
    Attached Files Attached Files

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1.You cannot use an expression such as :
    Code:
    If Not Combo17.Value = Null Then
    To test for a Null contents, you must use the IsNull() function:
    Code:
    If IsNull(Combo17.Value) = False Then
    2. You do not need to repeat the code twice for each subform at the beginning of the Combo17_AfterUpdate() and Combo42_AfterUpdate() procedures.
    3. You should use indentations. It makes no functional difference but it greatly improves the readability of the code.
    4. You should give more meaninful names to the control: Combo17, Combo42, Command17, Command44, etc. do not help in understanding the functional role of these controls in your application. Using the prefix "tbl_" in the names of the subforms does not help either.
    5. In your code:
    a) In the Combo17_AfterUpdate() procedure, if Combo17.Value is not null, you first set the Filter property of the subform to: "[Reporting_Month]= '" & Combo17 & "'", then you change your mind and set the same property to: "[Owner]= '" & Combo42 & "'". What happens when the contents of Combo42 is null?
    b) The same process is used in the Combo42_AfterUpdate() procedure, although you test for a not null value for Combo17 (while processing a Combo42 event).

    Here's what I would use:
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub Combo17_AfterUpdate()
    
        SetFilter
        
    End Sub
    
    Private Sub Combo42_AfterUpdate()
    
        SetFilter
        
    End Sub
    
    Private Sub Command17_Click()
    
        DoCmd.OpenForm "frm_Input1_old"
        
    End Sub
    
    Private Sub Command44_Click()
    
        Combo42.Value = Null
        Combo17.Value = Null
        SetFilter
        
    End Sub
    
    Private Sub Command45_Click()
    
        SetFilter ""
        
    End Sub
    
    Private Sub SetFilter(Optional ByVal Filter As String)
    
        Dim frm(1 To 4) As Form
        Dim strFilter As String
        Dim i As Long
                
        For i = 1 To 4
            Set frm(i) = Me.Controls(Choose(i, "tbl_Working_capital", "tbl_Upcoming_events", "tbl_Major_Productivity_Project_st3", "tbl_Functional_transformation")).Form
        Next i
        If Len(Filter) > 0 Then
            strFilter = Filter
        Else
            If IsNull(Me.Combo17.Value) = False Then
                strFilter = "[Reporting_Month]= '" & Me.Combo17.Value & "'"
            End If
            If IsNull(Me.Combo42.Value) = False Then
                If Len(strFilter) > 0 Then strFilter = strFilter & " AND "
                strFilter = strFilter & "[Owner]= '" & Me.Combo42.Value & "'"
            End If
        End If
        For i = 1 To 4
            frm(i).Filter = strFilter
            frm(i).FilterOn = IIf(Len(strFilter) > 0, True, False)
        Next i
    
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Oct 2013
    Posts
    2
    Thanks a million! Both for code and for the tips!

    Cheers,
    Stefan

  4. #4
    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
  •