By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,594 Members | 3,017 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,594 IT Pros & Developers. It's quick & easy.

#Error in calculated field in MDE (not MDB)

P: n/a
Subform is based on a single-table query that contains a calculated field:
Amount: Round(CCur(Nz([Qty]*[CostEach],0)),2)

Continuous subform displays this field in a text box named Amount.
As user enters new rows into subform, all rows show #Error in this box.
This only happens until they close the form, or move the main form to a
different record.

Worse, it only happens on the client's machine in the MDE.
The identical MDB file shows the calculation correctly.
Client is running Access 2000 SP1 on Windows 2000.
Even MDE shows correctly on my dev. machine (A2000 SP3 on WinXP).

No other calculated fields on the subform could interfere.
Amount text box has no Default Value. Its Format is Currency.

There are actually 3 of these subforms selecting from the same table (parts,
labor, expenses), and they all exhibit the same problem.

Split app (f.e. local, b.e. on server). Only 3 code references (Access, VBA,
DAO). All present and registered correctly.

What could cause this to display wrongly in an MDE but correctly in an MDB?
Nov 12 '05 #1
Share this Question
Share on Google+
14 Replies


P: n/a
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:3f**********************@freenews.iinet.net.a u...
Subform is based on a single-table query that contains a calculated field:
Amount: Round(CCur(Nz([Qty]*[CostEach],0)),2)

Continuous subform displays this field in a text box named Amount.
As user enters new rows into subform, all rows show #Error in this box.
This only happens until they close the form, or move the main form to a
different record.

Worse, it only happens on the client's machine in the MDE.
The identical MDB file shows the calculation correctly.
Client is running Access 2000 SP1 on Windows 2000.
Even MDE shows correctly on my dev. machine (A2000 SP3 on WinXP).

No other calculated fields on the subform could interfere.
Amount text box has no Default Value. Its Format is Currency.

There are actually 3 of these subforms selecting from the same table (parts, labor, expenses), and they all exhibit the same problem.

Split app (f.e. local, b.e. on server). Only 3 code references (Access, VBA, DAO). All present and registered correctly.

What could cause this to display wrongly in an MDE but correctly in an

MDB?

Does it work if you load an MDB on the client site? I'd suspect the
difference in service packs would be the culprit rather than the fact that
it's an MDE.
Nov 12 '05 #2

P: n/a
The MDB does not have the problem, even on the client's machine.

"Trev@Work" <bouncer@localhost> wrote in message
news:3f***********************@news.easynet.co.uk. ..
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:3f**********************@freenews.iinet.net.a u...
Subform is based on a single-table query that contains a calculated field: Amount: Round(CCur(Nz([Qty]*[CostEach],0)),2)

Continuous subform displays this field in a text box named Amount.
As user enters new rows into subform, all rows show #Error in this box.
This only happens until they close the form, or move the main form to a
different record.

Worse, it only happens on the client's machine in the MDE.
The identical MDB file shows the calculation correctly.
Client is running Access 2000 SP1 on Windows 2000.
Even MDE shows correctly on my dev. machine (A2000 SP3 on WinXP).

No other calculated fields on the subform could interfere.
Amount text box has no Default Value. Its Format is Currency.

There are actually 3 of these subforms selecting from the same table

(parts,
labor, expenses), and they all exhibit the same problem.

Split app (f.e. local, b.e. on server). Only 3 code references (Access,

VBA,
DAO). All present and registered correctly.

What could cause this to display wrongly in an MDE but correctly in an

MDB?

Does it work if you load an MDB on the client site? I'd suspect the
difference in service packs would be the culprit rather than the fact that
it's an MDE.

Nov 12 '05 #3

P: n/a
On Mon, 1 Dec 2003 23:25:37 +0800 in comp.databases.ms-access, "Allen
Browne" <Al*********@SeeSig.Invalid> wrote:
The MDB does not have the problem, even on the client's machine.


