Loop Form Records (1 Viewer)

[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

 

[Pat Hartman]

arne loaded some bullets in your gun but Minty gave you good advice. This piece of data should not be stored.

Think about what you are asking. You will have to loop through EVERY SINGLE mainform record EVERY SINGLE day to keep this field updated!!!!

And finally, do NOT use Now() when you really want Date(). Date() is just the date. Now() includes the time and will interfere with date checks.