Subform Hierarchy from Query Result with multiple criteria

[kballing]

I'm relatively new to Access but very willing to get my hands dirty. Here's a problem I've been trying to figure out for the last few days for a research lab DB.

I've got three tables with the following relevant fields:

--SAMPLES-- one to many tubes
Skey - primary key unique to each sample received
S# - sample number - could be duplicate if a sample is running low and more is obtained from the same person
R# - record number to identify who a sample belongs to
A# - account number, individuals have multiple A#s but usually just one A# per sample

--TUBES-- one to many changes
Tkey - primary key unique to each tube of sample
Skey - relates to Skey in samples, each sample has many tubes with the same Skey

--CHANGES--
Ckey - primary key unique to each change performed on a tube
Tkey - relates to Tkey in Tubes, each tube can have many changes with the same Tkey



I have a form where a user can input either an S# an R# or an A#, but do to the nature of the samples, not all are known.

--Problem 1.
I want to update a subform to show the results of a query that returns records from SAMPLES with matched S# OR R# OR A# excluding nulls.
I.E. Select * From Samples Where S# = foo OR R# = bar OR A# = blah

E.G. I enter in a S# and R# and it returns 5 records with unique Skeys that have Either the specified S#, R# or both

--Problem 2. (I can probably figure this one out sooner or later)
When a record from the said query subform is selected, link the corresponding Skey to populate another subform with records from TUBES. In other words, when I select a record from a subform, a field from that record is used to link in another sub form.

E.G. from the above example: I select a record from the 5 returned above. The Skey of that record is sent to a variable linked to a second sub form.

Similarly, the user could select a specific TUBES record and the Tkey would be passed to a subform to display records from CHANGES

 

[Galaxiom]

Firstly stop using special characters, particualry # in your field names. It has special meaning and best avoided.

Not sure I understand the question but it sounds like:
The subform Record Source query should include the name of the search textboxes
SELECT * FROM Samples WHERE SNum = Forms!formname!textboxname OR etc

The subform form will need to be Requeried using the AfterUpdate event of the search boxes.

The subsubform is linked by the Master and Child Link Fields properties in the subformcontrol holding the subsubform.
The records in the subsubform will track the current record in the subform based on the fields you enter in these links.
Note that these links may use fields from the record source that are not displayed in controls on the forms.

 

[kballing]

Thanks for the advice Galaxiom, it got me on the right track.

Sorry, I only used the # character to abbreviate my field names. They can just as easily be refered to as Snum, Anum, Tnum, Cnum or whatever.

I figured out my first problem.
I set the criteria for my query to point to the form controls as you mentioned. I didn't notice I had an AND in there instead of OR.(doh)
At first the query wouldn't work. If I entered only an Snum, and ran the query it would work, but if I added a different criteria with a different Snum, it would still return the same results with the new ones appended.
I rebuilt my form from scratch and it all worked. I must have had some linked fields incorrectly set.

 

[kballing]

Actually if anyone cares, here is a forum thread that I also found really helpful along the lines of my original question.

http://www.utteraccess.com/forums/showflat.php?Cat=&Number=783603&page=&view=&sb=5&o=&vc=1