Automated Message Box notice when loading a form.

[Robert2150]

I have a database that has a main form, and a sub-form (Service Contracts). The sub-form is filled in from a table based upon the property that is shown in the main form.

I would like to generate a message based upon one of the fields being loaded in the sub-form. That field is "End Date".

The bases for opening the message is that the "End Date" of the form record is less than the current Date.

The message is "The Service Contract Has Expired."



I have tried entering code in the "On Load" property of both the main form and the sub-form, to no avail.

The code I have tried is:

Private Sub Form_Load()
If "EndDate" < Date Then
MsgBox "Service Contract Has Expired"
End If
End Sub

Any help would be appreciated. Thank you.

[June7]

So if this value is in each subform record, which record do you want to report on?

[Micron]

the "End Date" of the form record is less than the current Date

Which form?

EDIT: Assuming you mean the main form, "EndDate" is a string. [EndDate] is a field or control name. That may be your problem.
Maybe you should also use conditional formatting to flag that form control?

[Robert2150]

Only the record that has the same "Property Number" (A field on both tables.) is loaded into the sub-form.

[Robert2150]

The "End Date" is a field.

[June7]

I don't understand. The subform has only 1 record?

[ssanfu]

I would like to generate a message based upon one of the fields being loaded in the sub-form. That field is "End Date".

Private Sub Form_Load()

    If [EndDate] < Date() Then
        MsgBox "Service Contract Has Expired"
    End If

End Sub

The "Form_Load" event is probably the wrong event. I would try using the "Form_Current" event.


My preference would be to use a query and have a calculated column

IsExpired: IIF([EndDate] < Date(), "Service Contract Has Expired", "")

then add a text box (to the sub form) bound to this query column.

[Micron]

The "End Date" is a field.

When you write it like that, it is a string. Note ssanfu's omission of the space (which many of us practice). If it must be there, then it must be
[End Date] not "End Date" and not End Date and not "[End Date]". The latter might be OK in a domain function, but not for what you're doing.

[Robert2150]

It depends, I have a query than only shows (loads) the record for the property selected for the Main Form.