Refresh DSUM Values

[gheyman]

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?

 

[Joe4]

Re: Access: Refresh DSUM Values

Where on your Form is this calculation taking place?
Have a look here: https://access-programmers.co.uk/forums/showthread.php?t=192694

 

[gheyman]

Re: Access: Refresh DSUM Values

In the Body section, not the header or Footer

 

[Joe4]

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]

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]

 

[gheyman]

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]

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]

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]

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).

 

[xenou]

Re: Access: Refresh DSUM Values

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