Shifting fields left on a continuous form in Microsoft Access
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.)
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
ctl.Left - ShiftLeftTwips
On Error GoTo 0
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in
procedure ShiftFieldsLeft of VBA Document Form_EquipmentQuickFind"
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.
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
distribute new and updated Front End databases to your users with several mouse
Granite Fleet Manager
- the best designed fleet maintenance tracking and
management system available