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
Solved

Calculated control problem

Posted on 2014-02-06
7
314 Views
Last Modified: 2014-02-10
A & B  are controls on a subform that get their data from other fields. A is from a total field on the subform's subform.

When I open the form for the first time All works well (Capture5.PNG)
When I duplicate all of the records in a project and move to the duplicate AB&C are correct but I get error in D. The really strange thing is that I now get the same error at D in ALL records including the original that was duplicated. When I close the form and reopen all records are correct including the duplicate.

I have tried refresh dirty update etc but the only way to get back to correct calculated fields (D) is to close the form and re-open. How can that be? It's as if duplicating the records has switched off certain calculates controls.

A =VPSubForm.Form!TVPCost
B =TotalDoorCost
C =A+B
D =VPSubForm.Form!TVPCost+TotalDoorCost

A. B, and C are all correct.  D Gives error
Capture5.PNG
Capture6.PNG
0
Comment
Question by:DatabaseDek
  • 4
  • 3
7 Comments
 
LVL 35

Expert Comment

by:PatHartman
ID: 39839988
When you are doing arithmetic with fields that may be null, you need to take that into consideration.  See if using the Nz() function solves the problem.

D =Nz(VPSubForm.Form!TVPCost,0) + Nz(TotalDoorCost,0)
0
 

Author Comment

by:DatabaseDek
ID: 39841903
Thanks Pat

I don't use Nz because if I return a zero when a control is not responding then my total will be out.

The weird thing is that I can see the controls on all subform and they all have values.

To solve the problem, I have discovered, I need a controls on the subform that get their data from the Subform's subform's calculated control. As in the example I can then add these together. If I don't do this and i refer to the subform's subform controls directly the problem then effects all the viewed record across all the entire Dbase. The data is not changing just the calculated fields. I do not understand this. You would think that if I can open the database and all is well that merely adding data to the same tables could not possibly effect all the calculated controls for other data on the dbase. But it does. I am not sure if there is an answer to this one.
0
 
LVL 35

Expert Comment

by:PatHartman
ID: 39843675
Access maintains only one set of properties for each control.  That's why when you have an unbound control on a continuous form, it always contains the same value across all instances.  You may find things work better if you do the initial calculation in the Form's RecordSource query.  Then you can aggregate a bound field.

Select ..., A + B as AB From yourtable;

So you can bind a control to AB and sum that rather than using =A+B as the ControlSource.
0
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)

 

Author Comment

by:DatabaseDek
ID: 39843848
Thank you

The subform's subform table is a different table to the subforms table and the values that are calculated are from the subforms subform. When i tried to join them in the main subform's underlying query so that I could do the calculations in the query I find that the subforms table is then not updateable.

So the reason for the calculated control is to sum the data from a different table or sub subform and then add it to the sum of the values on the main subform. Adding values from two tables.
0
 
LVL 35

Accepted Solution

by:
PatHartman earned 500 total points
ID: 39844162
The query isn't updateable because it aggregates data not because it joins multiple tables.  Once data is aggregated, there is no way for the database engine to identify a specific row to update and that is what makes the query not updateable.  Adding a calculated column to add/subtract/multiply/divide columns from one or more tables would not affect the updateable property of a query.

If you put your aggregation functions in controls in the footer of the subform, you should be able to refer to them there.  So you can add a control  named SumABCD that does a calculation and sums the result -
=Sum(A + ((B * C) - .5)/ D)

Then in the parent form you would refer to it as-
Me.sfrmXXX.Form!SumABCD
0
 

Author Comment

by:DatabaseDek
ID: 39845224
Thanks Pat

I was pretty sure I did not have any aggregates in the query. When I tried it.

But what I was trying to do was to add to the main form query the query from the subform so that I could use the new query for cross table calculations and when attempting to change values in fields on either the subform or the form (Can't remember which) I got a "this recordset is not updateable"
0
 

Author Closing Comment

by:DatabaseDek
ID: 39846909
Me.sfrmXXX.Form!SumABCD

Brilliant as usual

Thanks Derek
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

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…
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.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

840 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