Making Access Forms More Screen Responsive

I am unfortunately one those developers with a policy of “If it ain’t broke don’t fix it.”  Sometimes that’s a good thing but other times that can be a limiter on personal growth.

I mean this in the context of application design.  I have developed a standard look and feel for all my applications. This has been years (decades ?) in the making.  A Dick Moffat application looks like a Dick Moffat application.  It also allows me to be more efficient as I focus on the business process I am trying to automate and not the UI.  Also it means that my clients’ users tend to have little trouble understanding how to use any changes or new apps I do for them.  It also costs less because I don’t spend their money (or mine) on playing round with the UI,

One of my BIG obsessions has been with using modal, unmovable, unsizable, dialog-style forms.  This means my apps are designed to the lowest common denominator in the client Windows resolutions (lately it’s been pretty much 1280 x 800).  This means that if you have a higher resolution you simply get a floating window in the middle of your screen with a bunch of gray around it.  Works, but not very “Web-like” I’m afraid.

But one of my clients finally complained about the limits this makes on my solution for them, and he is right.

So I have reworked my standard fixed-size interface to allow for maximization and resizing and to allow for use of higher resolutions and getting more info on screen at one time.  The secret has been the use of the “Horizontal Anchor and Vertical Anchor” properties of objects on the form and the OnResize Event of the Form.

Rather than spending a lot of time explaining how I used them in detail, I’m going to assume you can look them up yourselves and play around with them (assuming you all don’t already use these properties all the time and I am just a dinosaur).

But the core concept is that by using these properties you can make an object on a form (such as a Sub-Form or a Text box) automatically expand vertically or horizontally to fill the space provided by a re-sized form (either larger or smaller).   It also means you can tag an object (like a Tab Control) to the right-side of a Form, while allowing a Text box to its left to expand to fill the space available.

Conversely, you can make a list box expand or contract vertically to fill the space available on the re-sized form.

Play with it.

I have found this, when combined with some kludgy code to redefine the column-widths in list boxes (see below – hopefully someone has a better solution for this), allows me to create wonderful flexible Forms that actually take advantage of the screen space available rather than fighting it in the interest of conformity.

I am somewhat less of a dinosaur now, I guess.


p.s. Here ya go:

Private Sub Form_Resize()

fmWidth = Me.lstProblemStatements.Width / Me.Width
fwidth = Me.WindowWidth * fmWidth
Me.lstProblemStatements.ColumnWidths = “0” & “””” & “,” & fwidth * 0.75 / 1440 & “””” & “,1” & “””” & “,1” & “”””

fmWidth = Me.lstAssigned.Width / Me.Width
fwidth = Me.WindowWidth * fmWidth
Me.lstAssigned.ColumnWidths = “0” & “””” & “,” & fwidth * 0.75 / 1440 & “””” & “,1” & “””” & “,1” & “”””

End Sub

The idea of this code is that I take the percentage of the default width of the form that the List Box I’m trying to fix is and then calculating the width (in inches) that I need to give each column in the list box a relative size in every resolution.  This isn’t perfect but it’s “close enough for jazz” to look good enough for me :-).   Dick

About Biggus Dickus

Dick is a consultant in London, ON Canada who specializes in Microsoft Excel and Microsoft Office Development.
This entry was posted in Uncategorized and tagged , , , , , , . Bookmark the permalink.

1 Response to Making Access Forms More Screen Responsive

  1. Claire says:

    I agree, UI is secondary to functionality–unless the lack of the former impinges on the latter. Congrats on the shake-up!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s