CODEWITHSUNDEEP
ms-accessvba
WKI
WKI

Reputation: 215

Looping through the entire record every time a form loads

The code is supposed to check if a filed "YearBigins" is empty and request an input then checks if the date value in "YearBigins" filed in older than one year and sets the value in the "counter" filed to 0. I have the following codes but does not work:

Private Sub Form_Load()
Me.Caption = Date
YearBegins_AfterUpdate
End Sub

Private Sub YearBegins_AfterUpdate()

Dim rst As Recordset
Do While Not rst.EOF
If IsNull(rst.Fields("YearBegins").Value) Then
YearBegins = InputBox("Please Enter the Beginning of counter Year  Date as: 
dd/mm/yyyy")
Else: YearBegins = YearBegins.Value
End If
rst.MoveNext
If (DateTime.Now - YearBegins.Value) < 365 Then
counter.Value = 0
End If

Loop

End Sub

Upvotes: 0

Views: 51

Answers (1)

Brad
Brad

Reputation: 12245

You're missing the part where you actually update the recordset.

Private Sub Form_Load()
    Me.Caption = Date
    YearBegins_AfterUpdate
End Sub

Private Sub YearBegins_AfterUpdate()

    Dim rst As Recordset
    Do While Not rst.EOF
        If IsNull(rst.Fields("YearBegins").Value) Then
            YearBegins = InputBox("Please Enter the Beginning of counter Year  Date as: dd/mm/yyyy")
            rst.Edit  '<---
            rst("YearBegins") = cdate(YearBegins) '<---
            rst.Update '<---
        Else
            YearBegins = YearBegins.Value
        End If
        rst.MoveNext
        If (DateTime.Now - YearBegins.Value) < 365 Then
            counter.Value = 0
        End If

    Loop

End Sub

With that said there are better ways to do this. You could open another form with only the data you want to update which will allow you to give your users some context. Just having a popup dialog with no data validation doesn't let them even really see what record they're updating. How do they know what value to put in?

It would be better if you could refactor your code in YearBegins_AfterUpdate to a new function and call that from Form_Load instead of calling an event handler like YearBegins_AfterUpdate.

I don't see where you're setting rst. You go from Dim rst As Recordset to using it but given the code you provided rst will always be null.

Upvotes: 2

Related Questions