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

--
Internal Virus Database is out-of-date.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.0/103 - Release Date:
9/15/2005
 

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com