[AccessD] Datasheet Filters

[Ryan W]

Rocky,
  Some after lunch testing:

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
    Dim strFilter As String: strFilter = Me.Filter

    strFilter = Replace(strFilter, "[t].", "")
    Parent!T1 = DAvg("Field2", "Table1", strFilter)
    Parent!T2 = DSum("Field2", "Table1", strFilter)
End Sub



I'm stripping the [t]. prefix off the .Filter string, because we cannot
reference the subform name (in my case, the very DESCRIPTIVE "t") in the
domain functions DAvg, DSum etc.

This works fine when clearing the filter as well, since it's not averaging
and summing the entire unfiltered datasheet (which you may want to use the
ApplyType variable in the _ApplyFilter event to handle that differently.

Such as:

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
    If ApplyType = acApplyFilter Then
        Dim strFilter As String: strFilter = Me.Filter
        strFilter = Replace(strFilter, "[t].", "")
        Parent!T1 = DAvg("Field2", "Table1", strFilter)
        Parent!T2 = DSum("Field2", "Table1", strFilter)
    ElseIf ApplyType = acShowAllRecords Then
        Parent!T1 = 0
        Parent!T2 = 0
    End If
End Sub


On Mon, Mar 13, 2023 at 12:00 PM Rocky Smolin <rockysmolin2 at gmail.com>
wrote:

> On Filter didn't fire but the ApplyFilter did.  So that's good to know.
>
> Thanks
>
> r
>
> On Mon, Mar 13, 2023 at 9:14 AM Ryan W <wrwehler at gmail.com> wrote:
>
> > You probably will want to set your WHERE statement for your total and
> > average textbox's on the parent forms in one of these events
> >
> > Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
> >
> > End Sub
> >
> > Private Sub Form_Filter(Cancel As Integer, FilterType As Integer)
> >
> > End Sub
> >
> >
> > https://learn.microsoft.com/en-us/office/vba/api/access.form.applyfilter
> >
> >
> https://learn.microsoft.com/en-us/office/vba/api/access.form.filter(even)
> >
> >
> >
> > On Mon, Mar 13, 2023 at 11:07 AM Rocky Smolin <rockysmolin2 at gmail.com>
> > wrote:
> >
> > > Another question I just thought of - is there an event triggered when
> the
> > > user sets a filter on a field in a datasheet?
> > >
> > > MTIA
> > >
> > > r
> > >
> > > On Sun, Mar 12, 2023 at 7:01 PM Ryan W <wrwehler at gmail.com> wrote:
> > >
> > > > Rocky,
> > > >  The datasheet form should use .FilterOn (True or False) to tell you
> > > > whether it's filtered, and the .Filter property to show what's
> > filtered:
> > > >
> > > >
> > > > ?Forms!Form1!t.form.filteron
> > > > True
> > > > ?forms!form1!t.form.filter
> > > > ((([Table1 subform].[Field1]="foo_zip") AND ([Table1
> subform].[ID]=1)))
> > > AND
> > > > ([Table1 subform].[Color]="vbBlack")
> > > >
> > > > On Sun, Mar 12, 2023 at 6:41 PM Rocky Smolin <rockysmolin2 at gmail.com
> >
> > > > wrote:
> > > >
> > > > > Dear List(s):
> > > > >
> > > > > I have a subform in datasheet format on a main form.  Several of
> the
> > > > fields
> > > > > on the subform are currency fields.
> > > > >
> > > > > The client wants sums and averages on several of those fields. I
> > > > explained
> > > > > that you can't do that with a datasheet but that in the footer of
> the
> > > > > parent form I could give him all that data but the sum and average
> > > won't
> > > > > line up under its column.  No problem for him.  He's happy with the
> > > data
> > > > on
> > > > > the footer.
> > > > >
> > > > > Since they will be using the filters on the datasheet fields to
> > > > > restrict the data to just that they're interested in, that,
> > obviously,
> > > > will
> > > > > change the values of the sum and averages as they apply different
> > > > filters.
> > > > >
> > > > > What I need then is a way to see what the filters are, and
> generate a
> > > sql
> > > > > statement with those filters as the source of the sums and
> averages.
> > > > >
> > > > > Q: Is there a way to poll the filters on those fields? I don't know
> > of
> > > > one.
> > > > > But I'll bet there is one.
> > > > >
> > > > > MTIA
> > > > >
> > > > > Rocky
> > > > > --
> > > > > AccessD mailing list
> > > > > AccessD at databaseadvisors.com
> > > > > https://databaseadvisors.com/mailman/listinfo/accessd
> > > > > Website: http://www.databaseadvisors.com
> > > > >
> > > > --
> > > > AccessD mailing list
> > > > AccessD at databaseadvisors.com
> > > > https://databaseadvisors.com/mailman/listinfo/accessd
> > > > Website: http://www.databaseadvisors.com
> > > >
> > > --
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > https://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > https://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>