Size to Fit: Text Box Edition

As you likely know, Microsoft Access has a command to auto-fit controls to their contents.

Form/Report Design > Arrange > Size/Space > Size To Fit

This feature is straightforward for controls with static content (content that remains the same from design time to runtime) like Labels and Buttons.  Running "Size to Fit" on these types of controls will automatically set the control's width and height properties to accommodate the control's caption property, taking into account the font, font size, and font weight.

But what does Access do with controls that display dynamic content?

Size to Fit for Text Boxes, Combo Boxes, Etc.

Access has no way of knowing how much content will be displayed at runtime for controls such as text boxes, combo boxes, list boxes, and others.

In fact, the Access documentation says that Size To Fit does not do anything for these controls:

Not all controls that contain text or an image can be sized by the SizeToFit method. Several controls are bound to data that can vary in size from one record to the next. These controls include the text box, list box, combo box, and bound object frame controls.

This documentation is wrong.

Using Size to Fit on a text box will not adjust the control's width (Access has no idea how wide the control will be at runtime), but it will adjust the control's height by rounding it to the nearest multiple of text lines.  In other words, if the text box is about two lines of text high, Access will calculate the height of a single line of text and then multiply by two to set the text box height.  If the control is less than half a line high, running Size to Fit on it will set it to exactly one line high.

Here's a test that demonstrates the concept.  There are four columns of controls:

1. A label that describes the size of the original control
2. The original text box control, drawn at differing heights
3. The same text box after running Size to Fit on it
4. The same text box after changing the font to bold and running Size to Fit on it

Note that the bolded text boxes are slightly taller than their non-bolded counterparts.  This is font-specific.  Some fonts are the same height regardless of their weight (i.e., bold vs. non-bold), but most are slightly taller when bolded.  Size to Fit accounts for this difference, even with controls that support dynamic content.

Old-School Shortcuts

You can get to the Size to Fit command via the ribbon as shown above.

My personal preference is to use the old-school (pre-Access 2007) shortcut:

[Alt] + [O], [S], [F] (Format > Size > To Fit)

Yes, even in the latest versions of Access 365, the old Access 2003 and earlier shortcuts still work.

For a list of these pre-Access 2007 shortcut keys, check out this article I wrote on the subject:  Blast From the Past.

Blast From the Past

Why use keyboard shortcuts from Access 2003 when developing in 2020? Because starting with Access 2007, the new shortcuts suck.

No Longer SetMike Wolfe

Cover image created with Microsoft Designer