Update Query and DSum

[thart21]

I understand that it is not good practice to store calculated values in a table, but I am having a difficult time doing otherwise. I have a form with the field "volume". I have a table that stores monthly volumes for each product type. In my AfterUpdate of my "effectivemonth" field I have a DSum query go to the Volumes table and sum the volumes for the particular product based on the effective month. Each month all of the volumes change and I have to go into every record via my form one at a time and trigger the AfterUpdate event in order to get the field updated with the new volumes.
These volumes 1) have to be visible on my form at all times and 2) are used as the basis for most of my queries, pulling from the "volume" field in my table.

I am looking for a way to update the entire database volumes with one command button. Is this possible? I've thrown in my current AfterUpdate code to give you an idea of what I am doing now.

If Not IsNull(Me.fcstloc) Then

Me.prodvolume = DSum("[volume]", "qryMonthlyEngineVolumes", "[product] = '" & Forms!Projects!product & "'" & " And [year]= '" & Forms!Projects!cutinyear & "'" & " And [MonthID] Between " & Forms!Projects.MonthID.Column(0) & " And 12")
Me.prodvolume = DLookup("[volume]", "tblProjectDetails", "[projectid] = " & Forms!Projects!projectid & "")
If Me!otherloc = "Supplies forecast" Then
Me.prodvolume = DSum("[volume]", "qryMonthlySupplies", "[product] = '" & Forms!Projects!currentpn & "'" & " And [year]= '" & Forms!Projects!cutinyear & "'" & " And [MonthID] Between " & Forms!Projects.MonthID.Column(0) & " And 12")

End If
End If

Any ideas would be helpful.

Thanks

 

[namliam]

I would suggest you make a query that will calculate these values for you and then base a subform of that query. This way its allways up to date (you may have to do a requery after update...)

Alternatively you can put your update code in "on Update" but that may be to slow....