Ensuring uniform control width on forms and reports

Last Updated 2010/09/27

This utility ensures you have uniform control width on forms and  reports.   This example shows the Unit Number as found in many forms and reports through out the Granite Fleet Manager.   Other examples might be part numbers, serial numbers, license plate numbers or other fields in which you must display all the data and yet the field might be too short or you unexpectedly had to lengthen the field.

Visit my blog posting on why I created this utiliy.

See the red circled column following screen shot for an embarrassing example of how this utility can be useful.  My excuse wasn't a good one so I created this utility.  The utility analyzes and stores the widths and other data of all controls as well as columns in list boxes and combo boxes.    You can then filter on a partial control name.

The blue circled rows show a combo box which has a width for the combo box control as well as the width when F4 has been pressed or the down arrow clicked and multiple records visible.

Notes:

- the Combo Box/ListBox Field Name column is found by analyzing the query on which the combobox/listbox is based on.  This shows the width of the appropriate column.  If the query column is based on an expression or function call then it won't be found.  Same if the source for the combo box/listbox is a call back function.
 - the control width in this table is in twips (1440 per inch) and not inches or cm's as per your regional settings.
 - if the control width is 0 then it's very likely to be a zero width combo box column.
 - unbound controls won't be visible on the filtered form unless you've entered a partial control name which matches the control name on the form..  This utility will find all bound controls and listbox/combobox controls based on fields.
 - I use my own naming standard for fields in tables. The field eUnitNbr is on the Equipment table. See Tony's Table and Field Naming Conventions
 - Query Column is 0 based
 - The error message "Control ColumnWidth/query count mismatch" means the number of ColumnWidth values on the combo box/list box property sheet doesn't match the number of columns in the controls row source query.

Download the zipped MDB file here and import the table and the form into your MDB/ACCDB.

This code illustrates going through the collection of forms and reports then going through the collection of controls on each form or report.    If the control matches certain criteria, being a text, combo or list box then various properties are read from the control and placed in a table.  If a combo box or list box the field name is extracted from each column of the Row Source query. 

[ Main | Access | Downloads ]

   Auto FE Updater distribute new and updated Front End databases to your users with several mouse clicks.

Granite Fleet Manager - the best designed fleet maintenance tracking and management system available