[AccessD] How do YOU handle child table population?

[Rocky Smolin]

I would use the After Update (or maybe even After Insert not sure if you
have the PK at that point) event to create the record in Quote Checklist
(using DAO), populate the defaults there, then requery the main form, saving
the PK of the main form's new record so you can return to it after the
requery.  

Although if you have control over the back end design, you could set those
defaults in the default value property of the checklist fields so when you
added the record they would be set with no additional code from you.

But if you have control over the back end design and the relationship is 1
to 1, and each quote record has the checklist, then why not merge the
checklist fields into the quote table and lose the subform? IOW, don't solve
the problem, just get rid of it. :)


Rocky Smolin
Beach Access Software
760-683-5777
www.bchacc.com
www.e-z-mrp.com
Skype: rocky.smolin
 



-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Bill Benson
Sent: Saturday, March 25, 2017 12:22 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] How do YOU handle child table population?

I recall in working with unbound forms and controls i would create new
records with recordset object and .Addnew then .Update, then set the
recordsource sgain and use .FindFirst and recordsetclone, bookmark, update
controls on the form and leave the user in edit mode from there. Roughly,
it's from memory.

>From my non-flammable Note 3,
Bill Benson

On Mar 24, 2017 3:24 PM, "Ryan W" <wrwehler at gmail.com> wrote:

As you all know, if you have a table that's a child of another, meaning
there's a PK/FK relationship between them, Access won't populate the child
table with data until a piece of information is entered (a checkbox checked,
a combo box filled out, etc) if it's got a form related to it.


Example:

frm_Quote is my quotes form.
frm_QuoteChecklist is the subform on a tab control within that quote form.

Tables:
Quote
QuoteChecklist

Quote - QuoteID PK
QuoteChecklist - QuoteID FK


My manager has asked me to "fill in defaults" for QuoteChecklist once a
quote has been inserted.  Normally one might use the "default value" option
in the table layout, in this case SQL Server.

BUT... those defaults still don't actually populate/do anything until
something on frm_QuoteChecklist is manipulated, right?

So.. my question to you is how would YOU handle this??  My initial thought
is to have a insert trigger on the Quote table to insert the QuoteID_FK into
the QuoteChecklist table so that the defaults can populate and the
relationship happen.
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com