Update my form from a Module function

[gmatriix]

Hello All,

I have this code below that is working however the calculation are updating on my form late.

Basically, I have some calculation that are performed on a "After Update" event on some controls on my form. I wanted it to do the calculation after I update the control on the form.

The code is in a module and I just call the function after update on the control But the form is not updating when I change the value in a field. I have to change the field again for it to update.

What am I missing? Any ideas?

Control

Call GeraAuditCalc
DoCmd.RefreshRecord

Function

Function GeraAuditCalc()

Dim fHrs As Double
Dim Ttl As Double
Dim Ttl1 As Double
Dim Ttl2 As Double
Dim Ttl3 As Double
Dim Ttl4 As Double
Dim Ttl5 As Double
Dim Ttl6 As Double
Dim Ttl7 As Double

Dim AuditID As Long

AuditID = Forms![Gera Audit]![ID]

fHrs = Forms![Gera Audit]![Mon-Hrs]

Forms![Gera Audit]![Tue-Hrs] = fHrs
Forms![Gera Audit]![Wed-Hrs] = fHrs
Forms![Gera Audit]![Thu-Hrs] = fHrs

    Forms![Gera Audit]![Fri-Hrs] = fHrs
    Forms![Gera Audit]![GMon-Hrs] = fHrs
    Forms![Gera Audit]![GTue-Hrs] = fHrs
    
Forms![Gera Audit]![GWed-Hrs] = fHrs
Forms![Gera Audit]![GThu-Hrs] = fHrs
Forms![Gera Audit]![GFri-Hrs] = fHrs

    Forms![Gera Audit]![Sat-Hrs] = fHrs
    Forms![Gera Audit]![Sun-Hrs] = fHrs
    Forms![Gera Audit]![GSat-Hrs] = fHrs
    
Forms![Gera Audit]![GSun-Hrs] = fHrs

Forms![Gera Audit]![GNew Sensors] = 0
Forms![Gera Audit]![GFix Hrs Adj Wk] = 0

' Totals Correct
Ttl = DSum("[% Ballast] * [Fixtures] * [Ball Mat] + [Ball Labor]", "tblMainGera", "[ID] = Forms![Gera Audit]![ID]")
Forms![Gera Audit]![Ttl Ballast M$] = Ttl

' Totals Correct
Ttl1 = DSum("[Input-Watts] * [Fix Hrs Wk] * 52 / 1000", "tblMainGera", "[ID] = Forms![Gera Audit]![ID]")
Forms![Gera Audit]![kWh Burn Yr] = Ttl1

' Totals Correct
Ttl3 = DSum("[Lamp $] + [Labor $] + [Equipment $]", "tblMainGera", "[ID] = Forms![Gera Audit]![ID]")
Forms![Gera Audit]![Ttl Lamp M$] = Ttl3 * Forms![Gera Audit]!Lamps

Ttl4 = DSum("[Mon-Hrs] + [Tue-Hrs] + [Wed-Hrs] + [Thu-Hrs] + [Fri-Hrs] + [Sat-Hrs] + [Sun-Hrs]", "tblMainGera", "[ID] = Forms![Gera Audit]![ID]")
Ttl5 = DSum("[GMon-Hrs] + [GTue-Hrs] + [GWed-Hrs] + [GThu-Hrs] + [GFri-Hrs] + [GSat-Hrs] + [GSun-Hrs]", "tblMainGera", "[ID] = Forms![Gera Audit]![ID]")

Forms![Gera Audit]![Wrk Hrs Wk] = Ttl4
Forms![Gera Audit]![GWrk Hrs Wk] = Ttl5

Ttl6 = DSum("[Fixtures] * [Wrk_Hrs_Wk]", "tblMainGera", "[ID] = Forms![Gera Audit]![ID]")
Forms![Gera Audit]![Fix Hrs Wk] = Ttl6

Forms![Gera Audit]![GAdj Fix Hrs Wk] = Forms![Gera Audit]![GWrk Hrs Wk] - Forms![Gera Audit]![GFix Hrs Adj Wk]

Ttl7 = DSum("[GInput-Watts] * [GFixtures] * [GAdj_Fix_Hrs_Wk] * 52 / 1000", "tblMainGera", "[ID] = Forms![Gera Audit]![ID]")

Forms![Gera Audit]![GkWh Burn Yr] = Ttl7

Forms![Gera Audit]![GkWh Saved yr] = Forms![Gera Audit]![kWh Burn Yr] - Ttl7

Forms![Gera Audit]![GPercentage of Energy Saved] = Forms![Gera Audit]![GkWh Saved yr] / Forms![Gera Audit]![kWh Burn Yr

End Function

Thanks

 

[MarkK]

The thing is, this code can only ever work with Forms![Gera Audit], so why put it in a module? A general rule to consider is that every object should be able to fend for itself, so minimize dependencies, but this routine is completely dependent in almost every line on the existence of the form [Gera Audit], so put it in that form.

Also, think about this: time is data, not structure. Monday and Tuesday should never be field names they should be data in fields. If you write an accounting system you don't have a field called $12.57, you have a field called Amount which contains a value of $12.57. Likewise, maybe you need a field called AuditDate that might contain a value of June 17, 2013, which happens to be a Monday, but to have a field called Monday is almost certainly a design problem.

hope this helps,

 

[gmatriix]

Thanks for the reply,

The reason I did this is because I wanted the calculations to update when a value is changed.

For example: If field1 = 3 and field2 = 2 and field3 add these to together if I change field1 to 5 then field3 with change to 7 "after update" Rather than put the code on each field I decide to just write one code and call the function on the fields I want updated with the calculations. I am still learning. Any Ideas?

Also, the field called Mon ect....has to be that way. It is a requirement for this project. I understand what you are saying but the users want it set up this way. If you saw the actual form, I think you would understand a little better.

 

[MarkK]

This reveals a data structure that is not normalized, which is to say that it breaks fundamental rules about how to store data in related database tables. If this was designed properly you could update these related values with one query, with one line of code, and it would be blisteringly fast. Obviously you are free to do things however you want.

AuditID = Forms![Gera Audit]![ID]

fHrs = Forms![Gera Audit]![Mon-Hrs]

Forms![Gera Audit]![Tue-Hrs] = fHrs
Forms![Gera Audit]![Wed-Hrs] = fHrs
Forms![Gera Audit]![Thu-Hrs] = fHrs

Forms![Gera Audit]![Fri-Hrs] = fHrs
Forms![Gera Audit]![GMon-Hrs] = fHrs
Forms![Gera Audit]![GTue-Hrs] = fHrs
    
Forms![Gera Audit]![GWed-Hrs] = fHrs
Forms![Gera Audit]![GThu-Hrs] = fHrs
Forms![Gera Audit]![GFri-Hrs] = fHrs

Forms![Gera Audit]![Sat-Hrs] = fHrs
Forms![Gera Audit]![Sun-Hrs] = fHrs
Forms![Gera Audit]![GSat-Hrs] = fHrs
    
Forms![Gera Audit]![GSun-Hrs] = fHrs

All the best.

 

[gmatriix]

Here is a video of the problem I am having

http://www.box.com/s/wqt9sarz4du7pdd9ub7r

Anyone...
Any Ideas?