VBA Looking up dates "exception dates" in a table

[MvdBergh]

I have a simple schedule/planning database whereby I schedule staff for events. I have among others two forms with underlying tables as follow:

1. I have a Main scheduling Table, "tblEventDates", with a field: PlanDate (Form frmEventDates)
2. I have a separate Table, "tblExceptDates", with a field: ExecptDate (Form frmExceptDates) where exception dates such as holidays, conference dates, etc. are captured.

What I need:
The date entered in the field PlanDate needs to be checked against any exception dates entered in "tblExceptDates". A warning message then needs to be displayed to indicate the specific conflicting exception date.



Any suggestions as to how to go about shall be highly valued.

[orange]

see this tutorial /example for one technique.

[MvdBergh]

Very loosely, I have found a simple solution with DLookup at http://www.techonthenet.com/access/functions/domain/dlookup.php

(To have a more sensible message displayed a second DLookup has been used to display the description of the exception date, ex. " - Christmas")

Private Sub PlanDate_AfterUpdate()


On Error GoTo 1:

LDate = DLookup("ExceptDate", "tblExceptDate", "ExceptDate = PlanDate")
LDescription = DLookup("Description", "tblExceptDate", "ExceptDate = PlanDate")

MsgBox LDate & LDescription

1: End Sub