Link to home
Start Free TrialLog in
Avatar of Bob Collison
Bob CollisonFlag for Canada

asked on

In MS Access - Filter Form Data using value from another non-linked form

I have a Main Form (A) that is bound to Table A.  Form A contains a Non-linked Form (B) which is bound to Table B that is used to select a record / data from Table B.
I need to filter the data displayed in Form B based on a field [FILTER_KEY] in Form C that is bound to Table C.
I  don't want to use a MS Query to filter the the data in Form B based on the filed in Form C.
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Let me make sure I understand.

1.  Form B is actually a subform on Form A, but it is not linked using a master/child field relationship with any field on Form A.  (True/False)

2.  Form C is bound to Table C, and you somehow want to filter the data in subform B based on the value of the field [Filter_Key] in one of the records of Form C.  (True/False)

How are you opening Form C to select the record to use as the filter for subform B?

Generally, the way I would do this is to have a button (or label) on subform B that when clicked, would open Form C.  Then on Form C, I would have Cancel and Filter buttons.  If the user clicks the Filter button, I would set the Filter property of subform B using a syntax similar to:

Private Sub cmd_Filter_Click

    Forms("frmA").subFormBControlName.Form.Filter = me.[Filter_Key]
    Forms("frmA").subFormBControlName.Form.FilterOn = True

    Docmd.Close acform, me.name

End Sub

This should work as long as the value stored in the [Filter_Key] field is a valid criteria string.  Make sure you change "subFormBControlName" in the code above to the name of the subform control that is on Form A.


Avatar of Bob Collison

ASKER

