Trigger procedure each time you navigate through records

[DrONDeN]

Hi everyone.

I have three controls on a form, FI_T1, FI_T2 and FIresult. FI_T1 and
FI_T2 are bound to fields in a table, but FIresult is calculated by
the procedure below. (I have very little knowledge on VB, so any
comments on how to code this better would be v. much appreciated lol)


Both FI_T2 and FI_T2 store time values in MM:SS format. The aim of
the
procedure is to turn the values in FI_T1 and FI_T2 into seconds. Once
turned into seconds, FI_T1 is then multiplied by 2 and subtracted
from
FI_T2 and the result placed into the FIresult field.


If the value 99:99 is typed into FI_T1 then I would like all three
controls to equal 'BLOCKED'. If 99:99 is entered into FI_T2 then I
would like just FI_T2 and FIresult to change to 'BLOCKED'.


I currently have the procedure tied to the LostFocus event of FI_T2,
but I would like this procedure to be triggered every time I move
forward to a new record, and all three fields updated accordingly.
I've tried, but I can't figure out how to do this. If I knew how to
turn the procedure into a formula, then I would just make a
calculated
control, but I don't know how to do this either.


Any help would much appreciated, or even feedback on how to make the
procedure a bit more tidy.


Thanks in advance.


Private Sub FI_T2_LostFocus()
On Error GoTo FIerror


Dim F1 As Integer
Dim F2 As Integer


F1 = ((Left(Forms![raw data]![FI T1], 1) * 60) + (Right(Forms![raw
data]![FI T1], 2)))
F2 = ((Left(Forms![raw data]![FI T2], 1) * 60) + (Right(Forms![raw
data]![FI T2], 2)))


Select Case F1
Case Is > 0
Forms![raw data]![FIresult] = F2 - F1 * 2


Case Is = 0
Forms![raw data]![FIresult] = "BLOCKED"
Forms![raw data]![FI T1] = "BLOCKED"
Forms![raw data]![FI T2] = "BLOCKED"


Case F2 = 0
Forms![raw data]![FIresult] = "BLOCKED"
Forms![raw data]![FI T2] = "BLOCKED"


End Select


Select Case F2
Case Is < F1
Forms![raw data]![FI T2] = "BLOCKED"
Forms![raw data]![FIresult] = "BLOCKED"
End Select


FIerror:
Exit Sub


End Sub

 

[Klatuu]

Create a function in your form module that performs the calculation and
returns the results. Then make the function the control source of F1Results.
=MyFunctionName()

Just type it in the control source property box including the = sign and the
parenthises.

 

[DrONDeN]

Hi Dave,.

Thank you for your reply. I didn't know it was possible to make a
function the source of a control, so thank you for teaching me
something new. Your reply makes perfect sense, although I'm not sure
how to create a function in my form module, or even how to access the
form module in the first place. Is it somewhere in the form properties
box?

Regards,

Kenny.

Create a function in your form module that performs the calculation and
returns the results.  Then make the function the control source of F1Results.
=MyFunctionName()

Just type it in the control source property box including the = sign andthe
parenthises.
--
Dave Hargis, Microsoft Access MVP

Hi everyone.

I have three controls on a form, FI_T1, FI_T2 and FIresult. FI_T1 and
FI_T2 are bound to fields in a table, but FIresult is calculated by
the procedure below. (I have very little knowledge on VB, so any
comments on how to code this better would be v. much appreciated lol)

Both FI_T2 and FI_T2 store time values in MM:SS format. The aim of
the
procedure is to turn the values in FI_T1 and FI_T2 into seconds. Once
turned into seconds, FI_T1 is then multiplied by 2 and subtracted
from
FI_T2 and the result placed into the FIresult field.

If the value 99:99 is typed into FI_T1 then I would like all three
controls to equal 'BLOCKED'. If 99:99 is entered into FI_T2 then I
would like just FI_T2 and FIresult to change to 'BLOCKED'.

I currently have the procedure tied to the LostFocus event of FI_T2,
but I would like this procedure to be triggered every time I move
forward to a new record, and all three fields updated accordingly.
I've tried, but I can't figure out how to do this. If I knew how to
turn the procedure into a formula, then I would just make a
calculated
control, but I don't know how to do this either.

Any help would much appreciated, or even feedback on how to make the
procedure a bit more tidy.

Thanks in advance.

Private Sub FI_T2_LostFocus()
On Error GoTo FIerror

Dim F1 As Integer
Dim F2 As Integer

F1 = ((Left(Forms![raw data]![FI T1], 1) * 60) + (Right(Forms![raw
data]![FI T1], 2)))
F2 = ((Left(Forms![raw data]![FI T2], 1) * 60) + (Right(Forms![raw
data]![FI T2], 2)))

Select Case F1
    Case Is > 0
        Forms![raw data]![FIresult] = F2 - F1 * 2

    Case Is = 0
        Forms![raw data]![FIresult] = "BLOCKED"
        Forms![raw data]![FI T1] = "BLOCKED"
        Forms![raw data]![FI T2] = "BLOCKED"

Case F2 = 0
        Forms![raw data]![FIresult] = "BLOCKED"
        Forms![raw data]![FI T2] = "BLOCKED"

End Select

Select Case F2
    Case Is < F1
        Forms![raw data]![FI T2] = "BLOCKED"
        Forms![raw data]![FIresult] = "BLOCKED"
End Select

FIerror:
    Exit Sub

End Sub- Hide quoted text -

- Show quoted text -