[AccessD] filtering a subform

    Here is a solution that is absolutely code-less. In the source query for subform, have a calculated field named Status (SQL given below).

    Link MasterField  grpStatus to ChildField  Status. Option button values remain 0, -1, 1 as stated earlier.

    That is all. Nothing else needs to be done.

A.D.Tejpal
--------------

Source Query for the subform
=================================
SELECT Products.*, IIf(Forms!F_TestMain!grpStatus = 1, 1, [Discontinued]) AS Status 
FROM Products 
ORDER BY Products.ProductName;
=================================

  ----- Original Message ----- 
  From: Charlotte Foust 
  To: Access Developers discussion and problem solving 
  Sent: Thursday, September 22, 2005 22:10
  Subject: Re: [AccessD] filtering a subform


  You can't alter the subform's recordsource *after it opens*, which I
  don't find odd, given the nature and intent of subforms.  What you *can*
  do dynamically is alter the subform object, however, so another
  alternative would be to have 3 subforms to display the 3 result sets and
  simply swap out the subform on the button click.
    
  Charlotte Foust


  -----Original Message-----
  From: Susan Harkins [mailto:ssharkins at bellsouth.net] 
  Sent: Thursday, September 22, 2005 9:13 AM
  To: 'Access Developers discussion and problem solving'
  Subject: Re: [AccessD] filtering a subform


  Interesting solution. You know what I've found truly odd -- I can't find
  a way to modify the subform's recordset using code -- that's just odd. I
  realize that the subform's really just a control, so it's not going to
  have the same properties and behavior as a form, but you'd think it
  would have a Record Source property, even as a subform object. 

  I had considered just changing the linking fields, but you have to
  provide so much code to switch them back and forth, which is a nuisance,
  but at least it's a solution -- given you can't just change a Record
  Source property. 

  Susan H. 

  Susan,

      This is a solution with minimal code. The three option buttons have
  the values 0, -1 and 1 respectively. In design view, child field named
  Discontinued (on the subform) is linked to master field named grpStatus
  (on the main form).

      Source query (SQL given below)  for the subform has a calculated
  field named "ALL", with constant value of 1.

      AfterUpdate event of option group named grpStatus, has the code
  given below. SF_TESTSub is the name of control serving as container for
  the subform.

      Depending upon status of option group, you will get following
  results displayed on the subform.
      (a) First button selected - Active Records
      (b) 2nd button selected - Discontinued Records
      (c) 3rd button selected - All Records

  A.D.Tejpal
  --------------

  Source Query for the subform
  ================================
  SELECT Products.*, 1 AS [ALL]
  FROM Products
  ORDER BY Products.ProductName;
  ================================

  Form's Code Module
  ================================
  Private Sub grpStatus_AfterUpdate()
      If grpStatus = 1 Then
          SF_TESTSub.LinkChildFields = "ALL"
      Else
          SF_TESTSub.LinkChildFields = "Discontinued"
      End If
  End Sub
  ================================

    ----- Original Message -----
    From: Susan Harkins
    To: 'Access Developers discussion and problem solving' 
    Sent: Thursday, September 22, 2005 04:46
    Subject: Re: [AccessD] filtering a subform


    There's no criteria by which to filter -- I want to add an All option
  that
    shows all the records. 

    The first few options related to values. By setting the subform's
  master
    link property to the option group, I can get the subform to filter
  without
    any code. 

    Unfortunately, I can't figure out a way to force the subform to show
  all the
    records. The option group won't work because there's no underlying
  value to
    represent All in the data in the same way. In addition, a subform has
  no
    Record Source property, so I can't set it that way. 

    Here's the example -- using Products in Northwind, use an option group
  to
    create three options: Active, Discontinued, and All. Name the option
  group
    grpStatus and set the two option buttons' Option Value properties to 0
  and
    -1, accordingly. Add a subform based on Products and set the subform's
  Child
    Link Fields to Discontinued and set the Master Link Fields to
  grpStatus.
    Works great until you want to add an option that isn't represented in
  the
    underlying data -- like an All option. 

    Now, I can go to a code solution for all three options -- I just
  wanted to
    offer the simplest solution possible. 

    Susan H. 



    Something like this.... You would of course need to build the
    strSearchString criteria

    Dim strSearchString As String

    With me.ctrSubform.Form
    .Filter = strSearchString
    .FilterOn = True
    End with 


    Robert

    -----Original Message-----
    From: accessd-bounces at databaseadvisors.com
    [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Susan
  Harkins
    Sent: Wednesday, September 21, 2005 4:45 PM
    To: 'Access Developers discussion and problem solving'
    Subject: [AccessD] filtering a subform

    I'm trying to use an option group to filter a subform -- easy enough
  as long
    as there's some relationship between the options and the subform. 
     
    If there's no relationship and you don't want to use the subform's
  linking
    properties, how do you set the filter the subform's Record Source? The
    subform is based on a query. 
     
    Susan Harkins