[AccessD] Child records entered before parent

I might as well jump into this now, although I have been attempting to
restrain myself. What JC is getting at, IMO, is the disconnect between how
humans see a system and how developers/database designers see it. This is
increasingly true as the humans tend to think of paper forms. The cool thing
about paper forms is that you can fill in the boxes in any order you want.
(We all know about the down side, so let's not waste time discussing that.)
Faced with this sort of situation, I tend to go for a completely
un-normalized table that grabs all the data for master and child and
grandchild if necessary (customer info, order info, order detail info, and
so on). The user is free to enter the data in any desired order (details
first, customer second, order info last -- whatever). I don't care. I'll
have a validation routine on the form to be sure, but as for the user
hopping around in an unpredictable manner, I don't care. I will give them
some help such as a combo-box with a list of customers and a not-in-list
routine that exposes some additional controls if necessary, but I have no
problem in terms of design in providing such an un-normalized form. 
The big thing is that this form talks to a temp table whose rows are tossed
as soon as the user saves the record. At that point code kicks in and writes
the various fields to their appropriate normalized destinations.
This is never my first design choice, to be sure. But more than a few times
it has proved appropriate for certain clients. The normalized pattern tends
to create a UI where you first must obtain the customer info, adding the
customer if necessary, then create the order, then create the order details.
It may be, in a given business, that the order detail stuff is most
important. (Think of selling tickets to a concert, or any other product
where quantity is strictly limited, and sold on a first-come-first-serve
basis.) You call me and say, "I want two tickets to the Stones in NYC." It
is much more important to immediately reserve two tickets, removing them
(temporarily) from inventory, than it is to obtain your name. Once I have
reserved the tickets, then I can create the order and finally (and I might
add, most gracefully) ask the customer her name and her credit card info.
Imagine this transaction going the other way, in rude terms:
"I want two Stones tickets."
"Who the hell are you? Name, address, birthdate, credit card info?"
Back and forth for a while... and then the last remaining tickets have been
sold to somebody else. 
Nobody in their right mind, IMO, would be happy with a design like this. My
un-normalized table approach may not be the best solution, but it does allow
me to ask for the data in the reverse order, most important (order details)
first, and then work back up the chain. IMO, it is simply graceful to secure
the product first and only then ask for the customer info, once I am
confident that I do in fact have two Stones tickets for you.
Arthur

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John W. Colby
Sent: September 9, 2005 3:32 PM
To: 'Access Developers discussion and problem solving'
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 

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


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com