Custom function

[DrONDeN]

I have a custom function as follows:

Public Function FilterIndex() As String

On Error Resume Next

Dim F1 As String
Dim F2 As String

Select Case Forms![Raw Data]![FI T1]
Case Is = "BLOCKED"
F1 = 0
Case Is = 0
F1 = 0
Case Is > 0
F1 = ((Left(Forms![Raw Data]![FI T1], 1) * 60) + (Right(Forms![Raw
Data]![FI T1], 2)))
End Select

Select Case Forms![Raw Data]![FI T2]
Case Is = "BLOCKED"
F2 = 0
Case Is = 0
F2 = 0
Case Is > 0
F2 = ((Left(Forms![Raw Data]![FI T2], 1) * 60) + (Right(Forms![Raw
Data]![FI T2], 2)))
End Select

Select Case F1 And F2
Case Is <> 0
FilterIndex = F2 - (F1 * 2)
Case Is = 0
FilterIndex = "BLOCKED"
End Select

End Function

It's aim is to turn a value entered into two controls from minutes and
seconds into seconds only and put the result in a new control. The
only problem is that the value returned by the function isn't
dynamically updated when the values are changed in the form. It only
seems to work when the form is closed then reopened. Is there
something I'm missing?

Thanks.

 

[DrONDeN]

There's feature in excel (application.volatile) is there something
similar for access?

 

[DrONDeN]

Hi Damon,

Thanks for your suggestion. I'd already used this method while trying
to get to to work. It does work, but when I move back and forward
between records, the textbox that shows the result of the function
doesn't update. Have you any ideas how I can get it to update as I
navigate back and forth through the records?

Regards,

Kenny

 

[George Nicholson]

the Form_Current event is triggered each time you change a record.

 

[DrONDeN]

Success!! The function takes the values from controls called F1T1 and
F1T2 and places the result into FIresult. On the lostfocus event of
F1T2 I simply put the code me.FIindex.requery