print last record of 1-to-many relationship

[dfeigen115]

i have two tables defined with a 1-to-many relationsship. One of the reports
I need to generate consists of data from each parent record and only the most
recent "child" entry (per parent). How can I filter to the children to just
include this "last" record?

Thanks in advance,
Dan

 

[Duane Hookom]

You place a text box in the group header that is bound to Max(...) which
would identify the "last" record. Then in the On Format event of the detail
section, use code like:

Cancel = (Me.DetailControl <> Me.txtGroupHeaderControl)

 

[dfeigen115]

Thanks Duane. I'll give that a shot.
Dan

You place a text box in the group header that is bound to Max(...) which
would identify the "last" record. Then in the On Format event of the detail
section, use code like:

Cancel = (Me.DetailControl <> Me.txtGroupHeaderControl)

 

[dfeigen115]

Duane,
I keep getting a run time erro 94, invalid use of null ib both controls
referenced on the "cancel" statement. If I run without the cancel, the values
of the controls display (for debugging purposes) as expected. I'm
referencing them directly via [reports]![reportname]![reportcontrol].

 

[Duane Hookom]

Why are you using the syntax "[reports]![reportname]![reportcontrol]"? I had
expected you to modify
Cancel = (Me.DetailControl <> Me.txtGroupHeaderControl)
with your actual control names.

--
Duane Hookom
Microsoft Access MVP

Duane,
I keep getting a run time erro 94, invalid use of null ib both controls
referenced on the "cancel" statement. If I run without the cancel, the values
of the controls display (for debugging purposes) as expected. I'm
referencing them directly via [reports]![reportname]![reportcontrol].

Thanks Duane. I'll give that a shot.
Dan

 

[dfeigen115]

Duane,

Sorry, I should have noted that I did try the "me." format first and got the
same "null" error, the [report] format was just the last variant I tried. The
inital code was

Cancel = (Me.LastStatusRecord <> Me.Status_id)

where the LastStatusRecord is the Max(status_id) of the child record for the
parent and status_id is the detail control.

Why are you using the syntax "[reports]![reportname]![reportcontrol]"? I had
expected you to modify
Cancel = (Me.DetailControl <> Me.txtGroupHeaderControl)
with your actual control names.

--
Duane Hookom
Microsoft Access MVP

Duane,
I keep getting a run time erro 94, invalid use of null ib both controls
referenced on the "cancel" statement. If I run without the cancel, the values
of the controls display (for debugging purposes) as expected. I'm
referencing them directly via [reports]![reportname]![reportcontrol].

Thanks Duane. I'll give that a shot.
Dan

:

You place a text box in the group header that is bound to Max(...) which
would identify the "last" record. Then in the On Format event of the detail
section, use code like:

Cancel = (Me.DetailControl <> Me.txtGroupHeaderControl)

--
Duane Hookom
Microsoft Access MVP


:

i have two tables defined with a 1-to-many relationsship. One of the reports
I need to generate consists of data from each parent record and only the most
recent "child" entry (per parent). How can I filter to the children to just
include this "last" record?

Thanks in advance,
Dan

 

[Duane Hookom]

I assume the code is located in the Detail on Format. Is it possible that the
Status_id for a record might be Null?

I suppose you could just sort your report so the most recent status record
appears first. Then move the detail controls to the Group Header section and
hide the detail section.

--
Duane Hookom
Microsoft Access MVP

Duane,

Sorry, I should have noted that I did try the "me." format first and got the
same "null" error, the [report] format was just the last variant I tried. The
inital code was

Cancel = (Me.LastStatusRecord <> Me.Status_id)

where the LastStatusRecord is the Max(status_id) of the child record for the
parent and status_id is the detail control.

Why are you using the syntax "[reports]![reportname]![reportcontrol]"? I had
expected you to modify
Cancel = (Me.DetailControl <> Me.txtGroupHeaderControl)
with your actual control names.

--
Duane Hookom
Microsoft Access MVP

Duane,
I keep getting a run time erro 94, invalid use of null ib both controls
referenced on the "cancel" statement. If I run without the cancel, the values
of the controls display (for debugging purposes) as expected. I'm
referencing them directly via [reports]![reportname]![reportcontrol].

:

Thanks Duane. I'll give that a shot.
Dan

:

You place a text box in the group header that is bound to Max(...) which
would identify the "last" record. Then in the On Format event of the detail
section, use code like:

Cancel = (Me.DetailControl <> Me.txtGroupHeaderControl)

--
Duane Hookom
Microsoft Access MVP


:

i have two tables defined with a 1-to-many relationsship. One of the reports
I need to generate consists of data from each parent record and only the most
recent "child" entry (per parent). How can I filter to the children to just
include this "last" record?

Thanks in advance,
Dan

 

[dfeigen115]

Duh, of course that was it. I have records for which no child exists. I put
some "null" logic around the cancel statement and voila it works. Thatnks for
your assistance and patience.
Dan

I assume the code is located in the Detail on Format. Is it possible that the
Status_id for a record might be Null?

I suppose you could just sort your report so the most recent status record
appears first. Then move the detail controls to the Group Header section and
hide the detail section.

--
Duane Hookom
Microsoft Access MVP

Duane,

Sorry, I should have noted that I did try the "me." format first and got the
same "null" error, the [report] format was just the last variant I tried. The
inital code was

Cancel = (Me.LastStatusRecord <> Me.Status_id)

where the LastStatusRecord is the Max(status_id) of the child record for the
parent and status_id is the detail control.

Why are you using the syntax "[reports]![reportname]![reportcontrol]"? I had
expected you to modify
Cancel = (Me.DetailControl <> Me.txtGroupHeaderControl)
with your actual control names.

--
Duane Hookom
Microsoft Access MVP


:

Duane,
I keep getting a run time erro 94, invalid use of null ib both controls
referenced on the "cancel" statement. If I run without the cancel, the values
of the controls display (for debugging purposes) as expected. I'm
referencing them directly via [reports]![reportname]![reportcontrol].

:

Thanks Duane. I'll give that a shot.
Dan

:

You place a text box in the group header that is bound to Max(...) which
would identify the "last" record. Then in the On Format event of the detail
section, use code like:

Cancel = (Me.DetailControl <> Me.txtGroupHeaderControl)

--
Duane Hookom
Microsoft Access MVP


:

i have two tables defined with a 1-to-many relationsship. One of the reports
I need to generate consists of data from each parent record and only the most
recent "child" entry (per parent). How can I filter to the children to just
include this "last" record?

Thanks in advance,
Dan