[AccessD] Child records entered before parent

    Let SF_01 be the name of control on main form, acting as  container for the subform in question. Code in its Enter event, as given below, will ensure that if the user tries to go into it while it happens to be in locked state, there will be a helpful message followed by transfer of focus to desired control on the main form.

A.D.Tejpal
--------------

Code in Enter event of SubFormContainerControl
==================================
Private Sub SF_01_Enter()
    If SF_01.Locked = True Then
        MsgBox "Please Make Entry In ... First"
        ControlOnParentForm.SetFocus
    End If
End Sub
==================================

  ----- Original Message ----- 
  From: John W. Colby 
  To: 'Access Developers discussion and problem solving' 
  Sent: Saturday, September 10, 2005 01:01
  Subject: Re: [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