Need max date; complicated with forms and subforms....

Bear with me as I explain this.....

I have a db with a main form and several subforms. The DB is being used as a contact log to collect various pieces of information that my team needs to track while working with our clients.

So, the way it works, when you open the form, it shows the information a particular client. Each of the subforms shows information of a particular type on that client.

One of these is barriers that a particular client may be encountering when we work with them.
The 'barrier' subform shows the date the barrier was identified, the barrier and whether or not it is active.

My troubles... I've been asked to create an alert/pop-up that appears when barriers haven't been updated in a month's time.

I've successfully gotten a MsgBox to show up if I use this on the subform's load event:
Dim rs As DAO.Recordset
Dim TheDate As Date    ' Declare variables.
Set rs = Me.RecordsetClone
TheDate = rs.Fields("MaxOfBarrierDate")
If DateDiff("d", TheDate, [Now]) >= 30 Then
MsgBox "Barriers not updated in more than 30 days"
End If

But, the problem is that on the main form, there are navigation buttons which will take you to a different client... and when clicking these navigation buttons, the subform's load event doesn't fire

I've tried to call a function on the subform when clicking those navigation buttons but have failed :(

HELP!!!!
Start Free Trial