How to search for records on a main form based on a value in their subform?

[ConfusedDatabaseDesigner]

Hello! I'm fairly new to Access and suspect I have BIG gaps in my knowledge on how to use it. Currently I'm desperately needing help/advice/guidance on this one particular issue. I suspect it's a simple fix, so hopefully this won't take up much of your time.



I work for a psych lab and was asked to create a database to keep track of our participants. My main form is for basic participant info (Names, birth dates, contact information, etc.). It is linked to three subforms by participant ID number. Each subform has it's own table as a record source, as does the main form. Also, the design is such that each subform is on it's own tab, if that matters. These subforms help us keep track of the person's participation(s) with us at different locations: the lab, at schools, and at another site. (The reason three separate subforms are necessary, in case it matters, is because the type of research we do at each site is very different, and so each type of site needs to have it's own subform with a unique set of fields.) One of the subforms has a participation date field called "PDate", and each time a participant comes into the lab, they get a new record in this subform with the appropriate PDate value.

Hopefully this database description makes sense, now here is my question:

How can I make it so that database users can easily find participants' main form record by searching on a field in the in-lab subform, namely the PDate? I don't need Access to come up with a list of participants who came into the lab on X day as a query or report, or even to filter the records (although if that's how it needs to be done, so be it). What I'm envisioning is simply to be able to navigate from record to record the way you can do when you search by a field (i.e. Last name) on the main form using the "Find and replace" thingy. Maybe there's a way to set up a text box on the main form as a search box?

There are probably a million ways to solve this problem, but given my limited skills I'd truly appreciate a clear, simple fix for this. I've created filters based on queries before for other things, but figuring that out was frustrating and took forever, plus I'd like not to need to create a whole new query that mushes together all four of the tables in question and then base the form and subforms on them, as such a change would probably be fraught with peril for an inexperienced Access user like me.

A big huge thanks in advance to anyone who can walk me through this!!!!!

[June7]

Subform does not dictate the records displayed on main form (child does not order parent). Subform records synchronize to the master record on main form. In this case, the synchronization is controlled by the ID link. The subforms can be further filtered by additional criteria. You can have an unbound box on main form that the subform RecordSource can reference for filter criteria and use code to trigger the filter action. Here is one method http://www.datapigtechnologies.com/f...tomfilter.html
Note that it does not demonstrate date parameter. Dates can be a little trickier.

Or you can use the intrinsic filter/sort tools. Right click on any field of subform to get the shortcut menu.

[ConfusedDatabaseDesigner]

Hi June7,

Thanks so much for your reply! I already tried using the intrinsic filter/sort tools, but what I'm left with is still every single participant record on the main form, which then has a filtered subform. Unfortunately, filtering the subform itself isn't really what I'm trying to do, more like filtering the main form records (in this case, participants) based on a subform value (participation date). Just to clarify, what you're saying is that this isn't possible because of the nature of the parent-child relationship, correct?

[June7]

True. Unless the date field is in the main form RecordSource, there is nothing to search on. From your description of data this date field is not in the basic participant info that is the data source for the main form.

I am sure a solution could be found to filter the main form to those participants that are associated with a particular PDate.

Start with an unbound text or combo box for entry of date value. Then VBA code could build an SQL string that would be used to reset the RecordSource property of the main form. I expect this RecordSource will involve a nested subquery. I've never tried quite this method so it is not tested.

Review
http://allenbrowne.com/subquery-01.html
http://msdn.microsoft.com/en-us/library/ms188336.aspx

Me.RecordSource = "SELECT * FROM Participants WHERE EXISTS (SELECT ID FROM tablename WHERE tablename.ID=Participants.ID AND PDate=#" & Me.boxname & "#);"

[ConfusedDatabaseDesigner]

Hmm, that sounds a bit more complicated than I'm comfortable attempting right now. Maybe one day when I learn to code and build my skills (and am feeling adventurous) I'll give it a try! I ended up resolving this in a less sophisticated manner, but I'm happy with it as it's very practical and functional.

In case anyone who has a similar question winds up on this thread looking for ideas, here's what I did: I ended up just making a query using a query wizard that contains fields from both the Basic Info (parent) table and the In-Lab participation (child) table and then basing a new "multiple items"-style form off of it. This new form has locked fields to avoid accidental fudging of data, but lets researchers see a list of participants' names, ages, genders, studies they participated in, dates they came into the lab, etc. Users can filter this form with many different child fields as they'd like. There's also a button in each row that opens the All Participants form (which is the main form with all its subforms) right at that particular participant's record (linked by ID number). It was actually a lot faster and simpler than I thought it was going to be, I think I need to get over my query phobia. That being said, it IS a lot of forms and query business to deal with so it's probably more elegant and for the best to try the method proposed by June7 (thanks again, June7!) if possible.