Creating Pop up based on Conflicting Dates

[jabaker54]

Hello All,

So I posted something similar about a month ago but didn't really understand the answer...

Let me lay done what my database entails.

- I currently have a table that is labeled WorkSchedule. I then Created a query called WorkSchedule Query.

- The column labels are as follows (listed from right to left):
Project, Event (something inside of the project), Employee1, Employee2, StartDate, EndDate

- I created a split form that shows each record.

- On each record I made the employee1, and employee2 as a drop down box that allows you to pick from all employees.

*MY QUESTION*

How do I make a pop up form appear when there is a conflicting date based on the employee I picked?

For example:
If I had assigned Rebecca to an event that went on from 11/12/2014 - 11/14/2014. Then I tried to assign her to another event from 11/13/2104-11/15/2014. I want a pop up message saying that these dates conflict.


Any solution will work. If it is VBA I may need some guidance.


Thanks and I hope you can help!

Justin

 

[llkhoutx]

if YourConflictest then docmd.openreport "YourReportName"

 

[burrina]

Here is an Example: Of course you have to change to your table name and place the code in the BeforeUpdateEvent.

DoCmd.SetWarnings False
If DCount("*", "tblSchedules", "EndDate>=#" & Me.StartDate & "# And StartDate<=#" & Me.EndDate & "#") > 0 Then
Cancel = True
Me.Undo
MsgBox "An Employee is already Scheduled during this period.", vbInformation, "Scheduling Error"
End If
DoCmd.SetWarnings True

HTH