On Feb 7, 3:31*pm, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com>
wrote:
> On Mon, 7 Feb 2011 08:52:23 -0800 (PST), DaveJJ <garypalm...@gmail.com> wrote:
> >I have the following procedure that runs when I create or update a
> >record on a main-form and when an attached sub-form has zero records
> >Its purpose is to insert a default beginning record in the sub-form
> >table when a new record is created on the main form. *This action is
> >only supposed to occur once on the sub-form so it is not a default
> >value in the normal sense. *The following code works well but to be
> >honest I'm not sure why or if its the best approach.
>
> >The query that is fired is an append query. *I was initially got
> >update errors with this procedure until I added *DBEngine.Idle
> >dbRefreshCache, DoEvents in the code.
>
> >Private Sub Form_AfterUpdate()
>
> > * *If Nz(DCount("*", "tblRouting", "[JobEnvelopeNo] = " & Me.txtID),
> >0) = 0 Then
> > * * * *DoCmd.SetWarnings False
> > * * * *DoCmd.OpenQuery "qryInsertRoutingJobReview"
> > * * * *DoCmd.SetWarnings True
> > * * * *DBEngine.Idle dbRefreshCache
> > * * * *DoEvents
> > * * * *Me.sfrmRouting1.Form.Requery
> > * * * *Me.sfrmRoutingQuickLook.Form.Requery
> > * *End If
>
> >End Sub
>
> >Would appreciate any insights to better understand what is happening
> >here and if this is fully optimized code.
>
> The problem is that a) a Subform opens *before* its mainform, and b) if you're
> enforcing referential integrity, a record cannot be created in the child table
> unless there is already a record in the parent table.
>
> What real-life need is served by creating an (empty? placeholder?) recordin
> the child table on any change in a parent record?
> --
>
> * * * * * * *John W. Vinson [MVP]
> *Microsoft's replacements for these newsgroups:
> *http://social.msdn.microsoft.com/For...-US/accessdev/
> *http://social.answers.microsoft.com/.../en-US/addbuz/
> *and see alsohttp://www.utteraccess.com
This after-update event occurs in the main form not the sub form. The
query does not insert an empty record. It enters the beginning
station in a manufacturing routing plan and only on a new record. It
does actually work quite well but my sense it might cause problems
down the road that's why I posted it. I haven't enforced referential
integrity yet but I was planning to.
Similar Threads