I've tried re-creating this scenario on a O2K SP1 machine but my test
worked ok. What data types are involved in the table?

--
A)bort, R)etry, I)nfluence with large hammer.
Nov 12 '05 #4

P: n/a
Qty Number (Double)
CostEach Currency
"Trevor Best" <bouncer@localhost> wrote in message
news:ra********************************@4ax.com...
On Mon, 1 Dec 2003 23:25:37 +0800 in comp.databases.ms-access, "Allen
Browne" <Al*********@SeeSig.Invalid> wrote:
The MDB does not have the problem, even on the client's machine.


I've tried re-creating this scenario on a O2K SP1 machine but my test
worked ok. What data types are involved in the table?

--
A)bort, R)etry, I)nfluence with large hammer.

Nov 12 '05 #5

P: n/a
TC
Does it make any difference whether you create the MDE on your PC or the
client's PC?

TC
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:3f**********************@freenews.iinet.net.a u...
Qty Number (Double)
CostEach Currency
"Trevor Best" <bouncer@localhost> wrote in message
news:ra********************************@4ax.com...
On Mon, 1 Dec 2003 23:25:37 +0800 in comp.databases.ms-access, "Allen
Browne" <Al*********@SeeSig.Invalid> wrote:
The MDB does not have the problem, even on the client's machine.


I've tried re-creating this scenario on a O2K SP1 machine but my test
worked ok. What data types are involved in the table?

--
A)bort, R)etry, I)nfluence with large hammer.


Nov 12 '05 #6

P: n/a
Interesting question, TC. I did not try that, but have had experience where
creating the MDE on the client machine made a difference to stability in
A2000 SP1.

However, I'm starting to suspect the network at the client site.
We are now seeing another issue where a combo fails to display all the
records in the list. Changing the option under Tools | Options | Edit/Find |
"Don't display lists with more than" from 1000 to 10000 temporarily solved
the problem, but after a restart the problem is back. There are only 41
items in the combo's RowSource.
"TC" <a@b.c.d> wrote in message news:1070333116.340442@teuthos...
Does it make any difference whether you create the MDE on your PC or the
client's PC?

TC
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:3f**********************@freenews.iinet.net.a u...
Qty Number (Double)
CostEach Currency
"Trevor Best" <bouncer@localhost> wrote in message
news:ra********************************@4ax.com...
On Mon, 1 Dec 2003 23:25:37 +0800 in comp.databases.ms-access, "Allen
Browne" <Al*********@SeeSig.Invalid> wrote:

>The MDB does not have the problem, even on the client's machine.

I've tried re-creating this scenario on a O2K SP1 machine but my test
worked ok. What data types are involved in the table?

Nov 12 '05 #7

P: n/a
TC
Seems very strange. The reason I asked about where the MDE was created, is
that (as you know) the references are frozen in the MDE. So if they are
different on the client PC to your PC, that could cause some problems. This
would come to light if creating the MDE on the client PC caused the problem
to go away.

Cheers,
TC
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:3f**********************@freenews.iinet.net.a u...
Interesting question, TC. I did not try that, but have had experience where creating the MDE on the client machine made a difference to stability in
A2000 SP1.

However, I'm starting to suspect the network at the client site.
We are now seeing another issue where a combo fails to display all the
records in the list. Changing the option under Tools | Options | Edit/Find | "Don't display lists with more than" from 1000 to 10000 temporarily solved
the problem, but after a restart the problem is back. There are only 41
items in the combo's RowSource.
"TC" <a@b.c.d> wrote in message news:1070333116.340442@teuthos...
Does it make any difference whether you create the MDE on your PC or the
client's PC?

