sub report not linking to main report

[steveo]

It would be a great help if people would'nt mind reading
this large post and letting me know what they think.

Apologies for the re-post, but it got no replies and I've
tried to hide the relevant lines with.

if me.subreport.field <> me.mainreport.field then
me.subreport.field1.visible=false
me.subreport.field2.visible=false
me.subreport.field3.visible=false
end if

I then set the can shrink property on all sections of the
sub report and all controls on the subreport (there are no
labels). However this has not really worked, it does make
the lines which are not relevant to the main report appear
blank but by page 10 there is a 5cm gap between the title
and the lines.

The original problem is below, basically the sub report is
bringing back ALL its records instead of the ones related
the main report.


-- ORIGINAL POST --

Sub Report NOT linking to main report

Server: Win2000 SP4 + SQL Server 2000 SP3
Client: Win2000 SP4 + Access 2000 SP3 (Access Data Project)

I have a sub report which shows all data in its recordet,
not the data associated with a field in the main report
which changes on each page.
I've been working on it for two days and its very
frustrating.

The data consists of columns, ab_Year, ab_Cost_Centre,
ab_Amount1, ab_Amount2 etc
The ab_Year is decimal(4), ab_Cost_Centre nvarchar(6), all
ab_Amount? float


sproc1 selects lines based on parameters (ok)

sproc2 creates a #temp table based on the users @@SPID,
which is the first sproc totaled by code. It then selects
the data back (ok)

The record source for the main report is sproc1, the
report groups by ab_year and ab_Cost_Centre, with a new
page created after each change in ab_Cost_Centre. (ok)

The record source for the sub-report is sproc2, with the
link child and link master properties set to
ab_Cost_Centre (BAD)
This usually works, but it has not, the sub report returns
all data from sproc2 not just the data associated with the
main report via ab_Cost_Centre.
The data types are the same, as they come from the same
record source sproc1 for the main, and sproc2 which has
totaled sproc1.


So I tried setting the Filter property and/or the
serverfilter property

Tried this on the sub-report of OnOPen, onPage events.
Also tried in the detail section, onFormat and onPrint,
get the same error.

Me.Filter = "ab_Cost_Centre = '" &
Report_rpt_01_Cost_Centre_Detail_L9.ab_Cost_Centre & "'"
Me.FilterOn = True

Run-time error 2101 The setting you entered isn't valid
for this property


Can anyone help with this, as I've tried nearly all that I
know. The only thing left in my abilities was to put in
the formulae of the fields in the sub report something
like:
=if(me.ab_cost_centre<>main_report.ab_cost_centre then
me.visiable = false)
But its not great, and I cant think of anything else or
why its not working...

 

[steveo]

This link is for an identical problem, however a much
clearer explanation

again no one replied to his q too.

http://accessvbsqladvisor.com/dMSAccess001.nsf/0/8ad728f662
50a092e8b3f5a7a9e491a4?OpenDocument

 

[steveo]

Just a quick reply, after 3 days of messing about and a
lot of frustration its sorted:

sproc1 selects lines for main report

sproc2 creates temp table of totals from sproc1 lines

sproc3 is record source for sub report, and has input
paramater set to identical name for main reports linking
field (eg Cost_Centre).
Which sais select * from sproc 2 where Cost_Centre =
@Cost_Centre

The input parameter property is NOT set on the sub report,
the link master + child fields are NOT set.

The linking field on the main report and sub report are
Cost_Centre, they change on each page of the main report
(the one side) and the sub report has the related records
(the many side)

Normally this should not work if the link child + master
are blank (as they are).
And the input parameter property of the sub report is NOT
set, even though sproc 3 is expecting a parameter to be
passed.

This should not work, but it does, possibly because the
input parameter is set to the same name as the field on
the main report.

Laters...

 

[SA]

Steveo

Thanks for posting the solution once you found it.