Automatic Field Update based on date

[rhuff]

I am very much a new member to the Access VBA scene and have been learning as much as I can on my own through ebooks and by reviewing online code. Anyway, I have run in to a bit of a roadblock in designing my VBA code, here is my scenario:
I am tracking a list of materials that will be input at various times by various users. Each one of these materials is perishable and has a fixed expiration date that is input at the time the material is recieved. What I would like to do is have a neighboring 'Status' field read "Expired" when date()>Expiration. It would be nice if this was performed on the opening of the document. I can get it to manually update one field but I need it to do the entire list of materials at once not just one at a time. It would be nice to if the 'Status' field would display a warning in red font that reads something like "Material will expire in two weeks" when the material is nearing the end of its shelf life. If the above is not clear I can clarify- Thanks in advance.

 

[pdx_man]

Since this is a calculated field, Date() > Expiration, this should be done in a query.

SELECT FirstField, SecondField, Expiration, Status: GetExpStat([Expiration])
FROM YourTable

Public Function GetExpStat(ExpDate As Date) As String

    Select Case DateDiff("d", Date, ExpDate)
        Case Is > 21: GetExpStat = "> Three Weeks Left til Expiration"
        Case Is > 14: GetExpStat = "Two Weeks Left til Expiration"
        Case Is > 7: GetExpStat = "ONE Week Left til Expiration"
        Case Is < 0: GetExpStat = "EXPIRED"
        Case Is > 0: GetExpStat = Abs(DateDiff("d", ExpDate, Date)) & " Days left til Expiration"
    End Select

End Function

Then you can create a report and do your formatting there.