Solved

Recalculate fields after requery.

Posted on 2010-08-27
6
628 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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 Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

730 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