Example Apps for Businesses, Schools & Developers

Page 1 Page 2

Version 1.9           Approx 1.9 MB (zipped)                 First Published 27 Feb 2024


Section Links (this page):
          Introduction
          Download
          Using the Example App
          How Does the Example App Work?
          Version History
          More Continuous Forms Examples
          Feedback


Introduction                                                                                                                  Return To Top

This is the first part of the tenth article in my series showing how functionality can be added to continuous forms.

In this article, I will demonstrate how users can move, resize, hide and restore columns in a continuous form at runtime.
Once again, no APIs are used for the basic functionality of this application.

APIs are used as part of the optional dim background effect used with subsidiary forms frmAppTips & frmSettings.
No additional references are required if late binding is used.



Download                                                                                                                      Return To Top

Click to download:   MoveResizeColumns_v1.9    ACCDB file     Approx 1.9 MB (zipped)


Using the Example App                                                                                               Return To Top

Once again, the app opens to a startup form:

StartForm
Click Continue to open the main (continuous) form.

MainForm1
This is the main form in design view

Design View
The form includes several features already described in detail in earlier articles in the series. I won't explain these again here.
For a detailed explanation and code for those items, see the linked articles:

a)   Highlight Current Record

b)   Highlight Filtered Columns

c)   Highlight Sorted Columns

d)   Hide & Restore Selected Columns

e)   Multiselect Filter

f)   Get Control Positions & Sizes Using Accessibility Code

g)   Dim Background

This application adds the ability to move and resize selected columns at runtime

In order to move or resize a column, it must first be selected. To do so, either select the column in the listbox or double click anywhere in the column.
The selected column border is then highlighted in CYAN and the 4 small buttons next to the listbox become enabled.

ColumnSelected
To move the selected column to the left or right, click the green left/right arrow buttons once or several times. The column border highlighting changes to GREEN.

ColumnMoved
After a short delay, the highlighting is automatically removed.

To make the selected column wider or narrower, click the red +/- buttons once or several times. The column border highlighting changes to RED.

ColumnWidthChanged
Once again, after a short delay, the highlighting is automatically removed.

In conjunction with double clicking any column header to hide it and filtering / sorting, the layout of the form can be significantly altered to suit personal preferences.

ColumnHideSortFilter
To restore an individual hidden column, select it in the Show Hidden Columns combobox.
Alternatively click the Reset All Columns button to make all columns visible again in their previous position.
Similarly click the Clear Filters and/or Remove Sorts buttons as necessary.

The form can be scrolled both horizontally & vertically as for any continuous form. If a different record is selected, the record highlighting moves automatically.

ScrollSelect
In this version, the modified layout cannot be saved for future use. When you click the Close button, you may see this message (depending on your settings):

LayoutNotSaved
When the form is opened again, it will automatically revert to the original form layout.

MainForm1
Click the BLUE '?' button on the left to view a list of application tips for the various features in this application.
The Application Tips form is highlighted by dimming the background.

AppTips
Use the combobox to search for a specific application tip.
When the application tips form is closed, the background appearance is automatically restored.

Click the 'Tools' button on the right to view / edit various application settings. Once again the background is dimmed

AppTips
The various settings should be fairly self-explanatory:

a)   Hide Start Form - set to Yes to open the app directly to the main form

b)   Show Random Application Tips - set to Yes to view the tips in a randomly selected order ; set to No to use the default order

c)   Show Layout Warnings On Close - set to No to prevent the warnings appearing in future

d)   Reset Border Style Delay Time - the value here is the time in milliseconds before the border highlighting is removed.
      The allowed range is 500 to 2500 with the default value = 1500 (1.5 seconds)


How Does the Example App Work?                                                                           Return To Top

When a control is selected, either by double clicking or clicking on it in the listbox, this runs a procedure SelectControlInListbox.

This calls another procedure HighlightSelectedControl which creates the CYAN border around the control and label.
It also enables the 4 buttons used to reposition or resize the control.

Function SelectControlInListbox(frm As Form)
'Colin Riddington

      'use to select control name in listbox when double clicking a control
      frm.lstOrderColumns = GetLabelNameFromControl(frm.ActiveControl.Name)

      HighlightSelectedControl frm

End Function

'---------------------------------------------------------------

Function HighlightSelectedControl(frm As Form)
'Colin Riddington
'used to highlight selected control after double clicking

      'clear existing border formatting
      For Each ctl In frm.Controls
            If ctl.ControlType = acLabel Or ctl.ControlType = acTextBox Then ctl.BorderStyle = 0
      Next

      'get label & control name
      labelName = frm.lstOrderColumns.Column(0)
      controlName = GetControlNameFromLabel(labelName)

      'set solid cyan border to control & label
      frm(labelName).BorderStyle = 1 'solid
      frm(labelName).BorderColor = vbCyan
      frm(controlName).BorderStyle = 1 'solid
      frm(controlName).BorderColor = vbCyan

      'enable column control buttons
      frm.cmdLeft.Enabled = True
      frm.cmdRight.Enabled = True
      frm.cmdWiden.Enabled = True
      frm.cmdNarrow.Enabled = True

