Unanswered: Filters based on Form comboxes selection
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.
To test for a Null contents, you must use the IsNull() function:
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:
Option Compare Database
Private Sub Combo17_AfterUpdate()
Private Sub Combo42_AfterUpdate()
Private Sub Command17_Click()
Private Sub Command44_Click()
Combo42.Value = Null
Combo17.Value = Null
Private Sub Command45_Click()
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
If Len(Filter) > 0 Then
strFilter = Filter
If IsNull(Me.Combo17.Value) = False Then
strFilter = "[Reporting_Month]= '" & Me.Combo17.Value & "'"
If IsNull(Me.Combo42.Value) = False Then
If Len(strFilter) > 0 Then strFilter = strFilter & " AND "
strFilter = strFilter & "[Owner]= '" & Me.Combo42.Value & "'"
For i = 1 To 4
frm(i).Filter = strFilter
frm(i).FilterOn = IIf(Len(strFilter) > 0, True, False)