A.D. Tejpal
adtp at airtelmail.in
Mon Aug 13 03:17:54 CDT 2012
J.C.,
Further to my previous post, sample db named Form_SplitFormSimulated,
incorporating the concepts discussed so far under this thread, is now ready
and has been placed at Rogers Access Library. It is in access 2000 file
format and can be downloaded at the following Link:
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=45
A copy of explanatory notes accompanying the sample db is placed below,
for ready reference. Interested members of this forum might like to test it
and advise whether anything needs further attention.
Best wishes,
A.D. Tejpal
------------
Form_SplitFormSimulated (Sample Db)
Explanatory Notes:
===========================
This sample db demonstrates a simulated split form. It 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.
Some of the salient features are listed below:
1 - Ease of adaptation:
The developer wishing to make an adaptation of this sample db has to
simply assign his single form as source object for the top subform. Based
upon generic template, datasheet portion of split form gets generated
automatically in the bottom subform, displaying columns matching bound
controls in the single form above.
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. Nominal code for raising certain custom events has been added
to existing code in single form's module.
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).
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.
Usage:
1 - Incorporation of a new single form (other than the one included in
this sample) in split form arrangement.
1.1 - Place following two statements in declarations section of VBA
module pertaining to the form planned to be used as single form.
Public Event EvFilter()
Public Event EvSearchDone(fm As Access.Form, AbsPos As Long)
1.2 - In single form's VBA module, insert the following statement after
each code block that affects form's filter state:
RaiseEvent EvFilter
1.3 - Similarly, insert the following statement after the code block
meant for performing search action:
RaiseEvent EvSearchDone(Me, Me.Recordset .AbsolutePosition)
1.4 - In declarations section of code module for class C_SplitForm,
modify the following statement so as to match the name of single form
actually used by you:
Private WithEvents msfmSF2 As Form_FSub_Single
(For example, if your form's name is MyForm, it will read
Private WithEvents msfmSF2 As Form_MyForm)
2 - Integrated wrapper class C_SplitForm is incorporated in the module
of main form F_Main as follows:
2.1 - Following statement is in F_Main's declarations section:
Private mfm As C_SplitForm
2.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.SF_Ref, Me.LbDiv
Note:
(a) Subform control at top, i.e. SF_A holds the single form while SF_B
holds the datasheet form.
(b) Hidden subform control named SF_Ref holds a single record subform
for storing the position of divider bar as well as name of single form - at
close of session.
(c) Label LbDiv serves as the divider bar. It can be dragged up & down
as desired.
(d) Setting of mfm 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.
References: DAO 3.6
A special note of thanks to John W. Colby (of Colby Consulting) for
kindly providing valuable insights and willingly sharing his vast expertise
in the field of classes.
==================================