?
Solved

Referencing Calculated fields in Subforms in Access 2007

Posted on 2008-11-01
5
Medium Priority
?
1,395 Views
Last Modified: 2013-11-28
I have two subforms on an Access Main Form.  I need to get two calculated values from each subform footer and add together on the main form. One of the subforms is embedded into a second subform. This one holds the calculation of TotalDuration of multiple sessions in another subform.  See image on first link here stored on Jing.  http://screencast.com/t/E9gCEctqB
Short Flash video explaination on this link.

http://screencast.com/t/HLwX4rQU0Ya

HELP!

0
Comment
Question by:phoenxMan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 22861140
To refer to a control on a sub-sub form:

forms!MFname!SFContainerControlName.Form!SSFContainerControlName.Form!ControlName

MFName is the name of the main form.
SFContainerControlName is the name of the control on the main form that holds the subform.
SSFContainerControlName is the name of the control on the subform that holds the sub-subform.
ControlName is the name of the control on the sub-subform.
0
 

Author Comment

by:phoenxMan
ID: 22861647
In my string I am setting the value of a calculated field from a subform1 to a subsubform2 .  The syntax is correct but it is returning a "0" verses the value as shown.When I do this an unbound control on the parent form (main) it gets the value correctly.  

=[Forms]![FRMSimSetupHeader]![sbfrmSimWCSessionsList].[Form]![SumTotalTime]

I am going to try setting the calculation to a bound control on the subsubform with event procedure to store it and update as the values change.  Not the way I want to do this but will see if this will work.  Otherwise I tried to use Dsum() to calculate the totals minutes in a series of sessions but it gets the where clause parameter from the subform1.  Doesn't work. I first placed all three subforms as children to the main from and used a filter criteria verses the Master Child function to synchronize the two subforms.  But Access 2007 gives a request for parameter when loading the form the first time. BIG BUG In access 2007.
Do you have any solutions to this problem?

0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 750 total points
ID: 22883062
phoenxMan,

Another approach is to not create the Calculated field on the form.
You can create it as a field in the source query.
This way it is a field, like any other field in the recordsource.
And as such, you can reference it as easily as any other field.

The other advantage is that the form will perfom much faster, because the query is doing the "Heavy Lifting"

Lastly, if it is in the source, you can easily use it in any other Reports or Forms, without having to re-create it over and over again.

JeffCoachman
0
 

Author Closing Comment

by:phoenxMan
ID: 31512424
Thanks for the feedback.  I did try this early.  However, I tested the technique of reference the second embedded calculation field in a simple form and it worked as it was suppose to,. Then I rebuilt the problem form and added new fresh controls to get the calculated values and now it works.  I have experience more anomolies in Access 2007 not working as it should then suddenly it works. But now I have the solution and I am happy.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22888333
OK
;-)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question