vba syntax / order question

[Geordie2008]

Morning all,

I have the following VBA to set a value in a form based on the calculation of two other variables within the form.

Sub Test1()

SetValue
Item: [Total FTE]
Expression: ([Permanent Staff FTE] +[Temporary Staff FTE])

End Sub

(I used the microsoft help paegs to derive this code.)

I then have sub's that call this function when the form is opened or either of the variables are updated:

Sub Form_Current()

Call Test1

End Sub

Private Sub Permanent_Staff_FTE_AfterUpdate()

Call Test1

End Sub

Private Sub Temporary_Staff_FTE_AfterUpdate()

Call Test1

End Sub

None of this is working... It falls over at the "SetValue" (second line!) of code!

Can anyone let me know where im going wrong.... I have all of this code stored in the actual form itself. (Not a public Module)

Thanks,
Mandy

 

[RuralGuy]

Your Test1 is not valid VBA code. It looks like it was intended to be for a macro. VBA code would look more like:

Sub Test1()

   [Total FTE] = [Permanent Staff FTE] + [Temporary Staff FTE]

End Sub

 

[Geordie2008]

Ah... I read "macro" as being a VB module....

Have just used the macro button for the forst time and it works great.

Thanks for the nudge in the right direction!

Mandy

 

[RuralGuy]

Glad I could help.

 

[Geordie2008]

Out of interest....

when [Permanent Staff FTE] or [Temporary Staff FTE] is left blank [NULL] the calculated field [Total_FTE] stays blank.... I tried setting the default value of these fields to 0, but this didn't work... (it ignored it!)

I would like it so that it caculates the total regardless of a field being blank...

Is there anything I can do?

Regards
Mandy

 

[RuralGuy]

Null fields propagate. Null plus anything = Null. Try using the Nz([YourField],0) function.

 

[Geordie2008]

Would I put that in the "default value" under properties of that field?

 

[Geordie2008]

ah... tis fine...

I ahve set the default value as 0 and the validation rule as Is Not Null and that seems to do the trick!

Thanks,
Mandy

 

[RuralGuy]

Glad you got it sorted.