filtering a mainform using data from a field in a subform

[John S. Ford, MD]

I have a main form whose underlying query lists a number of teams (A, B, C,
etc.) This main form has a subform listing the patients followed by each
team.

I've set up a search form that allows the user to select a patient and
thereby open the above main form with that patient listed in the subform.
The main form is set to look at the particular team of that patient.

What I want, however, is for the main form to filter ONLY the team following
that particular patient. As it is now, only the correct patient is listed
on the subform but the main form still has ALL the teams (not only the
patient's team).

I figure I need to use DAO's and I've tried something like this:

Dim rst As Recordset
Set rst =
Forms!frmMainEntryForm!sbfctlPatientInformation.Form.RecordsetClone
Forms!frmMainEntryForm.Filter = "TeamIDNum = " & rst!TeamIDNum

I get an error on line 3. Apparently I'm not referencing the TeamIDNum
field correctly. Any idea what I'm doing wrong here?

John

 

[Squirrel]

Hi John,

Reference it this way:

rst.Fields("TeamIDNum")

HTH -Linda

 

[Allen Browne]

See:
Filter a Form on a Field in a Subform
at:
http://members.iinet.net.au/~allenbrowne/ser-28.html

The article explains how to set the RecordSource of the main form to an
INNER JOIN statement so it contains only those teams (1 in your case) that
have a match in the subform.