[AccessD] filtering a subform

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