Pulling Sum Fields From One Subform to Another

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,
I have a main events table and the event number is joined to one of three
other
subtables (tblAV, tblScenic, and tblLighting).

For example, the form frmScenic has two entries tied to event number 1:
Palm Trees $300
Music $120

The footer (sumScenic) holds the total of $420.

Each of the subforms has it's own items associated with the event (either
AV, Scenic, or Lighting). I want a main form that has the totals that appear
in the footer from each form. I can't figure out how to do this, as I thought
I would enter: =Forms!sfrmAV!sunScenic into the control source on the main
form's text box. Instead of the $420 amount, I get the #Name? error.

I can explain more if this doesn't make sense. Thanks so much!!!
 
Hi Jennifer,

When referencing a subform, you need to use the following syntax:

forms!MAINFORM.SUBFORM.form.FIELD

or me.SUBFORM.form.FIELD

replacing MAINFORM, SUBFORM and FIELD with the appropriate names.

Hope this helps.

Damian.
 
The full syntax is:

= Forms!MainForm!sfrAV.Form!sumScenic

I assumed from the use of the prefix "sfr", you are aware that you need to
use the name of the SubformControl which may be different from the name of
the Form being used as the Subform.

Since you want to bring the Total from the Subform to the main Form in the
context of the Control on the main Form, you can also use:

= Form!sfrAV.Form!sumScenic

(note no "s" after the first "Form")
 
Hi Damian,
Ok, that worked. Here's what I ended up with:
=[Forms]![frmEventSetup]![Audio-Visual].[Form]![AVSumBudget]

Now that I have that working, I have another issue - the totals aren't
updating based on the record that is showing. For example, if the AVSumBudget
for record 1 is $100 and for record 2 is $300, the newly-created textbox will
not show $100 and $300 respectively as I navigate through the records. I
think I need an "OnCurrent" or something but am not sure where to put it. Can
you help? Thank you!!!
 
Hi Van
Your suggestions were great! Here's what I ended up with:
=[Forms]![frmEventSetup]![Audio-Visual].[Form]![AVSumBudget]

Now that I have that working, I have another issue - the totals aren't
updating based on the record that is showing. For example, if the AVSumBudget
for record 1 is $100 and for record 2 is $300, the newly-created textbox will
not show $100 and $300 respectively as I navigate through the records. I
think I need an "OnCurrent" or something but am not sure where to put it. Can
you help? Thank you!!!
 
Hi Jennifer,

It *should* refresh when the record changes - I find that it sometimes takes
a 1/2 a second or so...

If it's not refreshing, you might like to call the requery method to force a
refresh... put the following code in the On Current event for the main form:

me.FIELDNAME.requery

Where FIELDNAME is the name of the field you are putting the summed value in.

Hope this helps.

Damian.

Jennifer Cali said:
Hi Damian,
Ok, that worked. Here's what I ended up with:
=[Forms]![frmEventSetup]![Audio-Visual].[Form]![AVSumBudget]

Now that I have that working, I have another issue - the totals aren't
updating based on the record that is showing. For example, if the AVSumBudget
for record 1 is $100 and for record 2 is $300, the newly-created textbox will
not show $100 and $300 respectively as I navigate through the records. I
think I need an "OnCurrent" or something but am not sure where to put it. Can
you help? Thank you!!!

--
Thank you! - Jennifer


Damian S said:
Hi Jennifer,

When referencing a subform, you need to use the following syntax:

forms!MAINFORM.SUBFORM.form.FIELD

or me.SUBFORM.form.FIELD

replacing MAINFORM, SUBFORM and FIELD with the appropriate names.

Hope this helps.

Damian.
Click to expand...
Click to expand...
 
Hi Van,
Ok, we're getting close...

If I enter your code into the OnCurrent of the main form, it will update the
field that the focus is on - but only that field. It won't update the other
two fields. For example, if the curser is in the AVSumBudget field, it will
update but ScenicSumBudget and LightingSumBudget remain blank.

Any thoughts? You've been so helpful!
--
Thank you! - Jennifer


Van T. Dinh said:
Try:

Me.Recalc

in the Form_Current Event Procedure.

--
HTH
Van T. Dinh
MVP (Access)



Jennifer Cali said:
Hi Van
Your suggestions were great! Here's what I ended up with:
=[Forms]![frmEventSetup]![Audio-Visual].[Form]![AVSumBudget]

Now that I have that working, I have another issue - the totals aren't
updating based on the record that is showing. For example, if the
AVSumBudget
for record 1 is $100 and for record 2 is $300, the newly-created textbox
will
not show $100 and $300 respectively as I navigate through the records. I
think I need an "OnCurrent" or something but am not sure where to put it.
Can
you help? Thank you!!!
Click to expand...
Click to expand...
 
Hi Damian,
It's strange b/c it's refreshing and displaying information for the previous
record that was displayed...strange.

Ok, so I tried: Me.SumAVBudget.Requery where SumAVBudget is the name of
the field in the subform that needs to be updated. When I run it, though, I
get an error message stating "Compile Error: Method or Data Member Not Found".

Any thoughts? I think I need to tie the field to the form that the OnCurrent
action takes place on, but I don't know how to code it to read the field from
a subform.

Thank you so much for helping me through this!!!
--
Thank you! - Jennifer


Damian S said:
Hi Jennifer,

It *should* refresh when the record changes - I find that it sometimes takes
a 1/2 a second or so...

If it's not refreshing, you might like to call the requery method to force a
refresh... put the following code in the On Current event for the main form:

me.FIELDNAME.requery

Where FIELDNAME is the name of the field you are putting the summed value in.

Hope this helps.

Damian.

Jennifer Cali said:
Hi Damian,
Ok, that worked. Here's what I ended up with:
=[Forms]![frmEventSetup]![Audio-Visual].[Form]![AVSumBudget]

Now that I have that working, I have another issue - the totals aren't
updating based on the record that is showing. For example, if the AVSumBudget
for record 1 is $100 and for record 2 is $300, the newly-created textbox will
not show $100 and $300 respectively as I navigate through the records. I
think I need an "OnCurrent" or something but am not sure where to put it. Can
you help? Thank you!!!

--
Thank you! - Jennifer


Damian S said:
Hi Jennifer,

When referencing a subform, you need to use the following syntax:

forms!MAINFORM.SUBFORM.form.FIELD

or me.SUBFORM.form.FIELD

replacing MAINFORM, SUBFORM and FIELD with the appropriate names.

Hope this helps.

Damian.

:

Hello,
I have a main events table and the event number is joined to one of three
other
subtables (tblAV, tblScenic, and tblLighting).

For example, the form frmScenic has two entries tied to event number 1:
Palm Trees $300
Music $120

The footer (sumScenic) holds the total of $420.

Each of the subforms has it's own items associated with the event (either
AV, Scenic, or Lighting). I want a main form that has the totals that appear
in the footer from each form. I can't figure out how to do this, as I thought
I would enter: =Forms!sfrmAV!sunScenic into the control source on the main
form's text box. Instead of the $420 amount, I get the #Name? error.

I can explain more if this doesn't make sense. Thanks so much!!!
Click to expand...
Click to expand...
Click to expand...
 
Are all 3 Controls on the main Form whose Current Event you used to run the
Recalc?

Please stick to one thread and allow time for replies as it is hard to
reconcile your descriptions in different threads.

Note also that all respondents are volunteers and most have full-time jobs
somewhere else. Another thread should only be created if no reponse is
received for at least 24 hours.
 
Hi Van,
Sorry for the multi-threads.

The controls are within a subform on the main form. The main form houses
four subforms - one for scenic costs, one for lighting costs, one for AV
costs, and one that I want to display the totals for each on. This final
"total" subform is where I have the issue. My original code
(=[Forms]![frmEventSetup]![Audio-Visual].[Form]![AVSumBudget]) summed the
values from the scenic, lighting, and AV subforms and inserted them onto the
totals subform, but it wasn't linking the correct records.

I entered your code, but now if the curser is in the AVSumBudget field while
in the "total" subform, AVSumBudget will update but ScenicSumBudget and
LightingSumBudget remain blank. Let me know if that isn't super clear and
I'll try to re-explain.

--
Thank you! - Jennifer


Van T. Dinh said:
Are all 3 Controls on the main Form whose Current Event you used to run the
Recalc?

Please stick to one thread and allow time for replies as it is hard to
reconcile your descriptions in different threads.

Note also that all respondents are volunteers and most have full-time jobs
somewhere else. Another thread should only be created if no reponse is
received for at least 24 hours.
Click to expand...
 
If the Total Controls are on one of the SubForms and you need to recalc
these when you move to another record (in the main Form), use the main
Form_Current Event to run:

Me.sfrTotal.Form.Recalc

which recalculates the calculated Controls on the Subform "sfrTotal".
 
Back
Top