Can someone get me started here? I am trying to use a check box in a main
form that will apply a specific filter to a subform that has a datasheet
view (that will include/remove all items that are marked complete , where
Complete is a field in the subform source table.
Can I do this in a macro - I am assuming that I cannot.
If not, can someone get my started on how to write the subroutine - I am
still inexperienced with a lot of the available commands and how to use
them..
If Me!CheckCheckBoxName Then
With Me!SubformControlName.Form
.Filter = "Complete = 'Whatever'"
.FilterOn = True
End With
End If
End Sub
i used the AfterUpdate event, so the code will run only
when the box is checkmarked by the user; nothing will
happen if the checkmark is cleared from the box.
think of the value after "Filter =" as a WHERE statement
(like in SQL) but without the WHERE keyword.
note: make sure you use the subform CONTROL name (in the
main form), NOT the name of the subform object in the
database window - they're not always the same.
Thank you - I seem to be well on my way and have been tinkering around with
it.
One hurdle:
Here is a simplified version of what I have in my Sub:
Dim FilterTag as String
If CheckBox.value = true then
Filtertag1 = "is null"
Else
FilterTag1 = "= " " "
EndIf
.....
If Me![FilterToggleButton].Value = True Then
With Me![punch list subform].Form
.Filter = "[cross off date]" & FilterTag1 & "AND [TaskType]
=" & FilterTag2
.FilterOn = True
End With
Else
.Filteron=False
Endif
I want the .Filter line to basically set the [cross off date] argument in
the statement to a blank space if the checkbox is false. As if I were using
FILTER BY FORM and I deleted the criteria in the [cross of date] field - I
cannot seem to get this to work by setting it to "="""
As a follow up - I have gotten everything to work by using something
similar to the following:
I now have two check boxes in the Main form that will determine the
criteria. I have it set up so the user will check the desired boxes and then
hit a button that will toggle the filter on and off. The filter will be
based on what check boxes are true. The second check box has a combobox
associated with it, that included more criteria for the filter
CHALLENGE: I am having some trouble coming up with an effective way to deal
with the AND requirement in the .Filter string. when there are more than one
WHERE arguments. I have been using a string variable called "And1" that is
set to either "AND" or to " ", depending on which check boxes are selected.
Does anyone know of an easier way to deal with this (how to include &"AND"&
in the .Format string based on how many boxes are checked. I plan to add
more check boxes and coudl really see my method getting out of control...
MY CODE:
Private Sub FilterToggle_click ()
If Me!Check1.Value = True Then
FilterTag1 = "[cross off date] is null "
And1 = "AND"
Else
FilterTag1 = ""
And1 = " "
End If
If Me!Check2.Value = True Then
FilterTag2 = " [Task type] = [ComboBox1]"
Else
FilterTag2 = ""
And1 = " "
End If
If Me![FilterToggle].Value = True Then
With Me![Subform].Form
.Filter = FilterTag1 & And1 & FilterTag2
.FilterOn = True
End With
Else
With Me![Subform].Form
.FilterOn = False
End With
End If
****Untested air-code****
Private Sub FilterToggle_click ()
With Me
If .Check1 = True Then
If .Check2 = True
.Subform.Form.Filter = "[cross off date] is null AND [Task type]
= & _
.Combobox1
Else
.Subform.Form.Filter = "[cross off date] is null"
End If
Else
If .Check2 = True
.Subform.Form.Filter = "[Task type] = " & .Combobox1
Else
.Subform.Form.Filter = ""
End If
End if
If (.[FilterToggle] = True) And (.Subform.Form.Filter <> "") Then
.Subform.Form.FilterOn = True
Else
.Subform.Form.FilterOn = False
End If
End With
End Sub
****Code ends****
The above assumes [Task Type] and the bound Column of the ComboBox1 is of
numeric type. If they are of Text type, you need to delimit the value of
the ComboBox1 with single-quotes or double-quotes.
