Problem with calculated controls with data from subforms

  • Thread starter Thread starter Ragnar Midtskogen
  • Start date Start date
R

Ragnar Midtskogen

Hi,

We have an application where one of the forms has several calculated fields.
Some of the data comes from subforms, some use data in other calculated
fields.
The subforms are linked to the main form on the invoice number.

This has worked for years, moving from Access 95 through 98 to 2000, but
one client bought new computers running Win XP Home Edition and with Office
XP, and now the calculated fields show up blank..

It appears to be an Access 2003 problem, because it works fine on Access
2000 machines running Win 98, 2000 and XP.
I am wondering if it is a timing problem, since some calculated controls use
the contents of other calculated controls.
I also notice that when I move the main form from one record to the next,
the controls takes a second or two to update.
The new machines are also fast, 2.7 GHz.

Main form, Invoice, based on the Invoice Header table, has several subforms,
one of which is the Invoice Detail subform, based on the Invoice Detail
table.
..
The main form has one calculated control called SubTotal with the
ControlSource as follows:
=Nz([Forms]![Invoice]![Invoice Detail subform].Form!OrderTotal)
where OrderTotal, in the form footer in this subform, is the sum of all
invoice line item amounts.

It has another calculated control called CalSalTax with the following
ControlSource:
=(Nz([SubTotal])*Nz([STRATE]))/100
where SubTotal is the first calculated control.

There is another calculated control called CalTotal with the following
ControlSource:
=[SubTotal]+IIf(IsNull([CalSalTax]),0,[CalSalTax])
which depends on the two previous calcualted controls, SubTotal and
CalSalTax.

There is another calculated control, CalDeposits, which uses data from
another subform, the ControlSource is:
=IIf(Not
IsNull([Child127].Form!tcal_Sum_Payment),[Child127].Form!tcal_Sum_Payment,0)
where tcal_Sum_Payment in the form footer is the sum of all deposits in this
subform

Then there is a calculated control called CalIntPaid Installation paid)
whith the following ControlSource:
=IIf([InstalISpaid]=-1,Nz([INSTALCOST]),0)
where InstalISpaid and INSTALCOST are based controls on the main form.

Finally, there is the calculated control called DueStore with this
ControlSource:
=Nz([caltotal])-Nz([caldeposits])-Nz([CalIntPaid])
where caltotal, caldeposits and CalIntPaid are the calculated controls
above.

I tried calculating the second calculated control directly, but then all
calculated controls show #Error.
I am thinking about trying to calculate all the controls directly, but would
like some opinions.

Any help would be appreciated.

Ragnar
 
I had this happen this week in a similar scenario (moving
to Access 2003 and Windows XP from Access 2002 and Windows
2000). First try "compact and repair." If that does not
clear up the problem, then rebuild the database as follows:

Create a blank database
Import all the objects (tables, queries, forms, reports,
macros, modules) from the old database.

I did these things and it fixed my problem.
-----Original Message-----
Hi,

We have an application where one of the forms has several calculated fields.
Some of the data comes from subforms, some use data in other calculated
fields.
The subforms are linked to the main form on the invoice number.

This has worked for years, moving from Access 95 through 98 to 2000, but
one client bought new computers running Win XP Home Edition and with Office
XP, and now the calculated fields show up blank..

It appears to be an Access 2003 problem, because it works fine on Access
2000 machines running Win 98, 2000 and XP.
I am wondering if it is a timing problem, since some calculated controls use
the contents of other calculated controls.
I also notice that when I move the main form from one record to the next,
the controls takes a second or two to update.
The new machines are also fast, 2.7 GHz.

Main form, Invoice, based on the Invoice Header table, has several subforms,
one of which is the Invoice Detail subform, based on the Invoice Detail
table.
..
The main form has one calculated control called SubTotal with the
ControlSource as follows:
=Nz([Forms]![Invoice]![Invoice Detail subform].Form! OrderTotal)
where OrderTotal, in the form footer in this subform, is the sum of all
invoice line item amounts.

It has another calculated control called CalSalTax with the following
ControlSource:
=(Nz([SubTotal])*Nz([STRATE]))/100
where SubTotal is the first calculated control.

