Today’s guest blogger is Vinny Malanga. Vinny is the CTO of IMI Software, Inc. , which specializes in software development for the real estate and property management industries.

Many Access developers implement subforms to display data. Most often, at design time, the developer sets the subform to a specific size. In many cases this is fine, but there are some instances where it would be preferable to dynamically resize the subform at runtime, based on the number of records that it contains, up to a maximum number. Once the maximum number of records is reached, a vertical scroll bar should appear allowing the user to scroll through the rest of the records. The maximum number is arbitrary and simply used to prevent the subform from exceeding a certain height.

I’ve provided a sample database that you can download here. The database contains a table, two forms - a parent form and a subform - and a module with the resize method.

In short, when the parent form loads, it calls a function on the subform which handles the resizing. It first retrieves the height of the header, footer and detail section of the subform. From there the subform is resized based on the height of the header, the height of the footer, and the height of the detail times the number of records. There are a couple of twips added to the total number to even out the border around the subform. Next, the record count of the underlying data source is retrieved. If the record count is greater than the maximum number of records you’ve defined, the ScrollBars property of the subform is set to 2 (Vertical Only), otherwise 0 or None. Lastly, in case there are any controls that exist under the subform, another function is called which repositions those controls based on the calculated height of the subform.

I hope some of you find this technique useful.