Loop Form Records

[Jonny45wakey]

Hi I have a form called frmMasterData which contains the following code in the On Timer Event:-

Private Sub Form_Timer()
Me.Text33.Requery
If Me.Skill_Renewal_Date.Value >= Text33.Value Then
Me.Training_Status.Value = "In-Date"
Else
Me.Training_Status.Value = "Expired"
End If
End Sub

The Textbox called "Text33" contains the function =Now()

I want the form to loop through each record and compare the "Skill_Renewal_Date" value against "Text33" value and then update
"Training_Status" value accordingly as above.

i would like this to perform on the OnOpen Event of the form.

Any help much appreciated

Regards

Jonny

 

[Minty]

You shouldn't store this value - simply calculate in the forms recordsouce query, then it is always accurate.
As you have discovered it needs constant updating.

 

[arnelgp]

Private Sub Form_Timer()
    Dim lngTimer As Long
    lngTimer = Me.TimerInterval
    Me.TimerInterval = 0
    Me.Text33.Requery
    With Me.Recorsetclone
        If Not (.BOF And .EOF) Then
            .MoveFirst
        End If
        Do Until .EOF
            .Edit
            If Nz(![Skill_Renewal_Date], 1) >= Text33.Value Then
                ![Training_Status] = "In-Date"
            Else
                ![Training_Status] = "Expired"
            End If
            .Update
            .MoveNext
        Loop
    End With
    Me.TimerInterval = lngTimer
End Sub