[AccessD] Child records entered before parent

OK, so now how about where subforms require data filled in on other
subforms, or just elsewhere in the system?

I am building a system where a contractor has to fill out documents about
the systems (s)he works on.  The first level is that the document and system
tables have to be filled in.  Specific systems require specific documents so
there is a m-m tblSystemDocuments which is filled in.

Then a contractor table is filled in (or at least one or more contractors
entered).  
Next a m-m tblContractorSystem table to relate contractors to the systems
they worked on.
And finally, a m-m tblContractorDocuments to track which documents have been
sent to which contractors, and the status of those documents.

Obviously you cannot fill in any ContractorSystem records unless there is a
valid contractor (the previous email) but less obviously, you can't (or
perhaps shouldn't is more correct) fill in any ContractorDocument records
until ContractorSystem records are filled in.  ContractorDocuments and
ContractorSystems are both child to contractor.

The combo that allows the selection of documents is filtered on the
contractor side of ContractorSystem (since the subform and enclosed combo is
child to the contractor form), but more specifically the cboDocument is
filtered on the systems that are assigned to the contractor.  So by knowing
what contractor is selected in the contractor form, we can determine which
systems are assigned to that contractor, and from that determine which
documents are assigned to those systems, and therefore which documents to
display in the cboDocument.

So...

The entire subform(s) ContractorSystem and ContractorDocument are disabled
when you are on the new record (the strategy I also use), but as soon as you
are on a valid contractor record, that strategy (if used alone) enables both
the ContractorSystem subform (correct) and the ContractorDocument subform
(not correct unless a ContractorSystem has been entered).  Of course the
combo in ContractorDocument that allows the user to select a document is
filtered down to only show documents valid for the systems assigned to that
contractor, but this still leaves the user able to enter other data in that
record (a comment for example) when in fact (s)he can never complete the
record by selecting a document from the combo - because the combo doesn't
display any documents because no systems have been selected yet.

And finally, how do you notify the user that these "rules" are in effect?

As you might have guessed I use my framework for implementing the rules, at
least where I need consistency in the application of rules such as this
"child form unlocking".  

The framework has a dclsFrm which is a class for form stuff.  dclsFrm has a
control scanner that scans for every control and loads a class instance for
each (or most) controls, text classes, combo classes and (of importance
here...) subform control classes.  Subforms have special processing required
such as this stuff, so I have a collection in dclsFrm that holds a pointer
to each dclsCtlSFrm (the subform control class).  So when the scanner is
finished, there is a class instance loaded for each subform control, and a
pointer to each of those class instances are in a collection specifically
for these subform control class instances.

Now... When dclsFrm's OnCurrent and AfterUpdate sinks fire, I iterate the
collection setting or clearing the subform control locked property (start at
the bottom of the code, specifically the new record check):

Sub LockSubFrmCtls(lblnLocked As Boolean)
Dim ldclsCtlSFrm As dclsCtlSFrm
    For Each ldclsCtlSFrm In mcolSubForms
        ldclsCtlSFrm.pLocked = lblnLocked
    Next ldclsCtlSFrm
End Sub
Sub NewRecordCheck()
    If mfrm.NewRecord Then
         LockSubFrmCtls True
    Else
         LockSubFrmCtls False
    End If
End Sub
Private Sub mFrm_Current()
    On Error Resume Next
    mdclsCtlCboRecSel.FrmSyncRecSel
    NewRecordCheck
End Sub
Private Sub mfrm_AfterUpdate()
    On Error Resume Next
    mdclsCtlCboRecSel.FrmSyncRecSel
    NewRecordCheck
End Sub

This USUALLY correctly locks or unlocks the subforms, based on whether we
are on the new record or not.  The afterUpdate is required because if a new
record is entered, OnCurrent does not fire so we have to also check and
unlock the subforms when a new record is created and we "click into" the
subform, saving the parent record.  BTW, I use the subform control's Locked
property instead of Enabled property because the subform can be clicked into
allowing the main form to save it's record, whereas if the Enabled is used,
the subform cannot even be clicked into and the record would have to be
saved using a save button.

However in this specific case my code also unlock the ContractorDocument
subform, even if there are no documents that can be selected because there
have not yet been any Systems assigned to the contractor.

Almost as bad though, there is nothing that tells the user that a subform is
locked except that (s)he cannot create a record, and in the case of the
combo that can't display any documents because no ContractorSystems have
been entered yet.  Wouldn't it be nice if a message box popped up if the
user clicks into a locked subform and then tries to enter data, telling the
user that "this subform is locked because..."?

To this point, this has fallen into the "user training" pile.  I.e. the user
just "has to be told" that things have to be entered in a certain order
(which is always the case of course) and if they can't figure out why a
combo doesn't display anything... Well... the training must be lacking...

I am not entirely comfortable with "the training must be lacking" though.
Thus the questions re "how do you guys handle this stuff".  

And of course some of us would ask if this is "over engineering" the
application?  Given the presence of the framework, and the ease with which
functionality such as this is added, I personally do not think so but I am
equally sure others do.

John W. Colby
www.ColbyConsulting.com 

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/