Report/subreport question

[Guest]

I have a subreport that i want to link to another report. I already have the
same parameters in another report in another database; however, I do not know
how they relate to one another.

Is there a way in properties to see just how they are linked? I want to
link the date range parameter question but for some odd reason when I do that
to the two reports, the question pops up numerous times....3 or 4 times. I
am guessing that means my report is not linked properly but how do I tell
what they are linked on. I looked in properties.

Does anyone know where to look.
Judy

 

[Guest]

In the design view of the main report, select the subreport. On the Data tab
of the property sheet, look at the Link Child Fields and Link Master Fields
properties. If you click the elipsis to the right of either of these, it
brings up a little wizard that allows you to select fields to link on.

Barry

 

[Guest]

Barry,

In the data tab, i have no link child field properties.

 

[Guest]

Make sure you are selecting the subreport control, not the source report
itself. You know you have it selected correctly if the sizing handles around
the border are visible. It usually takes one click to select the control.
Subsequent clicks select things within the report.

Barry

 

[Guest]

Barry,
I am clicking on the subreport/properties and there is not any linking in
data tab

 

[Guest]

I think we must have a small miscommunication. Just so I'm sure I haven't
left anything out, I'll lay this out in steps.
1- Open the parent report in design view (not the report you're using as a
subreport). You should see the subreport embedded here.
2- Single-click the subreport control. The property sheet's caption bar (at
the top) will say:
Subform/Subreport: YourSubformControlName

If you don't see "Subform/Subreport", you don't have the control selected.

3- With this selected, go to the Data tab. You should see three properties:
Source Object
Link Child Fields
Link Master Fields

These instructions should be valid for all versions of Access (I can't
specifically vouch for Access 2007 or Access 1.0).

Barry

 

[Guest]

Judy:

There seem to be two separate issues involved here:

1. Linking a subreport to its parent report on key fields, e.g. a Customers
report might have an Orders subreport linked on CustomerID. This is done by
setting the LinkMasterFields and LinkChildFields properties of the parent
report's subreport control as Barry explained. The subreport control is the
control n the parent report which houses the subreport. In the above example
both properties would be set to CustomerID. Normally the fields will be the
primary key of the parent report's underlying recordset and the corresponding
foreign key of the subreport's underlying recordset.

2. Using the same parameters to restrict the result sets of both a parent
report and a subreport. In your case it sounds like you want to do this on
the basis of a date range defined by start and end date parameters. The best
approach in this sort of scenario is to make the parameters references to
controls on an unbound dialogue form. First create a form, frmDateRangeDlg
say, and add two text boxes txtStartDate and txtEndDate to the form. Add a
command button which opens the parent report. The wizard can set up the
button for you or you can put code along these lines in its Click event
procedure:

DoCmd.OpenReport "YourReportName", View:=acViewPreview

You might want to put a second button on the form to print the report with:

DoCmd.OpenReport "YourReportName", View:=acViewNormal

In the queries on which the report and its subreport are based reference the
controls on the form like so:

PARAMETERS
Forms!frmDateRangeDlg!txtStartDate DATETIME,
Forms!frmDateRangeDlg!txtEndDate DATETIME;
SELECT *
FROM YourTable
WHERE YourDate >= Forms!frmDateRangeDlg!txtStartDate
AND YourDate < Forms!frmDateRangeDlg!txtEndDate + 1;

With date/time parameters it’s a good idea to declare them as such in the
query as I've done here. Otherwise a date parameter entered in a short date
format could be interpreted as an arithmetical expression rather than a date
and give the wrong result. Also, the way I've defined the date range is more
bullet-proof than a BETWEEN…AND operation as it caters for sloppy data, where
a non-zero time of day might have crept into a date in the table unnoticed;
this can easily happen if precautions are not taken against it at design
time. Any rows which such a date/time value on the last day of the range
would not be returned by a BETWEEN…AND operation.

Rather than opening the report directly you would open the form, enter the
dates and click the button. It is possible to use the same dialogue form for
more than one parent report which requires a date range, but that needs a
little bit more doing to the form and report. Its simple enough to do,
however, so if you want to try it let me know.

Ken Sheridan
Stafford, England