Link Child/Master fields - Setting masterfield value in code

[Rick A]

Access 2000 and up.

I am setting linkchildfields and linkmasterfields during file open. That
works.

The masterfields is based on data from the underlying query and a field I
want to populate on the report. The issue is how to populate the field?
The subreport is in the report header. When I try to populate the value
during open it is not allowed. In the format of the report header it is too
late.

Any one know if this can be done and if so how?

Thanks,

 

[Duane Hookom]

Depending on where the master field value is coming from, you might want to
try place the value in the record source of the main report.

 

[Rick A]

Duane,

Thanks for the good idea, however, the record source of the main report will
not work (see below). That's why I'm trying this approach.

Let's see if I can say this simply, it's kind of complicated.

The report is a catalog of dogs for an event and the sub-report shows all
judges for the event. The judge's sub-form is displayed in the report
header and from an application point of view is a certification page. There
can be multiple judges for the event and the master/child link is based on
EventDateID. It works great.

The new request is to display the judges results, the first page of the
catalog (Certification page) but for a single judge. Therefore the
master/child link needs to change to EventDateID;JudgeID. The sub-report
has JudgeID as part of the recordset but the main report does not, and it is
way too difficult to add if not impossible given the layout of the
normalized database. To that end, I wanted to place the JudgeID of the
desired judge on the report and control the sub-report that way.

Does this make sense?

Anyway, I've tried hard coding the values in the master/child links and that
has not worked. I've tried placing the value on the report but that does
not seem to work. Is there anything else that you can think of that might
work? Can I reference the JudgeID from an opened form?

Thanks much,

--
Rick Allison

Depending on where the master field value is coming from, you might want
to try place the value in the record source of the main report.

 

[Duane Hookom]

If the JudgeID is in a control on an open form, you can add this to your
report's record source like:

Judge: Forms!frmYourForm!txtJudgeID

The value then becomes available for linking. You may need to use Val() if
the field is numeric.

--
Duane Hookom
MS Access MVP

Duane,

Thanks for the good idea, however, the record source of the main report
will not work (see below). That's why I'm trying this approach.

Let's see if I can say this simply, it's kind of complicated.

The report is a catalog of dogs for an event and the sub-report shows all
judges for the event. The judge's sub-form is displayed in the report
header and from an application point of view is a certification page.
There can be multiple judges for the event and the master/child link is
based on EventDateID. It works great.

The new request is to display the judges results, the first page of the
catalog (Certification page) but for a single judge. Therefore the
master/child link needs to change to EventDateID;JudgeID. The sub-report
has JudgeID as part of the recordset but the main report does not, and it
is way too difficult to add if not impossible given the layout of the
normalized database. To that end, I wanted to place the JudgeID of the
desired judge on the report and control the sub-report that way.

Does this make sense?

Anyway, I've tried hard coding the values in the master/child links and
that has not worked. I've tried placing the value on the report but that
does not seem to work. Is there anything else that you can think of that
might work? Can I reference the JudgeID from an opened form?

Thanks much,

 

[Rick A]

Duane,

Not the best way to "skin the cat" but what you suggested worked perfectly.
I need to document the heck out of this because I'm sure years from now I'll
wonder by I did what I did.

Regards,