If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
print last record of 1-to-many relationship
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 |
#2
|
|||
|
|||
print last record of 1-to-many relationship
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 "dfeigen115" wrote: 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 |
#3
|
|||
|
|||
print last record of 1-to-many relationship
Thanks Duane. I'll give that a shot.
Dan "Duane Hookom" wrote: 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 "dfeigen115" wrote: 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 |
#4
|
|||
|
|||
print last record of 1-to-many relationship
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" wrote: Thanks Duane. I'll give that a shot. Dan "Duane Hookom" wrote: 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 "dfeigen115" wrote: 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 |
#5
|
|||
|
|||
print last record of 1-to-many relationship
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" wrote: 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" wrote: Thanks Duane. I'll give that a shot. Dan "Duane Hookom" wrote: 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 "dfeigen115" wrote: 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 |
#6
|
|||
|
|||
print last record of 1-to-many relationship
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" wrote: 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" wrote: 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" wrote: Thanks Duane. I'll give that a shot. Dan "Duane Hookom" wrote: 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 "dfeigen115" wrote: 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 |
#7
|
|||
|
|||
print last record of 1-to-many relationship
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" wrote: 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" wrote: 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" wrote: 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" wrote: Thanks Duane. I'll give that a shot. Dan "Duane Hookom" wrote: 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 "dfeigen115" wrote: 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 |
#8
|
|||
|
|||
print last record of 1-to-many relationship
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: Thanks Duane. I'll give that a shot. Dan "Duane Hookom" wrote: 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 "dfeigen115" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|