Refresh DSUM Values

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,678
I have several DSUM fields on a form. The fields are looking for values from a Query:

=DSum("MONTH0_Active","qry_OnTimeDelivery_MetricData")

But unless I have the query open at the same time as I have the Form open, I don't get values. How can I get this to calculate without having to open the query - or is that not possible?
 

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,678
Re: Access: Refresh DSUM Values

In the Body section, not the header or Footer
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,168
Office Version
365
Platform
Windows
Re: Access: Refresh DSUM Values

You said that you have "several" DSUMs on your Form. Is it just this one having this issue, or all of them?

I would recommend doing the calculation in the query you are using for the data source of the form, and then just pulling that field. I always have more luck doing that than trying to do the calculation right on the form itself.
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,526
Re: Access: Refresh DSUM Values

Note that if the query can run without the form being open then it is "independent" of the form. If it requires the form to be open then the query is not independent ...

for instance, if the query refers to a field on the form:
select * from table where Month = [Forms]![MyForm]![MonthField]
 
Last edited:

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,678
Re: Access: Refresh DSUM Values

Right now the Query is independent and to answer Joe's question there are multiple formulas on the form just like the one states above - but calculating something a little different.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,168
Office Version
365
Platform
Windows
Re: Access: Refresh DSUM Values

Do the other DSUM calcs work?

Do you give thought to doing the calculation at the query level instead of the form level?
 

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,678
Re: Access: Refresh DSUM Values

I'm not sure what you mean by at the Query Level. No none of the DSUMs Calc unless the Query is open at the same time the form is. So my solution for now is to run code to open the query whenever someone opens the form.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,168
Office Version
365
Platform
Windows
Re: Access: Refresh DSUM Values

So my solution for now is to run code to open the query whenever someone opens the form.
That shouldn't be necessary.

What is the Data Source for your Form (i.e. what is the Form based on)?
I assume it is a bound form, if you are returning data (I wouldn't think that they are ALL calculated fields).
 
Last edited:

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,526
Re: Access: Refresh DSUM Values

ordinarily shouldn't be necessary to open the query if it is independent. What is the query?
 

Forum statistics

Threads
1,078,394
Messages
5,339,939
Members
399,340
Latest member
JasonT903

Some videos you may like

This Week's Hot Topics

Top