Examining the NEXT record in a report

[RSfromCO]

I know how to save a value from the current record so that when the report moves to the next record I can use the value I saved to compare it to the current record.

What if I need to compare a value in the current record to a value in the NEXT record, BEFORE the report moves to the NEXT record. Is there a way to retrieve a field value from the NEXT record?

I am trying to turn-on the detail footer if the next records date field is more than 30 days beyond the current records date field. Once I move to the next record in the report it is too late to turn on the footer for the previous record. I tried using a detail header section but turning that on/off programatically seems to cause Access all sorts of problems with formatting.

 

[randysmid]

Yes,
This is possible with VBA code. Essentially you will be making another connection to the same database, and running the same query (as SQL code) you have for the report. You will be able to use MoveNext to retrieve the next record. Personally, I would experiment with opening the parallel database in the open event of the report, then use the OnFormat event of the Detail section to advance to the next record. To be perfectly honest, I don't know if the OnFormat event is run twice when Access encounters a page break, but that is a simple test.

Let us know if you need a sample bit of VBA code to open a parellel connection.

HTH,
Randy Smith, MCP
rsmith@cta.org
California Teachers Association