Ensuring uniform control width on forms and reports

Tony — Tue, 07 Apr 2009 01:31:21 GMT

Jim, a good friend and client complained, err commented, that the unit number was too small on one key form in the Granite Fleet Manager. He had increased the number of characters in that field on the table from six to eight. I vaguely recall him mentioning that once.

I had increased the size of that field on some forms for them a while back but clearly I hadn't done a good job of testing an example value. Turns out I also hadn't increased the field size in my testing, demo or data MDB files either. Oops. I'm sure he forgot to tell me. (I can just see Jim now rolling his eyes and giving me the appropriate finger. <smile>)

I also knew though that I would've missed a few and not made them wide enough. As far as I know Rick's Find and Replace doesn't have any way of doing this kind of analysis so I threw together some code to find where all the problems existed. And, yup, only about 5 of the 20 occurrences of that field were sufficiently wide. <sigh>

So I did some more work and realized that I should make this utility available to everyone. It has been suggested that I make this an Add-In but I don't have the time right now to figure out the quirks of the URegSysInfo table, etc, etc. Anyhow visit Ensuring uniform control width on forms and reports for details and a link to download an MDB containing the table and a form.

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.

Tony's Microsoft Access Blog : Access, Utility

Ensuring uniform control width on forms and reports