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

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