End Function


When the left arrow button, cmdLeft, is clicked, it runs the following procedure:

Private Sub cmdLeft_Click()
'Colin Riddington

     'move selected column one position to left (move up in listbox)
     MoveColumnLeft Me

     'reset position of all other controls
     ShowControlsInOrder Me

     'enable reset button
      cmdReset.Enabled = True

End Sub


The MoveColumnLeft function updates the control position to be one place higher in the listbox, changes the border colour to GREEN and updates the tab order.

Function MoveColumnLeft(frm As Form)
'Colin Riddington

Dim position As Long
Dim AddText As String

      If IsNull(frm.lstOrderColumns.Column(1)) Then Exit Function
      position = frm.lstOrderColumns.ListIndex
      If position <= 0 Then Exit Function

      AddText = frm.lstOrderColumns.Column(0) & ";" & frm.lstOrderColumns.Column(1)
      frm.lstOrderColumns.RemoveItem position
      frm.lstOrderColumns.AddItem AddText, position - 1

      labelName = frm!lstOrderColumns.Column(0)
      controlName = GetControlNameFromLabel(labelName)

      'set solid green border to control & label
      frm(labelName).BorderStyle = 1 'solid
      frm(labelName).BorderColor = vbGreen
      frm(controlName).BorderStyle = 1 'solid
      frm(controlName).BorderColor = vbGreen

      'update tab order
      frm(controlName).TabIndex = frm(controlName).TabIndex - 1

End Function


The ShowControlsInOrder function loops through each control and label in turn and repositions them in the order specified in the listbox.
It also disables the border highlighting after a short delay as specified in the app settings.

Function ShowControlsInOrder(frm As Form)
'Xevi Batlle / Colin Riddington

On Error Resume Next

Dim I As Integer
Dim leftPosition As Long

      leftPosition = 0
      For I = 0 To frm.lstOrderColumns.ListCount - 1
            labelName = frm.lstOrderColumns.ItemData(I)
            controlName = Left(labelName, Len(labelName) - 6)
            frm(labelName).Left = leftPosition
            frm(controlName).Left = leftPosition
            leftPosition = leftPosition + frm(controlName).Width
      Next I

     'run form timer event after specified delay - default = 1.5 seconds
     'this event resets label & control border styles = transparent
     frm.TimerInterval = GetResetBorderStyleDelayTime

End Function


The Reset button is also enabled to allow the change to be easily reversed if required.

Clicking the right arrow button, cmdRight, runs very similar code with the only difference being the MoveColumnRight function:

Private Sub cmdRight_Click()
'Colin Riddington

      'move selected column one position to right (move down in listbox)
      MoveColumnRight Me

      'reset position of all other controls
      ShowControlsInOrder Me

      'enable reset button
      cmdReset.Enabled = True

End Sub


The MoveColumnRight function updates the control position to be one place lower in the listbox, changes the border colour to GREEN and updates the tab order.

Function MoveColumnRight(frm As Form)
'Colin Riddington

Dim position As Long
Dim AddText As String
Dim strName As String

      If IsNull(frm.lstOrderColumns.Column(1)) Then Exit Function
      position = frm.lstOrderColumns.ListIndex
      If position >= frm.lstOrderColumns.ListCount - 1 Then Exit Function

      AddText = frm.lstOrderColumns.Column(0) & ";" & frm.lstOrderColumns.Column(1)
      frm.lstOrderColumns.RemoveItem position
      frm.lstOrderColumns.AddItem AddText, position + 1

      labelName = frm!lstOrderColumns.Column(0)
      controlName = GetControlNameFromLabel(labelName)

      'set solid green border to control & label
      frm(labelName).BorderStyle = 1 'solid
      frm(labelName).BorderColor = vbGreen

      frm(controlName).BorderStyle = 1 'solid
      frm(controlName).BorderColor = vbGreen

      'update tab order
      frm(controlName).TabIndex = frm(controlName).TabIndex + 1
      strName = frm.Name

End Function


Clicking the cmdWiden button (RED +) again runs very similar code:

Private Sub cmdWiden_Click()
'Colin Riddington

      If IsNull(Me!lstOrderColumns.Column(1)) Then Exit Sub

      'make selected control & label 100 twips wider
      WidenSelectedColumn Me

      'reset position of all other controls
      ShowControlsInOrder Me

      'enable reset button
      cmdReset.Enabled = True

End Sub


The WidenSelectedColumn function changes the border style to RED and increases the width of both control and label by 100 twips (approx 0.18 cm or 0.07 inch).
It then loops through all controls to the right of the selected control and moves those by the same amount to the right.
Code is used to handle error 2100 in the case where the form reaches the maximum width allowed in Access.

