Solved

Recalculate fields after requery.

Posted on 2010-08-27
6
629 Views
Last Modified: 2012-05-10
I have a form with a subform and calculated field.  When the form opens the subform has no control source.  There is a field on the main form where the user enters a date and clicks "Submit".
The following events then take place:
Subform control source is set to a query.
Main form date is passed to subform/query and records are returned.
One subform field sums up a field from all records (this was in the footer of subform, now in main section of subform).
Control Source gets set for calculated field on main form (see below).

The calculated field on the main form is linked to the subform field that totals up all records.  I just want to show the total on the main form.  I should note that the calculated field on the main form is unbound upon opening the form, but the control source gets set after the control source is set for the subform.

Issue:  The calculated field on the main form is blank after clicking "Submit" and after records are returned.  I'm pretty sure this is a timing issue because I have a button that just calculates the total on the main form (when there are subform records present) and it pops up with no problems.

How do I get this field to be calculated after the subform returns all records?

Thanks in advance!
Kelly
0
Comment
Question by:kellysteevens
[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
6 Comments
 
LVL 11

Expert Comment

by:LambertHeenan
ID: 33545726
Try Me.Refresh

That's supposed to force recalculation.
0
 
LVL 75
ID: 33545755
Are you sure you don't mean

Me.ReCalc ....?

mx
0
 
LVL 10

Accepted Solution

by:
t_hungate earned 500 total points
ID: 33545767
I think tht you called it on this one.  I would set an on timer event, 3-5 sec should work from after the time you click on the submit button.  Also, if you are not storing the calculated filed from the main form, you can always set them to mirror. i.e.  

Me.txtMyCalcVal = [Forms]![frmYourSubFrm].[txtSubCalcVal]

TLH
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 11

Expert Comment

by:LambertHeenan
ID: 33545814
Yes indeed. I meant to say Me.ReCalc (stupidly relied on mere human memory)
0
 
LVL 75
ID: 33545908
Can you:

1) Compact & Repair (*** to shrink the size),

2) Zip up the MDB (*** to further shrink the size)

3) Attach the file for upload here (using the 'Attach File function below) ... removing any sensitive data of course.

4**** And please give a clear explanation of exactly how to reproduce the problem or what you are trying to do.

NOTE:  If you have an A2007 ACCDB, please convert to A2003 MDB.
mx
0
 

Author Closing Comment

by:kellysteevens
ID: 33546499
I am not too bright today.  I had originally set the source of the fields in the control source properties (the easy way), but when the form loaded with no submit date, the fields *I thought* said "Name?" which I thought was confusing to the user.  So then I spent hours over-thinking things and was trying to problematically get the value.

I went back and did it the easy way as suggested and it worked fine and now calculates correctly.  Thanks for all the help!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

728 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