TC
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:3f**********************@freenews.iinet.net.a u...
Qty Number (Double)
CostEach Currency
"Trevor Best" <bouncer@localhost> wrote in message
news:ra********************************@4ax.com...
> On Mon, 1 Dec 2003 23:25:37 +0800 in comp.databases.ms-access, "Allen > Browne" <Al*********@SeeSig.Invalid> wrote:
>
> >The MDB does not have the problem, even on the client's machine.
>
> I've tried re-creating this scenario on a O2K SP1 machine but my test > worked ok. What data types are involved in the table?


Nov 12 '05 #8

P: n/a
CDB
Is it worth looking at RAM, swap file, HD fragmentation, No. of apps open,
etc.Even your test with an .mdb could have been influenced by such
considerations.

Clive
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:3f**********************@freenews.iinet.net.a u...
Interesting question, TC. I did not try that, but have had experience where creating the MDE on the client machine made a difference to stability in
A2000 SP1.

However, I'm starting to suspect the network at the client site.
We are now seeing another issue where a combo fails to display all the
records in the list. Changing the option under Tools | Options | Edit/Find | "Don't display lists with more than" from 1000 to 10000 temporarily solved
the problem, but after a restart the problem is back. There are only 41
items in the combo's RowSource.


Nov 12 '05 #9

P: n/a
On Tue, 2 Dec 2003 09:47:20 +0800 in comp.databases.ms-access, "Allen
Browne" <Al*********@SeeSig.Invalid> wrote:
Qty Number (Double)
CostEach Currency


Still can't reproduce that error, mind you the machine I used was
clean (just ran Seti@home) until I installed Office onto it. As a
general rule, we usualy insist on clients getting themselves up to the
same SP level as our development machines, that way we can have a
level playing field as far as we can go and rule out any
inconsistencies that may be caused by different levels of patching.

Thinking of Clive's comments, I had a zip file that was corrupt every
time I downloaded it. In the end I renamed the existing download
instead of overwriting it and the next download was fine. Turned out
to be a bad sector on the disk.

Does the error still occur if you put your calculation into the form's
control rather than the query? Moreover does the error stil occur if
you run the query on it's own?

--
A)bort, R)etry, I)nfluence with large hammer.
Nov 12 '05 #10

P: n/a
Thanks very much for testing, Trevor. It's very encouraging to know that you
could not repro the error either.

What we are planning at this stage is to install a runtime tomorrow. If I
understand correctly, this guarantees there are no differences between their
service packs/dlls and mine.

If the problem still exists and the client's drive is not corrupt, the only
remaining differences are network ones. (Their server is NT4SP6a, running on
a fibre network - both different from my dev environment.)

--
"Trevor Best" <bouncer@localhost> wrote in message
news:83********************************@4ax.com...
On Tue, 2 Dec 2003 09:47:20 +0800 in comp.databases.ms-access, "Allen
Browne" <Al*********@SeeSig.Invalid> wrote:
Qty Number (Double)
CostEach Currency


Still can't reproduce that error, mind you the machine I used was
clean (just ran Seti@home) until I installed Office onto it. As a
general rule, we usualy insist on clients getting themselves up to the
same SP level as our development machines, that way we can have a
level playing field as far as we can go and rule out any
inconsistencies that may be caused by different levels of patching.

Thinking of Clive's comments, I had a zip file that was corrupt every
time I downloaded it. In the end I renamed the existing download
instead of overwriting it and the next download was fine. Turned out
to be a bad sector on the disk.

Does the error still occur if you put your calculation into the form's
control rather than the query? Moreover does the error stil occur if
you run the query on it's own?

Nov 12 '05 #11

P: n/a
al***@delete.wave.co.nz (CDB) wrote in
<bq**********@news.wave.co.nz>:
Is it worth looking at RAM, swap file, HD fragmentation, No. of
apps open, etc.Even your test with an .mdb could have been
influenced by such considerations.


Clean out the temp folder, too.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #12

P: n/a
On Tue, 2 Dec 2003 17:26:33 +0800 in comp.databases.ms-access, "Allen
Browne" <Al*********@SeeSig.Invalid> wrote:
Thanks very much for testing, Trevor. It's very encouraging to know that you
could not repro the error either.

