Message Box warning when incorrect date is entered

[Marlene23]

Good Day Access Guru's!



I was wondering if I am able to add a message warning linked to a field?

E.g. I built a training tracker with a linked table that holds the learner and the course he is enrolled on and the table has a termination date field where a termination date that must be entered when a learner terminates from the course.

Each course also has a start date and an end date.

What I want to do is to give the administrator a warning message when the incorrect date is entered into the termination date field. The incorrect date will be if the termination date is BEFORE the course start date and AFTER the course end date. In other words, the correct date should fall within those to dates.

I assume I would enter some code on the termination date field on the after update event? Please correct me if I am wrong.

I have attempted something in the line of the following:

if me.TerminationDate > me.projectstart OR < me.projectenddate then
msgBox "The Termination date cannot be before the course start date or after the course end date"
end if

If the correct date was entered then no message box should appear.

Please can someone guide me in the right direction?

Thank you in advance!

[June7]

Have to repeat the control or field name in the If condition

if me.TerminationDate > me.projectstart OR me.TerminationDate < me.projectenddate then

Could use textbox ValidationRule and ValidationText properties instead of VBA.

[Marlene23]

Thanks a million! At least I did not fall to far from the wagon hahaha

I get a compile error now "Method or data member not found"

The start date and end date fields does not physically appear on my datasheet subform but I did include it within the query from where the datasheet subform is built from.
I also checked the spelling etc.

Anything else I could have missed?

Thank you for the alternative solution you provided as well, will investigate those as well and see what works best. In my world you need to have flashing lights go off with huge signals for people to realise they are entering the incorrect data hahaha

[davegri]

Try it this way:
if me.TerminationDate > [projectstart] OR me.TerminationDate < [projectenddate] then

The ME. prefix requires the dataname to be bund to control on the current form. If the dataname is just in the query, use brackets instead.

[Marlene23]

Awesome thanks! The message box pops up.

The message box however pops up even if the termination date falls between those dates, should this happen?
I only need the message to appear if the termination date falls outside the other dates.

And thanks for the bracket tip, now I understand why or when to use brackets :-)

[davegri]

The logic is backwards.
if me.TerminationDate < [projectstart] OR me.TerminationDate > [projectenddate] then

[Marlene23]

Awesome! Now it is working perfectly! Thank you for all the assistance!

[davegri]

Happy to help!