Function WidenSelectedColumn(frm As Form)
'Colin Riddington

On Error GoTo Err_Handler

      Dim lngOldWidth As Long

      labelName = frm!lstOrderColumns.Column(0)
      controlName = GetControlNameFromLabel(labelName)

      If Nz(controlName, "") <> "" Then
            lngOldWidth = frm(labelName).Width

            'set solid red border to control & label
            frm(labelName).BorderStyle = 1
            frm(labelName).BorderColor = vbRed
            frm(controlName).BorderStyle = 1
            frm(controlName).BorderColor = vbRed

           'increase widths by 100 twips
            frm(labelName).Width = lngOldWidth + 100 'label
            frm(controlName).Width = lngOldWidth + 100 'corresponding control
      End If

      'move all controls to right of selected control by 100 twips to the right
      For Each ctl In frm.Controls
            If (ctl.ControlType = acLabel Or ctl.ControlType = acTextBox) And ctl.Tag <> "A" Then
                  If ctl.Left > frm(labelName).Left Then
                        ctl.Left = ctl.Left + 100
                  End If
            End If
      Next

Exit_Handler:
      Exit Function

Err_Handler:
      If Err = 2100 Then
            MsgBox "The form cannot be made any wider", vbInformation, "Maximum width"
      Else
            MsgBox "Error " & Err & " in WidenSelectedColumn procedure: " & Err.Description
      End If

      Resume Exit_Handler

End Function


Similarly clicking the cmdNarrow button (RED -) runs almost identical code.

Private Sub cmdNarrow_Click()
'Colin Riddington

      If IsNull(Me!lstOrderColumns.Column(1)) Then Exit Sub

      'make selected control & label 100 twips narrower
      NarrowSelectedColumn Me

      'reset position of all other controls
      ShowControlsInOrder Me

      'enable reset button
      cmdReset.Enabled = True

End Sub


The NarrowSelectedColumn function changes the border style to RED and reduces the width of both control and label by 100 twips (approx 0.18 cm or 0.07 inch).
It then loops through all controls to the right of the selected control and moves those by the same amount to the left.
Code is used to handle error 2100 in the case where the control cannot be made any narrower.

Function NarrowSelectedColumn(frm As Form)
'Colin Riddington

On Error GoTo Err_Handler

      Dim lngOldWidth As Long

      labelName = frm!lstOrderColumns.Column(0)
      controlName = GetControlNameFromLabel(labelName)

      If Nz(controlName, "") <> "" Then
            lngOldWidth = frm(labelName).Width

            'set solid red border to control & label
            frm(labelName).BorderStyle = 1
            frm(labelName).BorderColor = vbRed
            frm(controlName).BorderStyle = 1
            frm(controlName).BorderColor = vbRed

            'reduce widths by 100 twips
            frm(labelName).Width = lngOldWidth - 100       'label
            frm(controlName).Width = lngOldWidth - 100       'corresponding control
      End If

      'move all controls to right of selected control by 100 twips to the left
      For Each ctl In frm.Controls
            If (ctl.ControlType = acLabel Or ctl.ControlType = acTextBox) And ctl.Tag <> "A" Then
                  If ctl.Left > frm(labelName).Left Then
                        ctl.Left = ctl.Left - 100
                  End If
            End If
      Next

Exit_Handler:
      Exit Function

Err_Handler:
      If Err = 2100 Then
            MsgBox "The control cannot be made any smaller", vbInformation, "Minimum width"
      Else
            MsgBox "Error " & Err & " in NarrowSelectedColumn procedure: " & Err.Description
      End If
      Resume Exit_Handler

End Function


All the other code used in the form is described in the related articles linked above.

A reminder that, in this version, the layout is not saved when the form is closed.
However that functionality is provided in the updated version available with the second part of this article.



Version History                                                                                                            Return To Top

v1.9     27/02/2024       (Page 1 of this series of articles)
Initial release. Layout not saved when the form is closed

v2.1     28/02/2024       (Page 2 of this series of articles)
Added functionality to optionally save the modified layout for future use



More Continuous Form Examples                                                                              Return To Top

The following articles provide further examples of functionality added to continuous forms:

•   Highlight Current Record
•   Highlight Selected Control or Column
•   Highlight Filtered Columns
•   Add Multiselect Filter   (2 pages)
•   Paint Me Transparent       (Selectively Hide Controls)
•   Sort columns
•   Hide duplicate values in a column   (as for reports)
•   Hide & Restore Selected Columns   (2 pages)
•   Freeze Columns   (3 pages)
•   Move and Resize Columns   (2 pages)



Feedback                                                                                                                     Return To Top

Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.

Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington           Mendip Data Systems                 Last Updated 27 Feb 2024



Return to Example Databases Page Page 1 of 2 1 2 Return To Top