Function

[aandress]

I have created the following function in VBA to run as an Event Procedure for the Fee1 field in a form... however... noting is showing up in the field... what am I doing wrong??? Here's the code I wrote:

Private Function Fee1_BeforeUpdate(Cancel As Currency)

If ManagedAssetValue >= 10000 <= 250000 Then
Fee1 = ManagedAssetValue * 0.02

ElseIf ManagedAssetValue >= 250001 <= 500000 Then
Fee1 = ((250000 * 0.02) + ((ManagedAssetValue - 250000) * 0.015))

ElseIf ManagedAssetValue >= 500001 Then
Fee1 = ((250000 * 0.02) + (250000 * 0.015) + ((ManagedAssetValue - 500000) * 0.01))
End If


End Function

Does the code need to be on the Control Source of the field for it to show up? Help! Thanks...

Alissa

 

[RV]

Alissa,

I'm not too familiar with functions and how to call them.
Your code however can use some small adaptions.

Try this:

Public Function Fee1_BeforeUpdate(ManagedAssetValue As Currency)
Dim Fee1 As Currency

If ManagedAssetValue >= 10000 Then
If ManagedAssetValue <= 250000 Then
Fee1 = ManagedAssetValue * 0.02
ElseIf ManagedAssetValue <= 500000 Then
Fee1 = ((250000 * 0.02) + ((ManagedAssetValue - 250000) * 0.015))
Else
Fee1 = ((250000 * 0.02) + (250000 * 0.015) + ((ManagedAssetValue - 500000) * 0.01))
End If
End If

End Function

I guess (as not being an expert on functions....) you could use an unbound textbox in your form for the fee, using your function in the Control Source like this:

=Function(Forms!YourFormName!NameOf YourTextboxContainingManagedAssetValue)

Beware that when declaring a function as Private (as you did) a procedure Function is only accessable for other procedures in the module in which the function was declared.


Suc6,

RV