Parent/Child Linking Fields 1

[JohnHarkins]

Hi!

I have a report and sub-report arrangement, where the linkage is on "program" and "cycle." The master report is built on a table that uses "series" and "cycle," while the subform uses "program" and "cycle." The convenient relationship between series and program is that a series consists of first three characters that match the program name. I can, of course, create a query that is invoked to strip out the program from a series field simply by using the left function to pull the first three characters and create a field called program. Now then, I can match up the report and sub-report linked fields by specifying "program; cycle" on each.

I wanted to be able to avoid the extra query by specifying something like "program; cycle" on the report and "left([series],3);cycle" for the sub-report. Access didn't like it. Is there any easy want to handle this?

Thanks, John Harkins

 

[KenReay]

Hi

The query method you suggested yourself is the way to go.

Or amend you table design so that 'series' is not a compound column.

Regards
Ken Reay
Freelance Developer
kenneth.reay@talk21.com

 

[Steve101]

You can actually do exactly what you want to without the query. It will require you to set up an unbound, hidden text control on your main report, as follows:

(a) Open the main report in design view, and create the unbound text control. Lets name it txtLeftSeries3

(b) Set the ControlSource property of this new control to:
=left([series],3)
I assume that [series] is the name of the bound control on the main report. Make sure that you include the equal sign.

(c) Set the visible property of the above control to false
(d) Now, in the subreport's LinkMasterFields property refer to the txtLeftSeries3 control.

By adding this little bit of 'indirection', you should solve the problem.

Incidentally, this is quite a powerful technique, and I use it all the time for synchronising forms and subforms as well.

Hope this helps,
Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)