Calculated Control on a subform update value on the main form

[Guest]

I have a calculated text box on a sub form in the footer section. Control
Source = Sum([ForecastSavings])
When I leave any individual record, it will update the total text box in the
footer.
However, when the value changes in that text box, I want to update a field
on the main form with the value of that box. I have code that works fine if
I use it in the unload event for the form, but I also want to do it every
time a user changes the total by modifying records.
The trouble is, I can't find the right event procedure to run the code when
the total text box changes. OnChange, AfterUpdate and other procedures don't
seem to fire when the total text box updates. What is the correct procedure
to use? Thanks.

 

[Dirk Goldgar]

I have a calculated text box on a sub form in the footer section.
Control Source = Sum([ForecastSavings])
When I leave any individual record, it will update the total text box
in the footer.
However, when the value changes in that text box, I want to update a
field on the main form with the value of that box. I have code that
works fine if I use it in the unload event for the form, but I also
want to do it every time a user changes the total by modifying
records.
The trouble is, I can't find the right event procedure to run the
code when the total text box changes. OnChange, AfterUpdate and
other procedures don't seem to fire when the total text box updates.
What is the correct procedure to use? Thanks.

You can have a calculated text box on the main form that displays the
value of the text box on the subform, just by referring to it; e.g.,

=[NameOfSubform]![NameOfSubformTextBox]

Then you wouldn't need to do anything at all to keep the text box up to
date.

Normally, you wouldn't want to actually store this value in the main
form's recordsource table, because it is always calculable from the
related records that the subform displays, and storing a calculated
value leaves your database open to data inconsistencies. Do you really
need to do this? If so, you probably need to use the Form events of the
subform -- AfterInsert and AfterDelconfirm -- to do it. But I wouldn't
recommend it.

 

[Guest]

Dirk,
Point taken about data inconsistencies. The form I have set up is a form
for project tracking. I allow them to type in a total project savings
amount and then calculate the savings by month on a subform for them. I
don't need to store the total amount in the project level table (and worry
about updating it) because I can just query the sub table for the totals when
I need to. My question now becomes, a user can scroll to several records
(projects) on the main form and the corresponding savings by month display on
the subform. If the recordset is empty on the subform when i scroll to a new
record, I want to enable the calculate button and the savings total fields.
If records exist, however, I assume that they have already used the button
for that particular record and would like to disable (still visible but
grayed out) the calculate button and savings amount text boxes.
What procedure should I use to check the subform on a record change and what
code should I use to check if the subform recordset is empty?
Thanks for your time, it is greatly appreciated!
Brandon

I have a calculated text box on a sub form in the footer section.
Control Source = Sum([ForecastSavings])
When I leave any individual record, it will update the total text box
in the footer.
However, when the value changes in that text box, I want to update a
field on the main form with the value of that box. I have code that
works fine if I use it in the unload event for the form, but I also
want to do it every time a user changes the total by modifying
records.
The trouble is, I can't find the right event procedure to run the
code when the total text box changes. OnChange, AfterUpdate and
other procedures don't seem to fire when the total text box updates.
What is the correct procedure to use? Thanks.

You can have a calculated text box on the main form that displays the
value of the text box on the subform, just by referring to it; e.g.,

=[NameOfSubform]![NameOfSubformTextBox]

Then you wouldn't need to do anything at all to keep the text box up to
date.

Normally, you wouldn't want to actually store this value in the main
form's recordsource table, because it is always calculable from the
related records that the subform displays, and storing a calculated
value leaves your database open to data inconsistencies. Do you really
need to do this? If so, you probably need to use the Form events of the
subform -- AfterInsert and AfterDelconfirm -- to do it. But I wouldn't
recommend it.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Dirk,
Point taken about data inconsistencies. The form I have set up is a
form for project tracking. I allow them to type in a total project
savings amount and then calculate the savings by month on a subform
for them. I don't need to store the total amount in the project
level table (and worry about updating it) because I can just query
the sub table for the totals when I need to. My question now
becomes, a user can scroll to several records (projects) on the main
form and the corresponding savings by month display on the subform.
If the recordset is empty on the subform when i scroll to a new
record, I want to enable the calculate button and the savings total
fields. If records exist, however, I assume that they have already
used the button for that particular record and would like to disable
(still visible but grayed out) the calculate button and savings
amount text boxes.
What procedure should I use to check the subform on a record change
and what code should I use to check if the subform recordset is empty?

Hmm. If I've understood you correctly, I think you can use something
like this in the main form's Current event:

'----- start of proposed code -----
Private Sub Form_Current()

If Me!sfSavingsByMonth.Form.Recordset.RecordCount = 0 Then
Me!txtTotalSavings.Enabled = True
Me!cmdCalculateSavings.Enabled = True
Else

' Ensure focus is not on the controls we're going
' to disable.
Me!SomeOtherControl.SetFocus

' Disable controls.
Me!txtTotalSavings.Enabled = False
Me!cmdCalculateSavings.Enabled = False

End If

End Sub
'----- end of proposed code -----

Note that there are lots of dummy control names in the above code, that
you'll have to replace with your own control names. "sfSavingsByMonth"
must be the name of the subform *control* that displays the subform.
"SomeOtherControl" is the name of some control on the main form that you
can send the focus to before disabling the text box and command button.
That's just in case one of those two controls has the focus, which would
prevent you from disabling it.

 

[Guest]

That was the trick Dirk. The line I didn't know how to do was the Recordset
checking line. Thanks for your help, I appreciate it!
Brandon

 

[Guest]

That was the trick Dirk. The line I didn't know how to do was the Recordset
checking line. Thanks for your help, I appreciate it!
Brandon