There is another calculated control called CalTotal with the following
ControlSource:
=[SubTotal]+IIf(IsNull([CalSalTax]),0,[CalSalTax])
which depends on the two previous calcualted controls, SubTotal and
CalSalTax.

There is another calculated control, CalDeposits, which uses data from
another subform, the ControlSource is:
=IIf(Not
IsNull([Child127].Form!tcal_Sum_Payment),[Child127].Form! tcal_Sum_Payment,0)
where tcal_Sum_Payment in the form footer is the sum of all deposits in this
subform

Then there is a calculated control called CalIntPaid Installation paid)
whith the following ControlSource:
=IIf([InstalISpaid]=-1,Nz([INSTALCOST]),0)
where InstalISpaid and INSTALCOST are based controls on the main form.

Finally, there is the calculated control called DueStore with this
ControlSource:
=Nz([caltotal])-Nz([caldeposits])-Nz([CalIntPaid])
where caltotal, caldeposits and CalIntPaid are the calculated controls
above.

I tried calculating the second calculated control directly, but then all
calculated controls show #Error.
I am thinking about trying to calculate all the controls directly, but would
like some opinions.

Any help would be appreciated.

Ragnar


.
Click to expand...
 
Hi Ragner
I have had a similiar problem and only with 2003. Calculated fields that
displayed ok in A97, 2000 & 2002 didn't work in 2003 and that some
calculated controls showed blank until I moved the mouse over them.
Apparently there seems to be many display problems in 2003 but to correct
the problem in my case I carefully inserted a Me.Recalc in certain processes
which seemed to fix the problem by "forcing" a recalculation of the
controls. I don't know if this is helpful but just in case it is I thought I
would mention it.

--
Regards
Ian Baker
(If a=ian, b=jackaroo, c=net, d=au then me= (e-mail address removed))
-
Ragnar Midtskogen said:
Hi,

We have an application where one of the forms has several calculated fields.
Some of the data comes from subforms, some use data in other calculated
fields.
The subforms are linked to the main form on the invoice number.

This has worked for years, moving from Access 95 through 98 to 2000, but
one client bought new computers running Win XP Home Edition and with Office
XP, and now the calculated fields show up blank..

It appears to be an Access 2003 problem, because it works fine on Access
2000 machines running Win 98, 2000 and XP.
I am wondering if it is a timing problem, since some calculated controls use
the contents of other calculated controls.
I also notice that when I move the main form from one record to the next,
the controls takes a second or two to update.
The new machines are also fast, 2.7 GHz.

Main form, Invoice, based on the Invoice Header table, has several subforms,
one of which is the Invoice Detail subform, based on the Invoice Detail
table.
.
The main form has one calculated control called SubTotal with the
ControlSource as follows:
=Nz([Forms]![Invoice]![Invoice Detail subform].Form!OrderTotal)
where OrderTotal, in the form footer in this subform, is the sum of all
invoice line item amounts.

It has another calculated control called CalSalTax with the following
ControlSource:
=(Nz([SubTotal])*Nz([STRATE]))/100
where SubTotal is the first calculated control.

There is another calculated control called CalTotal with the following
ControlSource:
=[SubTotal]+IIf(IsNull([CalSalTax]),0,[CalSalTax])
which depends on the two previous calcualted controls, SubTotal and
CalSalTax.

There is another calculated control, CalDeposits, which uses data from
another subform, the ControlSource is:
=IIf(Not
IsNull([Child127].Form!tcal_Sum_Payment),[Child127].Form!tcal_Sum_Payment,0)
where tcal_Sum_Payment in the form footer is the sum of all deposits in this
subform

Then there is a calculated control called CalIntPaid Installation paid)
whith the following ControlSource:
=IIf([InstalISpaid]=-1,Nz([INSTALCOST]),0)
where InstalISpaid and INSTALCOST are based controls on the main form.

Finally, there is the calculated control called DueStore with this
ControlSource:
=Nz([caltotal])-Nz([caldeposits])-Nz([CalIntPaid])
where caltotal, caldeposits and CalIntPaid are the calculated controls
above.

I tried calculating the second calculated control directly, but then all
calculated controls show #Error.
I am thinking about trying to calculate all the controls directly, but would
like some opinions.

