Saving a record in a suborm with code

[Douglas Buchanan]

I set the OnExit property of the "Cost" field in subform
"subJobDetail" to save the record (see below). Then I call a global
sub to copy the [=sum([cost])] calculated field to a field in the
subform "subJob". The record in "subJob" displays the "writing"
(pencil) icon for the record but does not update visually until I
enter and exit a second field "Markup" that is identically coded.

How do I write code to save the record in the subform "subJob"

Note: Stepping through the code in debug mode works perfectly.

What is the solution?
Code in the subform:
========================
Private Sub Cost_Exit(Cancel As Integer)
SaveRecord
mUpdateJobGrandTotal
End Sub
========================
Private Sub SaveRecord()
If Me.Dirty Then
Me.Dirty = False
End If
End Sub
========================
Code in a module:
========================
Public Sub mUpdateJobGrandTotal()
Forms![frmCustomers]![subJob].Form![txtJobTotal] = _
Forms![frmCustomers]![subJobDetail].Form![txtSumCost] + _
Forms![frmCustomers]![subJobDetail].Form![txtSumMarkup]

End Sub
========================

[Allen Browne]

Updating the calculated fields is a lower priority task in Access, so it
completes your code before they are updated.

You could try the Recalc method to force the update before running
mUpdateJobGrandTotal.

Alternatively, you could perform a DSum() operation directly on the
subform's table instead of relying on the calculated total. This has the
added advantage that it works correctly even if the subform has a filter
applied.

Ulimately, though, the question arises as to why the total is being stored
in the main form. Is there a good reason for this? Or would it be a better
design just to calculate the total when you need it so it can never be
wrong?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Douglas Buchanan" <db*********@comcast.net> wrote in message
news:db*************************@posting.google.co m...

I set the OnExit property of the "Cost" field in subform
"subJobDetail" to save the record (see below). Then I call a global
sub to copy the [=sum([cost])] calculated field to a field in the
subform "subJob". The record in "subJob" displays the "writing"
(pencil) icon for the record but does not update visually until I
enter and exit a second field "Markup" that is identically coded.

How do I write code to save the record in the subform "subJob"

Note: Stepping through the code in debug mode works perfectly.

What is the solution?
Code in the subform:
========================
Private Sub Cost_Exit(Cancel As Integer)
SaveRecord
mUpdateJobGrandTotal
End Sub
========================
Private Sub SaveRecord()
If Me.Dirty Then
Me.Dirty = False
End If
End Sub
========================
Code in a module:
========================
Public Sub mUpdateJobGrandTotal()
Forms![frmCustomers]![subJob].Form![txtJobTotal] = _
Forms![frmCustomers]![subJobDetail].Form![txtSumCost] + _
Forms![frmCustomers]![subJobDetail].Form![txtSumMarkup]

End Sub
========================

[Douglas Buchanan]

> Updating the calculated fields is a lower priority

task in Access, so it completes your code before
they are updated.
Is code considered a calculated field?
Isn't this a calculated field: =[txtSource]*[txtFactor]
You could try the Recalc method to force the update
before running mUpdateJobGrandTotal.
mUpdateJobGrandTotal is the problem.
Alternatively, you could perform a DSum() operation
directly on the subform's table instead of relying
on the calculated total. This has the added
advantage that it works correctly even if the subform
has a filter applied.
This sounds like the direction I'll take. Thank you.

I chose the code because I thought that it would run more quickly and
with less overhead than DSum(). You see the subform already displays the
Total through a calculated field without any code. The code is just
copying that calculated field to another subform where the owner of the
information observes the value and makes a one-time judgement based on
that value, then enters an arbitrary value into a second field in that
subform.
Ulimately, though, the question arises as to why the total >is being

stored in the main form. Is there a good reason >for this? Or would it
be a better design just to calculate >the total when you need it so it
can never be wrong?

With the code running properly it could never be wrong. This form is the
only access to the table. Any values added to the subform updates the
Total.

The Total is being displayed on this form because in this application
this form is the only place where this value is seen. There are
currently no plans for printed reports. Should printed reports be added
the total will not come from this location. It would be caluclated on
the report from a query of the values in the table.

Doug

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!