VBA script for auto resizing of controls/subforms in MS Access 2003 forms?

Question

Since Access 2003 doesn't have the control anchoring functionality as exists in 2007, I was wondering if anyone has or is aware of some VBA script, or a freeware control, that can give this functionality?

Answer 1

I know of no exact duplication of the 2007 functionality in 2003. There are multiple components for resolution independence (resizing the controls on a form based on the users monitor resolution) and for resizing with the form resize event (such as http://www.fmsinc.com/products/components/ControlTour/resize.htm). None that I'm aware of quite replicate the 2007 experience, but a similar question (and code to handle it) can be found here: http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23662850.html

Personally, I just handled the resize event myself. The easiest way is to do so is to create the form in the minimum size you wish to support, and then record the base positions and widths (either in a table or as form scoped constants). From there you can resize using:

resizeRatio = currentFormWidth / baseFormWidth

control.left = baseLeft * resizeRatio control.width = baseWidth * resizeRatio

The advantage to doing this yourself is that over time you evolve it, with things such as keeping the labels on the left side the same width but expanding the fields to the right (this is done by not resizing the labels at all, and subtracting the end of the labels area off from the width of the form before applying the position and width changes, such as):

resizeRatio = (currentFormWidth - labelsAreaWidth) / (baseFormWidth - labelsAreaWidth)

control.left = (baseLeft - labelsAreaWidth) * resizeRatio + labelsAreaWidth control.width = baseWidth * resizeRatio