Inserting a beginning record in a subform

[DaveJJ]

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.

 

[John W. Vinson]

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?) record in
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/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[DaveJJ]

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/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/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.