Refresh DSUM Values

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
2,332
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Re: Access: Refresh DSUM Values

In the Body section, not the header or Footer
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
Re: Access: Refresh DSUM Values

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

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top