I'm trying to use the OnPaint and OnFormat events when printing an Access report to supress the printing of the footer line when there is only one detail record.

I found an example using an Access 97 report which works fine ( opened in 2007 and converted )

Private Sub GroupFooter3_Format(Cancel As Integer, FormatCount As Integer)
If Me.Text27 = 1 Then Cancel = True
End Sub

me.Text27 is a hidden field which counts the detail records in the group

However when I build exactly the same report in 2007 it fails to supress the footer line. I understand that in Access 2007 the format event does not fire for screen preview so I've put the code in the OnPaint event.

I've put a Msgbox to prove that the event is firing but the cancel = true statement is not actioned. Also an If statement to set a backgroundcolor based on a certain characteristic sets all the records to that color - unless you put an else statement to set them back to no color. I've encountered the same with Access 2010.

Can anyone help please ?

Thanks

Peter

Did you do a Print Preview of this report (as opposed to Open)? If you did and it is not working, can you post the database?

Sorry about delay in responding...

Yes I'm working in preview mode - the report is designed to be run from a button on a form. And yes I believe that the code in the format event works ok - when I send the report to a printer the code fires. But its the paint event that I've had problems with - when the report is built in either 2007 or 2010.

As I mentioned I took the demo report which was for Access 97 - hacked it about to suit my needs ( i.e replaced all the fields - changed the record source etc ) and then selected the conversion option offered by 2010. This works perfectly in print preview mode in 2010/2007 with just code in the format event. Presumably it must call some code in 2010 which a 2010 built report cannot - included for backwards compatability perhaps.

So I'm running with this at the present because I have very little experience so far coding reports. But I do want to find out what's going on and if I'm doing something stupid. I sense there is a lot of hidden power in VBA code in reports which I have hitherto ignored. My limited experiments with the paint event produced some weird , screen flickers and delays.

If you think it would be helpful I'll post the database. Please let me know.

Thanks for your help on this.

Peter

Feel free to post it. I don't have Access 2010.

You might want to try something else like making the controls in the footer not visible.

Yes I'll have a play with footer control visibility. If I've still got problems I'll post the db - 2007 is ok.

Thanks again for your help.

Peter

You're welcome.