I have a main form with a one-to-many subform appearing on it. I have the scrollbars property set to Vertical on the Subform.
If there are no entries in the Subform, there are no scrollbars. I would like to set the scrollbars to appear all the time (to keep the interface more consistent - if there are no entries in the Datasheet, there is a small 1/4" space on the right side of the form where the scrollbars would be...)
What you could do, (if your SubForm is in DataSheet view only) I suppose, is to give the effect that there is no scroll bar when it's not needed. By this I mean, suppose you set the far right column to the limit of the SubForm frame (you know...slide it wider with the mouse), then when a record is added to the SubForm the far right column shrinks to accomodate the scroll bar that appears. If you delete a record or records to the point where the scroll bar is no longer needed then the far right Column automatically expands to fill the empty space.
Here is some code (a function) to accomplish just that. The SubForm in question must be in Datasheet view on your form. There is a little setup required. Although slight, it must be done.
In your SubForm (not the control on your main form), open the code module for the SubForm and in the declarations section of the module (the top) enter the line:
Dim OrigCtrlWidth As Long
You need to declare this variable so that it is available to the entire module.
Now Copy and paste the following function into the code module for your SubForm:
Private Sub SetEndControl(Ctrl As Control)
' For DataSheet View ONLy!
Dim ScrollBarWidth As Long
Dim WidthOfActualSubForm As Long
Dim WidthOfSubFormControl As Long
' Set Variables
WidthOfActualSubForm = Me.InsideWidth
WidthOfSubFormControl = (Forms!yourMainFormName!theSubformControlNameOnMainForm.Width - 15)
ScrollBarWidth = WidthOfSubFormControl - WidthOfActualSubForm
' If ScrollBarWidth is greater than 0 then there is a
' scrollbar visible in SubForm.
If ScrollBarWidth > 0 Then
Ctrl.ColumnWidth = OrigCtrlWidth - ScrollBarWidth
Ctrl.ColumnWidth = OrigCtrlWidth
Be sure to enter the proper names for yourMainFormName and theSubformControlNameOnMainForm otherwise this won't work.
Now place the following line of code into the OnOpen event of your SubForm: