Making bound field value equal other field value

[Rob]

Hello All,

I have a form and a subform. When the subform is updated I get a total of a
specific field and can transfer that total to my main form and make an
unbound field equal that total. I really need one of the fields (bound) in
my main form to equal that total. How can I make a bound field equal that
total from the subform?

Thanks,

Rob

 

[Jeff Boyce]

Why do you 'really need' this? Are you trying to save the calculated value
in a table? If so, it is rarely necessary to save a value that can be
calculated from other data you already store.

If you'll explain more about your underlying data and situation, it may be
that you can do what needs to happen without saving calculated data.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Rob]

Hello Jeff,

Yes I need to store this calculated data in my table. I have a sub form
that allows users to track the checks that are paid to a vendor. At the
bottom of the subform the total is shown. I need this total to be
transferred to a bound field on the main form. I have tried to set the After
Update event of the subform to trigger the change but it seems to only work
if I go back and type numbers in twice - odd. I can also create a public
variable in a module and store the total from the sub form to that and
transfer it. What I am having a problem with is figuring out what event to
use to trigger this transfer. Any suggestions would be greatly appreciated.

Rob

 

[Jeff Boyce]

Rob

I'm sorry, but I'm still not grasping the business need for storing the
total.

If you have the sub-records, and can add up the total, what does have the
total allow you to do that you couldn't do by (re-)adding up the
sub-records?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Rob]

Jeff,

This was a program created by someone else and there are a ton of reports
that point to this field. I really just need to know if it is possible to
get the data from the calculated field to populate the bound field. And, if
not what is the best way to get it in there. I know that this sounds like an
odd request since you can create a calculation in any form or report to show
this total. But, since there is so much relying on this one field and I do
not want to redo a lot of reports and forms I just need to know how to
populate a bound field with data from aother source.

I have tried many different events such as On Dirty, After Update, On Change
for the calculated field in the subreport. In each instance I have told the
program to store the answer to a public variable and then transfer it to the
main report - no luck. I have even tried to point the answer directly to the
main forms' field - Forms!Form1!Field1 = Me.TotalField. This will not work
either. I have even tried to change the On Current and On Lost Focus for the
subform - no luck.

Thanks,

Rob

 

[Jeff Boyce]

Rob

I'll need to step back, as I've not done what you're asking for a way to do.

I suppose if you cannot devote the time/resources to fix the application
(think about the folks AFTER you who will need to maintain it!), then a
possible interim fix would be to create a query that returns the calculated
value, change the fieldname in that query to match the existing "stored"
fieldname, and re-point the existing reports (?and forms) to use the query
instead of the table.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

Rob

I'll need to step back, as I've not done what you're asking for a way to do.

I suppose if you cannot devote the time/resources to fix the application
(think about the folks AFTER you who will need to maintain it!), then a
possible interim fix would be to create a query that returns the calculated
value, change the fieldname in that query to match the existing "stored"
fieldname, and re-point the existing reports (?and forms) to use the query
instead of the table.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Rob]

Jeff,

Thank you for trying.

Rob

Rob

I'll need to step back, as I've not done what you're asking for a way to do.

I suppose if you cannot devote the time/resources to fix the application
(think about the folks AFTER you who will need to maintain it!), then a
possible interim fix would be to create a query that returns the calculated
value, change the fieldname in that query to match the existing "stored"
fieldname, and re-point the existing reports (?and forms) to use the query
instead of the table.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP