[AccessD] filtering a subform

Susan Harkins ssharkins at bellsouth.net
Thu Sep 22 11:38:24 CDT 2005


Actually, modifying the query isn't necessary. You just have to set the
child link to an empty string (""). I had considered this yesterday, but was
hoping for something a little shorter, but this morning, it doesn't seem
such a big deal. :) 

Thanks!
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
 




More information about the AccessD mailing list