[AccessD] Preventing childless parent records

Vacant nodding smiles is a very common problem. 

As for how to detect childless records, I do not believe there is any
solution without code.  The basic concept is:

In the parent form, do not allow a close of the form, or a move off of the
record without knowing that there are children.  This can be done two ways.
One, you can "ask" the subform how many records are in its recordset.  Every
(bound) form has a DAO recordset behind the form, called Recordset.  Thus 

me.Recordset.recordcount will return the number of records in the recordset.

Thus (from the parent)

	me!MyFormControl.form.recordset.count 

will return the number of records that the form in MyFormControl contains.  

If me!MyFormControl.form.recordset.count > 0 then cancel a form close, and
warn the user they need to enter child records or delete the parent record.

For moving off the record, it is a bit more complex since the only event
that fires when you move to a different record is the Current event and
there is no Cancel for that.  Click the new record and you will move.  For
this reason the only thing that comes to mind is to set a bookmark, allow
the move to happen, but in OnCurrent go lookup the children of the record
with that bookmark.

This is a very interesting discussion and the solution begs for a framework
solution (which I do NOT have, but will once we figure out how to do this).

John W. Colby
www.ColbyConsulting.com 


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mcgillivray, Don
[IT]
Sent: Monday, March 06, 2006 5:42 PM
To: AccessD
Subject: [AccessD] Preventing childless parent records

Hello, All

I have a data entry form for entering transactions where the main form holds
the transaction header and a subform holds the header's details.
Works as advertised, except that I have a user who has a habit of creating
headers without adding line items.  She sets up the header, tabs into the
line item section, gets distracted (I guess), returns to the form (after
dealing with the distraction) and starts over for some reason.  Now, by
virtue of having tabbed into the line item section, the header was saved,
but because no line items were ever entered, the header is an orphan, or,
more accurately, a childless parent.  Every week, I scan the data and find
several of these records that need to be removed.  

My first approach was to educate the user about how to do data entry.  
My second approach was to educate the user about how NOT to do data entry.

Both approaches netted me that vacant nodding smile that says "I don't know
what you're talking about, but if I keep nodding like this, maybe you'll go
away and leave me alone."  I'm sure you're all familiar with that look.

This must be a common problem - the childless parent record, not the vacant
nodding smile - but I'm struggling with how to detect the condition and
disallow it.  Can anybody point me in the right direction?

Thanks!

Don

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