May 8 2011, 02:46 PM
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
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 ?
May 9 2011, 08:33 AM
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?
May 14 2011, 11:55 AM
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.
May 14 2011, 12:32 PM
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.
May 23 2011, 02:43 AM
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.
May 23 2011, 02:56 AM
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here