Shifting fields left on a continuous form in Microsoft Access

(Updated 2008-10-01)

I have a field on a continuous form that is only occasionally visible.  Yet, when visible, I'd like it to be right next to another field.   Actually it's one of two fields. 

This is for a client using my Granite Fleet Manager who wanted the capability of changing all the Unit Numbers.  However he wanted to have the new unit number visible for a short period of time when working on the new unit numbers.  Then he wanted the new unit number to be placed into the current unit number field, which is present on many forms and reports.  However the old unit number is  still visible for months or maybe even a year while the folks get used to the new unit number.

So I added the column to the current Equipment Quick Find form.  And that looks decent.  Also note that with the exception of this particular field all the fields are locked so users have to click on the View button  navigate one form down before they can start changing things.  (What's that question mark you ask?  Simple forms based help.  More details later.)

But if I left a gap in there as the below illustrates it would look pretty ugly.

So I added an entry to the Tag property of each of the controls to the right. (Of course I selected all the controls by using the shift key and dragging and updated them all at the same time.  You didn't think I would update each one individually now do you?)

I quickly did up some VBA code looping through the form control collection.  (Note that I used the Instr function in case I ever wanted to add another value into the Tag field.  The SearchByOldOrNewUnitNbr field is the search field that I don't want displayed all the time. I double checked and it's the same width as the field on the detail section.   I call the following code in the forms Open event.)

Sub ShiftFieldsLeft()

    Dim ctl As Control, ShiftLeftTwips As Long

    On Error GoTo ShiftFieldsLeft_Error

    ShiftLeftTwips = Me.SearchByOldOrNewUnitNbr.Width

    For Each ctl In Me.Controls
        If InStr(ctl.Tag, "ShiftLeft") > 0 Then
            ctl.Left = ctl.Left - ShiftLeftTwips
        End If
    Next

       On Error GoTo 0
    Exit Sub

ShiftFieldsLeft_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ShiftFieldsLeft of VBA Document  Form_EquipmentQuickFind"

End Sub

And now, when a client isn't using that option, it looks like the following:

(Actually this page took a lot more time than the code itself took.  But then I've been working with Access collections for a number of years now.)

David W. Fenton, fellow and valued newsgroup denizen had the following comments in my blog entry announcing this page:

While it's not such a big deal for the situation you describe, I find it much more efficient when looping through controls to do things to them to define custom collections. That way, you loop through the whole controls collection only once each time you open the form instance, and each other traversal is of a much smaller collection specific to your needs. I found that this approach noticeably enhances performance.

Of course, that only really matters when you're altering controls often during a single form sessions, e.g., enabling/disabling groups of controls based on a record type. In that situation, it really does help a lot.

See Error looping through controls on form in Access 2003 Options for a discussion of his technique and Access 2000 - AllowEdits property - setting false disables change of button for the sample code.   Note that he is saving the control object in the collection as well as the control name.

[ Access Tips | Access | Main ]

Auto FE Updater   Auto FE Updater distribute new and updated Front End databases to your users with several mouse clicks.

Wrench and gear Granite Fleet Manager - the best designed fleet maintenance tracking and management system available

Comments email Tony  Search Contact Tony's Blog Privacy Policy Table of Contents

Website copyright © 1995-2013 Tony Toews