databasedev.co.uk - database solutions and downloads for microsoft access

Controlling when Scroll Bars are displayed on a SubForm:

On occasions, and to improve the look of your forms, you may only wish to display scrollbars in a subform if there are a certain amount of records contained. If for example in an Order Entry database, a Customer can have many Orders however there are going to be occasions where a single customer may only have one or two orders. In this case, we can hide the Vertical Scroll Bar as there is no need for it to be visible.

The following code example demonstrates how to use the OnCurrent property event procedure of the subform to display or hide the scroll bars:

Private Sub Form_Current()

   ' If the number of records in the subform
   ' is greater than 2, display the
   ' horizontal and vertical scrollbars.
   
   If Me.RecordsetClone.RecordCount > 2 Then
        Me.ScrollBars = 3
    Else
        Me.ScrollBars = 0
   End If

End Sub

Below shows an example of this in practice.

The first example contains the main form and the subform, which you can see does not contain the Vertical Scroll Bar due to the fact that there are less than 2 records.

Subform containing no vertical scrollbar due to insufficient records

The next example shows the subform containing the vertical scrollbars due to the amount of records contained:

Subform containing scrollbars due to the amount of records contained

As you can see, the visual impact of the form is far greater when not seeing the scrollbars present when they are not required.