Open a report to a specific record AND a specific record on its subreport

[slharman1]

I have a form with cascading comboboxes, I select the record from the main table and then select the record of it child table.
I want to open a report to the specific record from the parent table and to the specific record of the subreport.
Any help is appreciated as always.
Thanks

 

[arnelgp]

create 2 query.
one that will filter the main table based on the main_combobox.
and another to filter the child table based on the child_combobox.

qry1:
select * from main_table where PK = [Forms]![yourForm]![main_combo]

qry2:
select * from child_table where PK = [Forms]![yourForm]![child_combo]

create a report from qry2 (rptSub).
create a report from qry1 (rptMain).

put rptSub on rptMain as sub report.
remove any Master/Child Link fields on subreport.

 

[slharman1]

create 2 query.
one that will filter the main table based on the main_combobox.
and another to filter the child table based on the child_combobox.

qry1:
select * from main_table where PK = [Forms]![yourForm]![main_combo]

qry2:
select * from child_table where PK = [Forms]![yourForm]![child_combo]

create a report from qry2 (rptSub).
create a report from qry1 (rptMain).

put rptSub on rptMain as sub report.
remove any Master/Child Link fields on subreport.

Thanks, I forgot to mention that I have a report that prints all records of the subreport is there a way to modify the current subreport based on the combo boxes or do i have to create two seperate reports and subreports?
And why do i have to remove the Master/Child Links?
Thanks again

 

[Gasman]

I do not believe you can goto a record in a report as you can in a form? I understand that to be what you were asking?
Arnel's solution was to filter for your desired records.

 

[slharman1]

Thanks all, I will create separate reports, one with the filtered qry and one without.
Thanks

 

[Gasman]

Thanks all, I will create separate reports, one with the filtered qry and one without.
Thanks

You could use TempVars as criteria? and set them before calling the report.
If you use Like without any * then that is the same as =
So if the TempVar is "*" you get everything, and if "AVO INTERNATIONAL", you get only those records that match the criteria.?

Just another way of doing it.

 

[Pat Hartman]

Move the code I wrote from the main report to the subreport. You will need to change the references.

If Me.Parent.OpenArgs = “Filter” Then
    Me.RecordSource = “query1WithFilter”
Else
    Me.RecordSource = “query2NoFilter”
End If