ACCESS FEED: Re: [MS_AccessPros] Using subforms that are split forms

Rod,

As an alternative, you could also experiment with my sample db for simulated split form. It offers plug & play facility in the sense that the developer can use any form of his choice as the single form portion of simulated split form, without having to worry about designing the datasheet portion.

All that needs to be done is to assign the given single form as source object for top subform control. Everything else, including generation of matching datasheet portion as well as proper synchronization between the two parts (single form and datasheet) gets taken care of automatically via the wrapper class. Single form functions as prime mover for the overall set up. Skeleton code in main form's module for instantiating the wrapper class, is covered under "Wrapper Class Usage" in explanatory notes below.

The sample named Form_SplitFormSimulated (Access 2000 file format) has been placed at Rogers Access Library. Link:
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=45

For ready reference, a brief description is placed below.

Best wishes,
A.D. Tejpal
------------

Form_SplitFormSimulated (Sample Db)
Brief Description
===========================
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. 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.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. Single form is the prime mover.
(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, 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.

References: DAO 3.6
========================

----- Original Message -----
From: John Viescas
To: MS_Access_Professionals@yahoogroups.com
Sent: Monday, August 27, 2012 10:26
Subject: RE: [MS_AccessPros] Using subforms that are split forms - revisit

Rod-

I'm having a hard time picturing what you've done. Even when you put a Subform
control on a Tab control, the "Parent" of the form inside the Subform control is
still the outer form. Is the "split" form (form + subform) now in a subform on
a tab in some other form? Should still work fine. Which exact statement is
getting the error, and what is the complete error text?

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of rodbevill
Sent: Monday, August 27, 2012 5:29 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Using subforms that are split forms - revisit

I have developed a split form using the manual approach described in an earlier
post. The main form is defined as a single form then a second copy is created
using the datasheet view as the default. This datasheet view is added to the
single form to create the split form. The link fields are blank and the
following code is use in the current method of the datasheet view:

Private Sub Form_Current()
' If on a new Record,
If Me.NewRecord Then
' Move the outer form to a new record
Me.Parent.SetFocus
RunCommand acCmdRecordsGoToNew
Else
' Sync the outer form by copying the Bookmark
Me.Parent.Bookmark = Me.Bookmark
End If
End Sub

This process appears to work fine with the form is opened. However, when I add
the split form to tab control on the main application form I get an error 2449
invalid method. I thought the datasheet view would use the other have the split
form as the parent. Is this a wrong assumption? If so what would I call the
other half of the split form once it is added to the Main application?
Currently no data has been entered into the system, just trying to get the
initial system to accept data with new data by user.

Rod

[Non-text portions of this message have been removed]