Saturday, October 8, 2011

How to reposition Microsoft© Access report controls at print time

The example I am using is an estimate which has a footer with Installation, Tax, Total of Estimate and Deposit. Many of the estimates do not have Installation or Taxes. The problem is how to print the footer without showing the items not in the estimate.
This code in the designer will set the control text to blank if there is no value returned from the data source:
=IIf([TxtSalesTaxToString]>0,[TxtSalesTaxToString]*[QuoteTotal],"")

The problem is that this blank control will print on the report as an empty line so to complete the solution you need to move the visible controls up and move the blank controls to a empty spot on the report footer otherwise the blank controls will over print the controls you just moved. The last issue is that the Microsoft© Access report, by default, uses a twips as it's unit of measure. "Twip" is a unit of measurement equal to 1/20th of a printers point. There are 1440 twips to and inch, 567 twips to a centimeter.

The following is my solution to this problem if you a using Microsoft Access 2007©. If you are using 2000 or 2003 you have to make two modifications. First, the position must be in a variable of type LONG. For some reason, the "top" position does not accept a literal. Second, put the code in the format section to the section the controls are in. (i.e. ReportFooter_format ). I used DIM v72,v490,v900, v2000 as LONG.

TIP: Put your controls where you want them in design mode and then add msgbox(me!control.top) for each control to get exact position.

No comments:

Post a Comment