filtering a form based on criteria in its subform

[April Marano]

I have a form frmPatients whose data source is tblPatients which contains
patient identifiers such as name, address, as well as the field
PatientIDNum.

This form has an embedded subform sbfDiagnoses in datasheet view which shows
multiple diagnoses for each patient. sbfDiagnoses's data source is a query
based on a linking of two tables: 1) tblLinkPatientDiagnosis which contains
fields LinkIDNum, PatientIDNum, DiagnosisIDNum and 2) tblDiagnoses which
contains fields DiagnosisIDNum and DiagnosisName.

The two forms are connected by the child and master fields PatientIDNum.

I'm trying to develop a search form with a ComboBox with a dropdown list of
all possible diagnoses. When I click the "Search" button, I want
frmPatients to open up filtered only with patients that share the diagnosis
shown in the ComboBox.

Any ideas on how I'd do this?

April

 

[Allen Browne]

See:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html

 

[Kailash Kalyani]

Hi April,

You're going to have to change the recordsource of frmPatients. When the
sbfDiagnoses' search button is pressed, you'll basically be generating a
query there. This query should contain all the fields from frmPatients
named the same way as tblPatients and your additional filters.


so when you click the search button the code will be something like

dim myQuery

myQuery = "select * from tblPatients where " & _
"tblPatients.PatientIDNum in (select PatientIDNum from
tblLinkPatientDiagnosis where tblLinkPatientDiagnosis.DiagnosisIDNum =" &
Me.DiagnosisIDNum & ")"

Me.Parent.RecordSource = myQuery
Me.Parent.Requery


--------------
The me.DiagnosisIDNum in between &s is the combobox with the DiagnosisID.

I'd recommend you also have a button in the main form or the subform to
clear up these actions and reset the recordsource to its original table.

Kailash Kalyani
MEA Developer Support Center
ITWorx on behalf Microsoft EMEA GTSC
--------------------

From: "April Marano" <[email protected]>
Subject: filtering a form based on criteria in its subform
Date: Wed, 7 Jul 2004 21:58:40 -0700
Lines: 22
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <[email protected]>
Newsgroups: microsoft.public.access,microsoft.public.access.forms,microsoft.public.acces
s.gettingstarted,microsoft.public.access.queries
NNTP-Posting-Host: lsanca1-ar1-4-62-127-077.lsanca1.dsl-verizon.net 4.62.127.77
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.access.forms:272171

microsoft.public.access.gettingstarted:151971
microsoft.public.access.queries:206137 microsoft.public.access:79994