By AD Tejpal
This sample db demonstrates simulated multiple split forms on tab control pages. The split form has two adjacent subforms, one below the other, separated by a divider bar. Top subform serves as single form while the bottom one represents datasheet portion. There is two way synchronization between single form and datasheet. Comparative vertical space available for the two subforms can be adjusted at run time by dragging the divider bar up or down. Single form portion functions as the prime mover.
Some of the salient features are listed below:
1 - Ease of adaptation:
The developer wishing to use this sample db for his own needs has to simply assign his single form as source object for the top subform in the main form included here. Everything else will get taken care of automatically. Based upon generic template, datasheet portion of split form gets generated programmatically in the bottom subform, displaying columns matching bound controls in the single form above. It is like a plug & play feature.
2 - Consolidation of code in a wrapper class:
Necessary code for integrated coordination between main form as well as its two subforms is consolidated in a wrapper class, instantiated in open event of main form. This class has pointers to both the subforms as well as the main form.
3 - No added burden for data loading:
Datasheet subform uses the recordset already loaded for single form, thus avoiding any additional burden.
4 - Divider bar:
(a) Divider bar can be dragged for dynamic expansion / shrinkage of datasheet and single form heights at run time.
(b) On opening the split form, divider bar assumes the position last held in previous session.
5 - Re-sizing of nominated controls on single form:
For added convenience, certain controls on single form, e.g. text box bound to memo field or even an image control, can be slated for vertical re-sizing so as to best utilize the available space resulting from divider bar movement. Tag property of such controls should include the word ReSize. In the sample db, such a behavior is demonstrated for control named Synopsis, bound to memo field.
6 - Hiding / Un-hiding of datasheet columns:
On opening, datasheet columns matching memo fields are in hidden state. The user can hide any other column by double clicking the same. Similarly, any column can be un-hidden by double clicking the matching bound control on single form.
7 - Auto adjustment of datasheet column widths:
(a) At any given stage, the width of displayed columns gets adjusted automatically, so as to ensure optimum utilization of available width of datasheet window, duly taking into account the latest status of hidden / un-hidden columns.
(b) For a given single form, on opening for the very first time, datasheet columns assume equal width, suiting available space. Thereafter, if the user has manually adjusted the column widths, any further automated adjustment of column widths in response to hiding / un-hiding of columns is carried out in such a manner as to retain relative proportion of column widths.
(c) On click of a command button, the user has the option to reset all column widths equally distributed, as if it were the first opening of form.
8 - Highlights:
(a) When a datasheet column is in hidden state, corresponding control in single form gets highlighted in light grey. As and when the column is un-hidden, matching control on single form reverts to its normal color.
(b) Current row in datasheet gets highlighted in light green.
(c) As the user tabs from one control to the other on single form, matching cell on current row of datasheet gets highlighted in distinct color (say light maroon).
Note: Flicker effect on datasheet, due to conditional formatting, seems to be more pronounced in Access 2010 as compared to Access 2003.
9 - Search Action - Positioning of destination row in datasheet window:
Based upon search action performed via suitable controls (like combo box etc) on the single form, the destination row on datasheet gets positioned at middle of display window. This takes into account dynamic height of datasheet window, resulting from movement of divider bar. In Access 2010, use of search box on navigation bar, too, results in similar positioning of destination row at middle of datasheet window.
Wrapper Class Usage:
Integrated wrapper class C_SplitForm is used in VBA module of main form F_Main as follows:
(1) Following statement is in F_Main's declarations section:
Private mfm As C_SplitForm
(2) Following code is in F_Main's open event:
Set mfm = New C_SplitForm
mfm.P_Init Me, Me.SF_A, Me.SF_B, Me.LbDiv, "T_ZZZRef"
(a) Subform control at top, i.e. SF_A holds the single form while SF_B holds the datasheet form. Single form is the prime mover.
(b) Reference table named T_ZZZRef holds divider bar position and columns widths from last session.
(c) Label LbDiv serves as the divider bar. It can be dragged up & down as desired, for adjusting relative heights of single form & datasheet portions at run time.
(d) Setting of form object to Nothing is carried out in close event of mfmMain in wrapper class C_SplitForm, so as to ensure proper termination of the class.
(Care should be exercised Not To set mfm to Nothing in real form's module, for example in its close event, as that would interfere with smooth termination of wrapper class. - Also see remarks in close event of mfmMain in this class).
(e) Class C_SplitFormControls is a child class of wrapper class C_SplitForm.
(f) Generic datasheet form used in bottom subform control (SF_B) has no code in its VBA module. It has 200 unbound text boxes along with corresponding attached labels so as to cater up to 200 columns.
Version: Access 2000 file format.
You can find the sample here: http://rogersaccesslibrary.com/forum/Form-splitFormsimulatedmultiple_topic605.html