473,327 Members | 2,055 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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
Apr 11 '06 #1
7 3185
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
Apr 11 '06 #2
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?

Apr 11 '06 #3
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
Apr 11 '06 #4
"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
Apr 11 '06 #5
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
Apr 11 '06 #6
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?

Apr 11 '06 #7
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.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
Apr 11 '06 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

15
by: U N Me | last post by:
I have a continuous form that lists records from a query. One of the columns is an amount field, In the footer of the form I place a text box that displays the total (sum) of the amount. The...
2
by: AccessHelp32 | last post by:
Hi All, I have a subform attached to a main form in which both forms are linked through a Project ID field. When I change the Project ID field on the main form, only the records associated with...
2
by: apartain | last post by:
In the detail of my report I am extracting a sum from a hidden subform via the following: =IIf(.Report.HasData=True,.Report!Text19,0) Where Text19 is the sum of the items in the subform. I...
1
by: lawton | last post by:
Source: this is an access 2003 question My knowledge level: reading books, internet, and trial & error; no formal training I'm trying to get a running sum of what's filtered in a subform which is...
2
by: post | last post by:
Sorry that this is rather basic. But I have tried the various suggestions given in this group and cannot get them to work. I have a main form, for simplicity called "Main". On it are two...
14
by: David Grist | last post by:
Hello, Any help would be appreciated!!! I need to sum AMOUNTTEND on a receipts subform that meet a certain criteria. It has to be something like. =sum() where ="tour installment"
2
by: Andreas | last post by:
Hello together, I want to calculate a sum in subform from a subsubform (sounds complicated?) but I get an #error but... see below My database: I have a main form with members, each members...
4
by: csolomon | last post by:
Good Morning, I am trying to get the sum of values on a subform, that is a continuous form. The name of the main form is : F_MixDesign Subform is : SF_MixSample Subform control name is:...
24
by: bkberg05 | last post by:
Hi - I have a form called mainForm that has a subform called subForm. The subform has a numeric field called subCost. There are many subForm records for every mainForm record. On the main form,...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.