Automatically change text box based on date changes

[jlb4350]

Hello. I have a problem that you all may be able to help me with. I have an access form and on that form are 2 fields, one is called "Status" and the other "Date Last Reviewed".

What I would like to do is have the Status field set as a text box that will automatically change from "Current" to "Review" when the date in the Date Last Reviewed field reaches a specific date.

For example, when a user inputs that they reviewed the document today, then the Status field changes to "Current". When that date reaches 30 days or older than the current date, the Status field automatically changes to "Review".



Could someone steer me in the right direction to accomplish this? I'm sure there will need to be some kind of code or expression to make this happen, but I'm at a loss.

Thanks in advance!

[Bob Fitz]

Hi

The textbox called "Status" should NOT normally be bound to the field of a table because this is "calculated" data.

Set the Control Source property of the textbox called "Status" to:
=IIf(DateDiff("d",Date(),[Date_Last_Reviewed])>=30,"Review","Current")

The AfterUpdate event of the other textbox called "Date_Last_Reviewed"
should be as follows

 
Private Sub Date_Last_Reviewed_AfterUpdate()
Me.Status.Requery
End Sub

[khalid]

Hi and welcome to AF.net,

So you have a date field on the form which is recorded to current date if someone enter the data and the status become "Current"?
And when that date is older than 30 days, then the status should change to "Review"?

If this is the case? then:

Private Sub Form_Current()
Dim intDay As Integer
intDay = DateDiff("d", Me.DateFieldOnForm, Date)
If intDay >= 30 Then
    Me.status.Value = "Review"
End If
End Sub

Hope this help.

[khalid]

Oops... Bob Fitz already replied