Refresh DSUM Values

gheyman

Well-known Member
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

MrExcel MVP, Junior Admin
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
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
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
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
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
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
Re: Access: Refresh DSUM Values

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

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top