How to catch the last record before a change?

[Happy YN]

I know that there is a function to look at a previous record and thus identify if there as been a change in a certain field. What I need to do is to catch the last record BEFORE a change. Obviously in a report, once it has moved to the next record, it is too late by then to change a previous one or is there a way that one can force a reformat of the report so one can take appropriate action so as to say highlight the last entry for a particular field before it change
Thanks

 

[Pat Hartman]

If the field in question is in the report's sort order, try grouping on it. You can add group headers or footers if you need to.

 

[Happy YN]

I don't understand fully. Why should grouping help. Again how would I know or rather how would the report know that this is the last record of the group. To be more specific, I would like to calculate the balance of in/out transactions for each date (using a calculated field). Each date has any number of transactions but I only want to show the balance for the last transactin of that date. i.e I would use an expression to determine whether that field should be visible or not exactly like in a bank statement. Hence:

Date,In,Out,Balance
7/7,5,10
,8,40
,100,20,43
8/7,25,50
,75,45,48

 

[Pat Hartman]

Because when you set the group by properties, the report allows you to define a group header and/or a group footer. The header gives you a place to do stuff before a new group starts and the footer gives you a place to do stuff as a group ends. Group totals go in the group footer. Usually the group name goes in the group header so it doesn't have to be repeated on every line of the detail.

 

[Happy YN]

Thanks Pat but that still does not help me. I don't want to have to show a footer for every date with info etc. I want to have it in the body of the report. The footer will do stuff after I'm finished but what I want is to do stuff just before I finish i.e. the balance textbox will be invisible but for the last record of that date it will become visible and then revert back to invisible for the next date

 

[Pat Hartman]

There is no event that recognizes "this is the last record" of some undefined group. I told you how to define group breaks. If you change your format slightly, you should be able to make it work.

It isn't necessary to actually have any controls defined in group headers and footers. You can leave their height at 0 but you can still put code in their events. The problem is that in the group footer, the last record of the group has ALREADY BEEN PRINTED. You can't get it back. You would need to write your own code in the detail events that worked with the recordsetClone to look at the next record each time a record is printed.

My vote is to work around the format issues and use the footer since it is a "no code" solution. Working with the recordsetClone will be more difficult. Don't forget! The RecordsetClone is a DAO Recordset so use DAO when working with it rather than ADO.

 

[Happy YN]

Thanks Pat I will try and work with your ideas! do you think looking at the recordsetclone for each record would noticeably slow down the report production?

 

[Pat Hartman]

It could if your report processes a lot of records. Essentially, looking ahead one record causes the recordset to be read twice. You also need to be careful if you write this kind of code. The Format event may be run more than once for a record. Access formats a row and then decides if there is room on the page to print it. If there is not enough room, it runs the Retreat event, runs its page break logic and then runs the Format event again for that record.