Filtering sub-form and sub-subform

[MilaK]

Hello,
I have an unbound form ”frmMain” with a subform “frmSamples”. The frmSamples has a bound sub-subform “frmCNVs”.
On opening of the Main unbound form I would like to filter both the frmSamples” and also the “frmCNVs. I know how to filter the “frmSamples” but not sure how to refer to frmCNVs to further filter the data. Is this possible?
Here is the code that I use to filter the subform "frmSamples" on Opening of frmMain:

 [COLOR=#7d2727][FONT="Times New Roman"]strFrmName = "frmMain"[/FONT][/COLOR]
 [COLOR=#7d2727][FONT="Times New Roman"]DoCmd.OpenForm strFrmName[/FONT][/COLOR]
 [COLOR=#7d2727][FONT="Times New Roman"]        Forms!frmMain.frmSamples.Form.Filter = Criteria[/FONT][/COLOR]
 [COLOR=#7d2727][FONT="Times New Roman"]        Forms!frmMain.frmSamples.Form.FilterOn = True [/FONT][/COLOR]

Thank you for your help,
Mila

 

[pbaldy]

This should help:

http://access.mvps.org/access/forms/frm0031.htm

 

[MilaK]

Thanks, this is very helpful.

Could you please clarify how to reference a textbox on subform frmSamples in a query that is used to populate record source for sub-subform frmCnvs? I've tried Me.Parent!ControlName and it didn't work.

SELECT tbl_Sample1_CNVs.[chr:gene], tbl_Sample1_CNVs.[5%_CI], tbl_Sample1_CNVs.[95%_CI], tbl_Sample1_CNVs.Raw_CN, tbl_Sample1_CNVs.First_Rev, tbl_Sample1_CNVs.Second_Rev, tbl_Samples.sample_name
FROM tbl_Samples INNER JOIN tbl_Sample1_CNVs ON tbl_Samples.sample_id = tbl_Sample1_CNVs.sample_id
WHERE (((tbl_Sample1_CNVs.[chr:gene]) Like "*NRAS*") AND ((tbl_Samples.sample_name)=[Me].[Parent]![txt_sample_name].[value]));

Thanks,

Mila

 

[pbaldy]

"Me" is only valid in VBA code. In a query you'd need the full form reference from the link:

Forms!Mainform!Subform1.Form!ControlName

 

[MilaK]

I got it to work but I have a performance related question if you don’t mind...
Is it a bad idea to set up six sets of Subforms with sub-subforms on one unbound form?

I need to display data side by side for six samples (six sets of forms) so the reviewer can look across instead of scowling down. Six subforms (frmSamples) with sub-subforms(frm_CNVs) that reference the same tables but filtered differently seems to be the only way I can get this to work. Each form will display data related to one sample only.

I’m able to get this to work; however, I’m worried how slow the loading of the form will be when I have a lot of data in Access, going forward. Is there a better way of accomplishing what I’m looking for? Thanks again, Mila

 

[pbaldy]

If that gives you the look you want, I'd stay with it unless the performance becomes an issue. Options include loading a single subform, and changing which sample is displayed based on user input.