What we are planning at this stage is to install a runtime tomorrow. If I
understand correctly, this guarantees there are no differences between their
service packs/dlls and mine.
Just a thought, will that not upgrade the DLLs used by their own
installation of Access up to your SP level without upgrading their
msaccess.exe thereby introducing an unknown environment for them?
If the problem still exists and the client's drive is not corrupt, the only
remaining differences are network ones. (Their server is NT4SP6a, running on
a fibre network - both different from my dev environment.)


What about Jet service packs (as installed by WindowsUpdate nowadays).

--
A)bort, R)etry, I)nfluence with large hammer.
Nov 12 '05 #13

P: n/a
Have you tried creating the MDE on the client's PC yet? I still think it
would be significant if the MDE created on your PC did not work on the
client's PC, but the MDE created on his one, did.

TC
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:3f**********************@freenews.iinet.net.a u...
Thanks very much for testing, Trevor. It's very encouraging to know that you could not repro the error either.

What we are planning at this stage is to install a runtime tomorrow. If I
understand correctly, this guarantees there are no differences between their service packs/dlls and mine.

If the problem still exists and the client's drive is not corrupt, the only remaining differences are network ones. (Their server is NT4SP6a, running on a fibre network - both different from my dev environment.)

--
"Trevor Best" <bouncer@localhost> wrote in message
news:83********************************@4ax.com...
On Tue, 2 Dec 2003 09:47:20 +0800 in comp.databases.ms-access, "Allen
Browne" <Al*********@SeeSig.Invalid> wrote:
Qty Number (Double)
CostEach Currency


Still can't reproduce that error, mind you the machine I used was
clean (just ran Seti@home) until I installed Office onto it. As a
general rule, we usualy insist on clients getting themselves up to the
same SP level as our development machines, that way we can have a
level playing field as far as we can go and rule out any
inconsistencies that may be caused by different levels of patching.

Thinking of Clive's comments, I had a zip file that was corrupt every
time I downloaded it. In the end I renamed the existing download
instead of overwriting it and the next download was fine. Turned out
to be a bad sector on the disk.

Does the error still occur if you put your calculation into the form's
control rather than the query? Moreover does the error stil occur if
you run the query on it's own?


Nov 12 '05 #14

P: n/a
Thanks for the feedback and suggestions on this.

We are writing this experience down to a network issue.
Moving the data file onto the local hard disk solved both issues (#Error is
calculated fields, and items absent from drop-down list).

Further, sharing the data mdb locally on one of the Windows 2000 machines
caused the software to run correctly on all 3 computers (presumably plugged
into the same hub). Their main LAN is quite extensive with the server in
another building (and parking lots in between), so the problem would appear
to be unrelated to Access itself.

Thanks again for your help. This n.g. is a great resource.

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:3f**********************@freenews.iinet.net.a u...
Subform is based on a single-table query that contains a calculated field:
Amount: Round(CCur(Nz([Qty]*[CostEach],0)),2)

Continuous subform displays this field in a text box named Amount.
As user enters new rows into subform, all rows show #Error in this box.
This only happens until they close the form, or move the main form to a
different record.

Worse, it only happens on the client's machine in the MDE.
The identical MDB file shows the calculation correctly.
Client is running Access 2000 SP1 on Windows 2000.
Even MDE shows correctly on my dev. machine (A2000 SP3 on WinXP).

No other calculated fields on the subform could interfere.
Amount text box has no Default Value. Its Format is Currency.

There are actually 3 of these subforms selecting from the same table (parts, labor, expenses), and they all exhibit the same problem.

Split app (f.e. local, b.e. on server). Only 3 code references (Access, VBA, DAO). All present and registered correctly.

What could cause this to display wrongly in an MDE but correctly in an

MDB?
Nov 12 '05 #15

This discussion thread is closed

Replies have been disabled for this discussion.