Open a report to a specific record AND a specific record on its subreport (1 Viewer)

slharman1

Member
Local time
Today, 07:26
Joined
Mar 8, 2021
Messages
467
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

..forever waiting... waiting for jellybean!
Local time
Today, 20:26
Joined
May 7, 2009
Messages
19,175
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

Member
Local time
Today, 07:26
Joined
Mar 8, 2021
Messages
467
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

Enthusiastic Amateur
Local time
Today, 12:26
Joined
Sep 21, 2011
Messages
14,051
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

Member
Local time
Today, 07:26
Joined
Mar 8, 2021
Messages
467
Thanks all, I will create separate reports, one with the filtered qry and one without.
Thanks
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:26
Joined
Sep 21, 2011
Messages
14,051
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

Super Moderator
Staff member
Local time
Today, 08:26
Joined
Feb 19, 2002
Messages
42,981
Move the code I wrote from the main report to the subreport. You will need to change the references.

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

Users who are viewing this thread

Top Bottom