I was asked by a customer to mark fields in red that changed each week in a report.

So what strategy did I come up with?

Well first I need to store the data from the original run of the report. The customer wants to know what changed since the last automated report run, so I added code to my report open routine to check for an OpenArgs flag. When the automated routine is kicked off, it opens the report with the OpenArgs flag set, but otherwise if the report is run manually, it doesn’t.

Each time the automation occurs, the report open routine will see this flag and store a copy of the data it uses from the query in the recordsource of the report. I built the table using the SQL command equivalent to a Create Table query. Then I built a log table which stores one entry, the date the report ran, and an ID. The data gets stored in the data table linked to the log table by the log ID.

Next, I need to be able to run the next automated report and open up the previous report’s data as a recordset. Then I can search for the key fields of Job ID and Phase Number which is unique per record in this report. If the line is not in the previous recordset, I know it was added and can mark the background of that detail row light green. If the line is in the previous recordset, but not in the new recordset I’ll know it was deleted and can mark that detail row background in light red and use strikeout for the text. If the record exists in both, I’ll be able to compare each field for that row and set the unequal fields to have bold red text.

Ok, but now I have another problem… How do I add a line back in that was deleted so I can show it on the report? The report will only show the detail section for each row in the current recordset. This means that I need to see if I can add the missing records into the recordset.

I haven’t done this yet, so off the cuff, what might I do? I could modify the recordsource property and create some kind of left / right join combo for the query which would put all this information into the single recordset.

Or I could just figure out ahead of time which rows were deleted and union just those rows back in.

Or maybe I could add a subreport in a second detail section that would show all the records that had been deleted. I could just do that at the end of the report, but they would not be sorted. It would be a pain to do that in the middle of the report at each point it should appear according to the sort order, but it could be done.

I think I’ll try to do a full outer join first. That means getting records in the query where either recordset has the data and either side could have no data. This is accomplished by doing both right and left joins (Although in SQL server you are allowed to tell it to do a FULL OUTER JOIN and it does all that for you, but Access DAO syntax doesn’t allow that).

I’ll let you know how it turns out!