Sum() problem on ADP subform

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

re: Sum() problem on ADP subform

Br@dley wrote:[color=blue]
> 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?[/color]

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

re: Sum() problem on ADP subform

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?

re: Sum() problem on ADP subform

Lyle Fairfield wrote:[color=blue]
> 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)[/color]
[color=blue]
> Is it possible that you have some control, object, property, field
> associated with the main form called Amount?[/color]

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

re: Sum() problem on ADP subform

"Br@dley" <dontlookforme@google.com> wrote in
news:e1f4l7$av1$1@news-02.connect.com.au:

[color=blue]
> 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).[/color]

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

re: Sum() problem on ADP subform

Lyle Fairfield wrote:[color=blue]
> "Br@dley" <dontlookforme@google.com> wrote in
> news:e1f4l7$av1$1@news-02.connect.com.au:
>
>[color=green]
>> 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).[/color]
>
> I'm glad I'm young and fresh; I've never met that old thing.[/color]

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...
[color=blue]
> Why
> would this create an error? The wrong answer maybe ... but an error?[/color]

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

Br@dley

re: Sum() problem on ADP subform

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?

re: Sum() problem on ADP subform

Lyle Fairfield wrote:[color=blue]
> 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?[/color]

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.Clone instead of Me.Recordsetclone.

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