Centering and Sizing in an MS Access Report

Most bar code symbols will vary in width depending on the amount of data that you encode in the bar code.  If a TAL Bar Code ActiveX control is inserted into an Access report and the control is bound to a text field in a data table, the width of the bar code may change for each page of the report if the amount of data to be encoded in the bar code changes from one database record to the next.  Access does not provide a way to automatically center a control in the report detail section therefore if the bar code increases or decreases in width from one database record to the next, the left edge of the bar code will remain in the same place on each report page and the bar code will grow or shrink to the right of where the control is originally placed.
 
The TAL Bar Code Control also has an “AutoSize” property that causes the control to automatically resize itself whenever the size of the bar code changes as a result of a change in the amount of data being encoded in a symbol.  Access does not honor the control's AutoSize property correctly when printing a report due to a bug in the Access report tool.  The following function solves both of these problems.  It should be called in the On_Format event handler for the report section containing the control.
 
Note:  When you insert the TAL Bar Code ActiveX control in your report, set the AutoSize property of the control to “Yes”

Solution

Add the following function named “CenterAndResize” to a module in your database.  Next, create a new Macro in the Macros section of your database that performs the “RunCode” action and calls the function named “CenterAndResize”.  Save the new Macro with the name “CenterBarCode”.  Open the report containing the bar code control in design mode and right click on the detail section of the report and select “Properties”.  In the Events section of the properties dialog box, select the “On_Format” event and choose the “CenterBarCode” macro as the event procedure for the event.  Close the properties dialog box and save your report.  When you run or print the report, your bar codes should be neatly centered and sized correctly.

 Function CenterAndResize() 
' This function centers and resizes the bar code in 
' the middle of the report section
 
' The following two variables must be set to the name of the specific
' report that you are working with and the name of the TAL Bar Code
' ActiveX control that resides in that report. These names will most
' likely different that the values shown below and will need to be modified
' to match the names of your report and your control.

' set the name of the report
ReportName$ = "Report1"
' set the name of the TAL Bar Code control in the report
BarCodeCtlName$ = "talbarcd1"
 
' get report width
ReportWidth = Reports(ReportName$).Width
' get bar code width
BarCodeWidth = Reports(ReportName$).Controls(BarCodeCtlName$).Width
' calculate the left edge of the bar code so it will 
' be centered in the middle of the report section.
X = (ReportWidth / 2) - (BarCodeWidth / 2)
 
'set the left edge of the bar code
Reports(ReportName$).Controls(BarCodeCtlName$).Left = X
 
' fire the refresh event for the bar code - this serves two purposes:  
'(1) it makes sure that the bar code is repainted in the middle of the report section
' (2) it forces the AutoSize property, which is normally not honored in an Access report
' to work properly - firing the Refresh event for the bar code in the On_Format event handler
' for the report section forces Access to correctly resize the bar code control
 
Reports(ReportName$).Controls(BarCodeCtlName$).Refresh
 
End Function

Contact Us