Sum() problem on ADP subform

[Br]

I'm sure this has been answered before but I can't dig up any posts that
give a good answer.. and I'm sure I've come across it before but forget how
I dealt with it.

I have an ADP (A2000, SQL2000). I have a form with a linked subform. The
subform list expense records and there is an unbound calculated textbox in
the form footer to Sum() the amounts. It works fine if the subform is
brought up by itself, but when it is used as a subform it displays #error.

It also used to not work when the subform was brought up by itself but I
worked out it didn't like another total textbox I had (I changed
=Sum([Amount]/12) to Sum([Amount])/12 to fix it). I have since removed all
other total textboxes so only the simple Sum() is left.

I could try and do the summing on the SQL server but I need the resulting
recordset to be updateable.

Since I only need the simple sum() (all others totals are derived from this
figure) I could just call a SP on the main form to get this total after an
update to the expense records is made... I don't like this approach but if
it works I guess it's the way I'll have to go.

Thoughts?
--
regards,

Br@dley

[Br]

Br@dley wrote:

I'm sure this has been answered before but I can't dig up any posts
that give a good answer.. and I'm sure I've come across it before but
forget how I dealt with it.

I have an ADP (A2000, SQL2000). I have a form with a linked subform.
The subform list expense records and there is an unbound calculated
textbox in the form footer to Sum() the amounts. It works fine if the
subform is brought up by itself, but when it is used as a subform it
displays #error.
It also used to not work when the subform was brought up by itself
but I worked out it didn't like another total textbox I had (I changed
=Sum([Amount]/12) to Sum([Amount])/12 to fix it). I have since
removed all other total textboxes so only the simple Sum() is left.

I could try and do the summing on the SQL server but I need the
resulting recordset to be updateable.

Since I only need the simple sum() (all others totals are derived
from this figure) I could just call a SP on the main form to get this
total after an update to the expense records is made... I don't like
this approach but if it works I guess it's the way I'll have to go.

Thoughts?

ps. If I click on the subform and press Shift-F9 the totals appear. But as
soon as I move off they return to #error
--
regards,

Br@dley

[Lyle Fairfield]

I asked Access to generate two auto-forms and added the totals control
as you describe, mine being =Sum([TotalAmount])/12. The error does not
occur. (Acc 2003, MS-SQL 2000)

Is it possible that you have some control, object, property, field
associated with the main form called Amount?

[Br]

Lyle Fairfield wrote:

I asked Access to generate two auto-forms and added the totals control
as you describe, mine being =Sum([TotalAmount])/12. The error does not
occur. (Acc 2003, MS-SQL 2000) Is it possible that you have some control, object, property, field
associated with the main form called Amount?

Nope. I thought of that.

Ok, I think I found the problem.

I have some code that calculates values based on the total expenses. I
issued a subform requery before the calc to make sure the data is saved. So,
it seems to be a timing issue (the same old thing in ADPs). The recordset
and calculated field aren't populated in time before they are used in the
calcs.

I don't think I need the requery.. but will have to test more to make sure
the calc work properly.

Thanks.
--
regards,

Br@dley

[Lyle Fairfield]

"Br@dley" <do***********@ google.com> wrote in
news:e1******** **@news-02.connect.com. au:

I have some code that calculates values based on the total expenses. I
issued a subform requery before the calc to make sure the data is
saved. So, it seems to be a timing issue (the same old thing in ADPs).

I'm glad I'm young and fresh; I've never met that old thing. Why would this
create an error? The wrong answer maybe ... but an error?

--
Lyle Fairfield

[Br]

Lyle Fairfield wrote:

"Br@dley" <do***********@ google.com> wrote in
news:e1******** **@news-02.connect.com. au:

I have some code that calculates values based on the total expenses.
I issued a subform requery before the calc to make sure the data is
saved. So, it seems to be a timing issue (the same old thing in
ADPs).
I'm glad I'm young and fresh; I've never met that old thing.

It's the same issue you get when you requery a form and try to search the
recordset and only the first 100 records or so are available to be
searched...
Why
would this create an error? The wrong answer maybe ... but an error?

No idea. Perhaps you can duplicate it and see:)
--
regards,

Br@dley

[Lyle Fairfield]

You're familiar with the solution posted to the general problem (not
yours) at
http://groups.google.ca/group/comp.d...8?dmode=source
?
Can you do something similar?

[Br]

Lyle Fairfield wrote:

You're familiar with the solution posted to the general problem (not
yours) at
http://groups.google.ca/group/comp.d...8?dmode=source
?
Can you do something similar?

My issue wasn't OnOpen but after a requery and then trying to reposition the
current record back to what it was (ie. mimmick a Refresh in an MDB). It's
the same cause though.
I reset the Recordsource of the form (Create RS, find record, then set
Me.Recordset = rs). This worked great, all records were returned "in time",
but my find wouldn't work at all anymore. I fixed this by using Set rs =
Me.Recordset.Cl one instead of Me.Recordsetclo ne.

Back to my current problem... I guess I could reset the RecordSource but it
seems that the Requery wasn't needed anyway....
--
regards,

Br@dley