[AccessD] How do YOU handle child table population?

[Charlotte Foust]

What I do is use the BeforeUpdate event of the subform to check for null
values in child keys that must be populated and copy the values from the
parent form.

Charlotte Foust
(916) 206-4336

On Sat, Mar 25, 2017 at 3:23 PM, Ryan Wehler <wrwehler at gmail.com> wrote:

> The insert trigger indeed did work as I tested Friday but I am not
> committed to that idea. When a quote is inserted my trigger inserts the
> identity column into my checklist table and the questionnaires column
> defaults get filled in. It seems to happen quick enough that my subform
> shows the right selections after the parent is added!
>
> When I added the checklist to the system it made more sense to me to have
> it be a child table rather than expanding the overall width of the
> quote/parent table. It feels more normalized to me but perhaps not needed
> since it's a 1:1 relationship.
>
> As far as control default options I don't believe those get committed
> unless an option is changed or a save is called on the form.... these are
> option groups by the way...  I can tinker with that idea Monday.
>
> Just trying to see what else would work. The trigger made the most sense
> to me at the time of writing the email.
>
> Thanks for all the suggestions !
>
>
> Sent from my iPhone
>
> > On Mar 25, 2017, at 4:34 PM, Stuart McLachlan <stuart at lexacorp.com.pg>
> wrote:
> >
> > Actually, that's only true if you enforce referential integrity at the
> table level.
> >
> > You can indeed create child records in a subfrom as you describe if
> integrity is not set. The
> > problem is that the Foreign Key of the supposed child is set to 0 since
> there is no record in
> > the parent form.
> >
> > I don't think your idea of an Insert table trigger will work because the
> sub form will not refresh
> > with the newly created child record.
> >
> > If a child record is required for every parent record, one was (untried)
> would be to use the
> > parent form's After Insert event to set the values of the controls in
> the sub form.
> >
> > But why not just set the default values for the child table in the
> various control properties on
> > the sub form?
> >
> > And one has to ask - why a parent child relationship?  Can you have more
> than one quote
> > checklist for a single quote?  Sounds like you need to rethink you
> schema.
> >
> >
> >> On 24 Mar 2017 at 14:23, Ryan W 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
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>