Hi fyed,
1. True.
2. True.
Form C is a Menu that is already open (and must be openned to open Form A is open.  It contains a field called [PERIOD].  eg. 2010.  This is the field that is to be used to filter the data displayed in Form B.

Form C cannot be closed unless Form A is closed thus ensuring data integrity.  The field to filter Form B data is therefore always available in Form C.

Thanks.
I like to use database properties in cases such as this.  On Form C, save the relevent filter value from the appropriate event (maybe a selection in a combo box, maybe the Form_Current event) to a database property, and then you can use it to filter Form A or for other purposes.  Here is some code that works with database properties:
Option Compare Database
Option Explicit

Private dbs As DAO.Database
Private prp As DAO.Property
Private prps As DAO.Properties
Private strPropertyName As String
Private strPropertyValue As String
Private lngDataType as Long
Private varPropertyValue As Variant


Public Sub SetProperty(strName As String, lngType As Long, _
   varValue As Variant)
'Created by Helen Feddema 2-Oct-2006
'Modified by Helen Feddema 2-Oct-2006
'Called from various procedures

On Error GoTo ErrorHandler

   'Attempt to set the specified property
   Set dbs = CurrentDb
   Set prps = dbs.Properties
   prps(strName) = varValue

ErrorHandlerExit:
   Exit Sub

ErrorHandler:
    If Err.Number = 3270 Then
      'The property was not found; create it
      Set prp = dbs.CreateProperty(Name:=strName, _
         Type:=lngType, Value:=varValue)
      dbs.Properties.Append prp
      Resume Next
   Else
   MsgBox "Error No: " & Err.Number _
      & " in SetProperty procedure; " _
      & "Description: " & Err.Description
      Resume ErrorHandlerExit
   End If

End Sub

Public Function GetProperty(strName As String, strDefault As String) _
   As Variant
'Created by Helen Feddema 2-Oct-2006
'Modified by Helen Feddema 2-Oct-2006
'Called from various procedures

On Error GoTo ErrorHandler
   
   'Attempt to get the value of the specified property
   Set dbs = CurrentDb
   GetProperty = dbs.Properties(strName).Value

ErrorHandlerExit:
   Exit Function

ErrorHandler:
   If Err.Number = 3270 Then
      'The property was not found; use default value
      GetProperty = strDefault
      Resume Next
   Else
      MsgBox "Error No: " & Err.Number _
         & " in GetProperty procedure; " _
         & "Description: " & Err.Description
      Resume ErrorHandlerExit
   End If

End Function

Public Function ListCustomProps()
'Created by Helen Feddema 3-Oct-2006
'Modified by Helen Feddema 3-Oct-2006
'Lists DB properties created in code (as well as built-in properties)

On Error Resume Next
   
   Set dbs = CurrentDb
   Debug.Print "Database properties:"
   
   For Each prp In dbs.Properties
      Debug.Print vbTab & prp.Name & ": " & prp.Value
   Next prp

End Function

==================================
Usage examples:

Private dbs As DAO.Database
Private prp As DAO.Property
Private prps As DAO.Properties
Private lngDataType As Long
Private strPropertyName As String
Private strPropertyValue as String
Private varPropertyValue As Variant

Date
====
    strPropertyName = "PropName"
    strPropertyValue = CStr(dteStart)
    lngDataType = dbDate
    Call SetProperty(strPropertyName, lngDataType, _
       strPropertyValue )

   GetStartDate = CDate(GetProperty("PropName", ""))

Text
====
   strPropertyName = "PropName"
   strPropertyValue = CStr(cbo.Value)
   lngDataType = dbText
   Call SetProperty(strPropertyName, lngDataType, _
      strPropertyValue )

   strDocsPath = GetProperty("PropName", "")

Long
====
   strPropertyName = "PropName"
   strPropertyValue = CStr(lngID)
   lngDataType = dbLong
   Call SetProperty(strPropertyName, lngDataType, _
     strPropertyValue )

   lngID = CLng(GetProperty("PropName", ""))

Integer
=======
   strPropertyName = "PropName"
   strPropertyValue = CStr(lngID)
   lngDataType = dbInteger
   Call SetProperty(strPropertyName, lngDataType, _
     strPropertyValue )

   intID = CInt(GetProperty("PropName", ""))

Saving to a custom property from a control's AfterUpdate event
==============================================================
Private Sub txtDate_AfterUpdate()
'Created by Helen Feddema 2-Sep-2009
'Last modified 2-Sep-2009

On Error GoTo ErrorHandler
   
   If IsDate(Me![txtDate].Value) = True Then
      dteSingle = CDate(Me![txtDate].Value)
      strPropertyName = "SingleDate"
      Call SetProperty(strName:=strPropertyName, _
         lngType:=dbDate, varValue:=dteSingle)
   End If
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & _
      Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

One of the advantages of this method is that once you have saved the value to a property, the form from which it is saved can be closed.  This is also convenient for filtering queries.
Hi Helen,
What you are suggesting is much too complicated for my level of understanding.  The value in Form C is always selected and avaialable and therefore also availlable in Table C.

The issue I have is not how to get the data to filter by.  I can easily put in on Form A as an unbound field.  What I don't understand is how to filter the data displayed in Form B basd on the data in the unbound field on Form A.

By the way if I write a MS Accsss query to select the  data and use it as the bound data source for Form B it works fine.  I just prefer not to use a MS Access query.

Thanks.
ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Helen,
This latest suggestion (select statement) looks like what I am after.
At this point I'm just not sure where to put the Select Statement.  Does it go in Form B - Data Tab - Record Source in place of the MS Access Query I am curently using?
Thanks.
Bob,

the latest recommendation came from me (fyed)

Yes, you could just put that as the RecordSource for Form B, or you could save it as a stored query (my preference as I really dislike SQL that is stored in the RecordSource of a form or as the RowSource of a control).  I would save that query as qry_Form_B_RecordSource, but that's just my naming convention.
Hi fyed,
The 'Select Statement' does the trick!  I'll use that solution.
Just out of interest, why do you not like SQL stored in the Record Source property?
Thanks.

1.  It has to be compiled at run time.

2.  I prefer to have all of the queries that can be saved visible in the NavPane (or database window for older versions).  When you save the SQL in the RecordSource property, it is less visible.  I have a Search function I wrote many years ago that searches stored queries for text strings, but it only works on stored queries, not on the properties of forms and controls.  My standard naming convention for SQL queries is "qry_FormName_RecordSource" or "qry_FormName_ControlName"  or "qry_ReportName_RecordSource".  With this convention, it is very easy to identify which form (or report) a query belongs to, and they sort so that all the queries which apply to a particular form are grouped together.