Calculated Field in a SubForm has Intermittent display failure

[Guest]

I have an Access program that displays Customer Info data into a Main form
and OrderID data into a subform in DataSheet View. The Totals field in the
Subform is calculated, i.e. =[Quantity]*[UnitPrice].

This works fine on my developement environment which includes a server set
up just as our client has. But at the client's site, the calculated field
dispay is intermittent, will appear if you click on it, disappear if you
scroll the field out of view, or two or three calculations will show, others
won't, sometimes they will appear 3 or 4 seconds later etc.

Although I am unable to repeat this behaviour in the development environment
I work on (which includes their full database), two systems I have set this
up on show the same problem.

For 2 days I have tried everything to fix this.

Has anyone run into this?

All systems are on the following

XP SP1 workstation
Access 2000 SP3
Win 2000 Server

Thanks

Tim Conway

 

[Allen Browne]

Tim, I'm not sure I have an answer for you on this one, but have seen
something similar. Client used A2000 (under Win2000), and their server was
WinNT. Intermittently, the calculated controls would show #Error, and the
drop-down lists would not load all values (e.g. only the first 30 or 50
where there should have been 200 items). There were 3 or 4 subforms, each
bringing a subtotal back to the main form which provided a grand total.

Upgrading all their workstations to JET 4 SP8 and Office 2000 SP3 did
improve the situation (esp with the drop-down lists), but we never
completely solved the #Error. I was unable to repro this on my (less busy)
network, so we concluded it was probably a timing issue: persumably Access
was timing out before it could load all the subform records, calculate the
subform totals, and bring the values back to the main form.

If anyone else has suggestions or experiences, we'd love to hear about them.

BTW, one thing worth checking out would be the service pack of the Win 2000
Server machine. It's crucial that Opportunistic Locking is off on the
server. From memory SP3 for Win 2000 Server did that.

 

[Guest]

Hi Allen

I installed Jet SP 8 and it now seems to be working. On the other hand, I'm
testing before anyone is in the office to affect network traffic.

Actually though, I also changed the approach and am doing the calculation in
the Query that the SubForm Datasheet displays, (i.e. Quaitity * Price AS
Total), then just put the Total Calculation as the control source for the
Total field on the Subform. Works, but of course, when I go back and forth
too much between forms and changing Customers to view, eventually I get
#error in that Calculated Total Field.

But that is what Jet SP 8 'seems' to have fixed.

We'll see. Thanks for the response

Tim

Tim, I'm not sure I have an answer for you on this one, but have seen
something similar. Client used A2000 (under Win2000), and their server was
WinNT. Intermittently, the calculated controls would show #Error, and the
drop-down lists would not load all values (e.g. only the first 30 or 50
where there should have been 200 items). There were 3 or 4 subforms, each
bringing a subtotal back to the main form which provided a grand total.

Upgrading all their workstations to JET 4 SP8 and Office 2000 SP3 did
improve the situation (esp with the drop-down lists), but we never
completely solved the #Error. I was unable to repro this on my (less busy)
network, so we concluded it was probably a timing issue: persumably Access
was timing out before it could load all the subform records, calculate the
subform totals, and bring the values back to the main form.

If anyone else has suggestions or experiences, we'd love to hear about them.

BTW, one thing worth checking out would be the service pack of the Win 2000
Server machine. It's crucial that Opportunistic Locking is off on the
server. From memory SP3 for Win 2000 Server did that.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have an Access program that displays Customer Info data into a Main form
and OrderID data into a subform in DataSheet View. The Totals field in
the
Subform is calculated, i.e. =[Quantity]*[UnitPrice].

This works fine on my developement environment which includes a server set
up just as our client has. But at the client's site, the calculated field
dispay is intermittent, will appear if you click on it, disappear if you
scroll the field out of view, or two or three calculations will show,
others
won't, sometimes they will appear 3 or 4 seconds later etc.

Although I am unable to repeat this behaviour in the development
environment
I work on (which includes their full database), two systems I have set
this
up on show the same problem.

For 2 days I have tried everything to fix this.

Has anyone run into this?

All systems are on the following

XP SP1 workstation
Access 2000 SP3
Win 2000 Server

Thanks

Tim Conway