Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Refresh DSUM Values

  1. #1
    Board Regular gheyman's Avatar
    Join Date
    Nov 2005
    Location
    Orlando, FL USA
    Posts
    1,632
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Refresh DSUM Values

    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?
    G Heyman
    Northrop Grumman - Laser Systems'
    Apopka, FL

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,793
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Access: Refresh DSUM Values

    Where on your Form is this calculation taking place?
    Have a look here: https://access-programmers.co.uk/for...d.php?t=192694
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular gheyman's Avatar
    Join Date
    Nov 2005
    Location
    Orlando, FL USA
    Posts
    1,632
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access: Refresh DSUM Values

    In the Body section, not the header or Footer
    G Heyman
    Northrop Grumman - Laser Systems'
    Apopka, FL

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,793
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default 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.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,474
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default 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 by xenou; Sep 23rd, 2019 at 09:16 AM.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  6. #6
    Board Regular gheyman's Avatar
    Join Date
    Nov 2005
    Location
    Orlando, FL USA
    Posts
    1,632
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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.
    G Heyman
    Northrop Grumman - Laser Systems'
    Apopka, FL

  7. #7
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,793
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default 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?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  8. #8
    Board Regular gheyman's Avatar
    Join Date
    Nov 2005
    Location
    Orlando, FL USA
    Posts
    1,632
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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.
    G Heyman
    Northrop Grumman - Laser Systems'
    Apopka, FL

  9. #9
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,793
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default 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 by Joe4; Sep 23rd, 2019 at 03:35 PM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  10. #10
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,474
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Access: Refresh DSUM Values

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

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •