Solved

Recalculate fields after requery.

Posted on 2010-08-27
6
624 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
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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…

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now