[AccessD] Filtering subform from parent form

Arthur Fuller fuller.artful at gmail.com
Mon Sep 26 03:41:20 CDT 2011


I tried Allen's example on that search page and it won't run Access 2007.
Access says:

The expression After Update you entered as the event property setting
produced the following error: Object or class does not support the set of
events.

I'm baffled. I even added a MsgBox at the start of the code and Access
doesn't even there.

<vba>
Private Sub cboShowSup_AfterUpdate()
    MsgBox "Here."
    Dim strSQL As String
    If IsNull(Me.cboShowSup) Then
        ' If the combo is Null, use the whole table as the RecordSource.
        Me.RecordSource = "tblProduct"
    Else
        strSQL = "SELECT DISTINCTROW tblProduct.* FROM tblProduct " & _
            "INNER JOIN tblProductSupplier ON " & _
            "tblProduct.ProductID = tblProductSupplier.ProductID " & _
            "WHERE tblProductSupplier.SupplierID = " & Me.cboShowSup & ";"
        Me.RecordSource = strSQL
    End If
End Sub
</vba>

This doesn't make sense at all to me. If I had an earlier or later version
of Access aboard, I'd try the example there, but I don't.

Arthur


On Mon, Sep 26, 2011 at 12:12 AM, Rocky Smolin <rockysmolin at bchacc.com>wrote:

> The first thing I see is a suggestion to build a SQL statement with the
> filtering parameters in the WHERE clause.  I suppose I could try that as a
> workaround.  Annoying that the .Filter doesn't seem to work as it should,
> though.
>
> Rocky
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
> Sent: Sunday, September 25, 2011 6:36 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Filtering subform from parent form
>
> I just checked the first posting and there's a reply from Allen himself:
>
> > With Me.Child0.Form
> > .Filter = "ExternalEvents Is Not Null"
> > .FilterOn = True
> > End With
>
> His Filter above was specific to the question he was asked, of course. He's
> assuming that your filter command button is on the main form, which seems
> logical enough, but it may mean that you cannot use the built-in
> Filter-By-Form thing, and instead may have to create your own dialog for
> this situation. If it's always the same fields, then that shouldn't be a
> problem.
>
> Another approach: you could open a dialog based on the subform's record
> source, then filter it by form, then grab the Filter property, close the
> dialog and plug the filter into the above code, which would live in the
> parent form.
>
> Arthur
>
> P.S.
> Interesting exercise. I think build a little toy program and see if I can
> make it work.
>
> On Sun, Sep 25, 2011 at 9:23 PM, Arthur Fuller
> <fuller.artful at gmail.com>wrote:
>
> > I just googled "filter on an Access subform" and I suspected, got led
> > immediately to Allen Browne's site (and others). There are several
> > hits. I'd try Allen's first. IIRC A.D. Tejpal's written something on
> > this. I haven't had a need for this particular capability but google
> > as I did and you should get your answer.
> >
> > HTH,
> > Arthur
> >
> >
> > On Sun, Sep 25, 2011 at 9:14 PM, Rocky Smolin
> <rockysmolin at bchacc.com>wrote:
> >
> >> Dear List:
> >>
> >> I'm trying to set the filter of a subform from parameters on the main
> >> form.
> >> The first line is
> >>
> >>    Me.subfrmAccountsReceivable.Form.Filter = ""
> >>
> >> But it wasn't working - so I put:
> >>
> >>   MsgBox Me.subfrmAccountsReceivable.Form.Filter
> >>
> >> right after the first line and it prints "False"
> >>
> >>
> >> After that first line there's code which I've written a dozen times
> >> to accumulate the filtering criteria (in this case an account number
> >> and a >= and <= date range) but always for the parent form - never for a
> sub form.
> >>
> >> What am I doing wrong? (Besides not drinking some Scotch and making
> >> dinner at 6:15 on Sunday night.)
> >>
> >> MTIA
> >>
> >> Rocky Smolin
> >> Beach Access Software
> >> 858-259-4334
> >> www.bchacc.com <http://www.bchacc.com/> www.e-z-mrp.com
> >> <http://www.e-z-mrp.com/>
> >>
> >>
> >>
> >>
> >>
> >> --
> >> AccessD mailing list
> >> AccessD at databaseadvisors.com
> >> http://databaseadvisors.com/mailman/listinfo/accessd
> >> Website: http://www.databaseadvisors.com
> >>
> >
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>



More information about the AccessD mailing list