[AccessD] Datasheet Filters

[Rocky Smolin]

More good ideas! I'll let you know how I finally engineer this bad boy.

r

On Mon, Mar 13, 2023 at 10:07 AM Ryan W <wrwehler at gmail.com> wrote:

> 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
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>