Any help would be appreciated.

Ragnar
Click to expand...
 
Thank you Chaplain Doug,

I tried both at the client's computer, to no avail.

If I set the focus to each of the calcualted controls the numbers show, so I
might try a routine that sets the focus.
Another possiblity is to fill the controls from a recordset.
The problem is, I have some reports that show the same problem, so either
approach could involve some work.
I think I will talk the client into getting 2002 on EBay (:-)).

Ragnar
 
Thank you Ian,

This is my first contact with Access 2003, it is somewhat of a relief to
hear that others have had problems too.

Please see my reply to Chaplain Doug. In my case it is not enough to move
the mouse over the controls, I have to set the focus to get any reaction..

I have thought about using some recalcs, and I will try that too, it should
be the least likely to cause other problems.

The problem in this application is that I am already using a lot of the
form's events for other purposes, so I am worried I might trip up something
else.

For example, I tried a Requery for the main form in the OnRecord event and
ended up with an oscillating form, probably because the Requery triggered
the OnRecord event.

Ragnar
 
Hello Ian,

Just in case you are interested, I had to make a change. The Recalc
apparently causes the form to be requeried and that tripped me up. I have a
case where I go to this form from another form, and I move to a specific
record. The requery messed this up.
To get around this I tried just moving the focus to the blank controls and
that worked. I had to set the focus to one of them twice, but it seems to be
working.

Ragnar
 
Hi Ragnar
Interesting as recalc does not cause the form to requery under standard
circumstances. From the help on Recalc:
"The Recalc method immediately updates all calculated controls on a form.

Using this method is equivalent to pressing the F9 key when a form has the
focus. You can use this method to recalculate the values of controls that
depend on other fields for which the contents may have changed."

Both Refresh and Requery do cause the form to requery with refresh having
limitations in that it does not include deleted or new records. So there
must be other processes happening that a recalc invokes resulting in the
form being requeried which is why I mentioned placing a recalc "carefully"
may resolve your issue.

Having said all this it is hard to see exactly the whole picture. If you
want you could strip out only the components that are in question into a new
database, zip it up and email it to me for a look and I will see what I can
do.

If it helps what I use to open another form and display a selected record
is:
Scenario - A Purchase Order form and an Account Payable form. The account
payable table has a field that contains the recordID of the purchase order
that was used for the account payble record
1. Create a label on the Account Payable form
2. Make it a hyperlink by simply placing a space in the hyperlink address
property
3. Place code in the OnClick event of the label (see step 4 for the reason
of why I have included acDialog, "Select Record" in the following code):
Dim stLinkCriteria As String
stLinkCriteria = "[PurchaseOrderID]=" & Me!PurchaseOrderID
DoCmd.OpenForm "Purchase Order", , , stLinkCriteria, , acDialog, "Select
Record"
4. If you want only the relevant record to be displayed and nothing else
then place the following code in the OnOpen event of the Purchase Order
form:
If Me.OpenArgs = "Select Record" Then Me.AllowAdditions = False
5. If there are a lot of calculated controls on the Purchase Order form and
with the display problems of 2003 you would then insert a Me.Recalc in the
OnLoad event of the Purchase Order form.

My fingers were getting cold which is why I thought I would type the above
:-) but an example of the above can be seen in my JackarooIT product on my
website:
If a=jackaroo, b=net, c=au then the web site is a.b.c

Anyway I hope this helps and send me your problem if you want to.
 
Thank you Ian,

I really appreciate your ideas on this. So far my latest fix seems to be
working so I haven't worried about it.
But, I don't like it when something does not work the way it ought too, so I
may take another look at it when I get some time.

But, Access' events can be a real minefield, and the main form uses many of
them: On Current, Before Update, After Update, On Open, On Activate and On
Got Focus, so there are a potential for mayhem here.

If I get some spare time I might take you up on your offer, .but I just got
through repairing a corrupted back-end file for another of the stores, so I
am way behind right now. Windows on their server died and in the process it
really did a number on the back-end.

BTW, I did not design this application, my boss did and several other people
have contributed along the line, in most cases by adding features or fixing
problems, but without time to really figure out how it works.

Ragnar
 
Back
Top