print last record of 1-to-many relationship

  • Thread starter Thread starter dfeigen115
  • Start date Start date
D

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
 
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)
 
Thanks Duane. I'll give that a shot.
Dan

Duane Hookom said:
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)
Click to expand...
 
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].
 
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


dfeigen115 said:
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].

dfeigen115 said:
Thanks Duane. I'll give that a shot.
Dan
Click to expand...
Click to expand...
 
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.

Duane Hookom said:
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


dfeigen115 said:
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].

dfeigen115 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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


dfeigen115 said:
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.

Duane Hookom said:
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


dfeigen115 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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

Duane Hookom said:
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


dfeigen115 said:
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.

Duane Hookom said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top