How to trigger an alert if annual leave dates have already been selected for another employee - MS Access?

I've created a form to monitor annual leave and would like to set up an alert that triggers if someone else is on annual leave at the same time.  
Not sure where to start.
Any ideas?
LillyCAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
Well there many ways to implement this..but we need some more info on how you have setup your monitor form.
I guess the simplest solution would be with the use of Conditional formatting...e.g painting Red all the employees with annual leave
0
als315Commented:
Do you have weekly-based or daily-based annual leaves?
0
LillyCAuthor Commented:
John - My monitoring form is attached to each individual employee, when I enter the start and end date of their annual leave, I would like a trigger to pop up to show if there are any other employees off at any period between those chosen start and end dates.

Als315 - annual leave can be entered for any dates.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

John TsioumprisSoftware & Systems EngineerCommented:
can we have a screenshot because i think i am having some other kind of implementation
0
John TsioumprisSoftware & Systems EngineerCommented:
by the way if you have a kind of an individual form per employee then on the After Update of the dates text boxes you can have a query run to indicate the employees who currently are in a leave
0
LillyCAuthor Commented:
Here you go John.
Annual-Leave-Form.JPG
0
John TsioumprisSoftware & Systems EngineerCommented:
I think the After Update should cover your needs...just place a check method that ensures that both Start and End Date are correctly filled and then run a recordset iteration that will inform you the employees with conflicting leaves...if you want to check before entering the actual leave then a small form with similar functionality (just start,end date) should be sufficient
0
PatHartmanCommented:
Use the BEFORE update event of the form.  Using the After update events of the date fields requires more code since you have to check the dates in two places plus you have to account for the fact that one of the dates will be empty at least some of the time.  And you STILL need code in the form's BeforeUpdate event to prevent saving the record so just do it all in one place.

I included several tests that I would perform to ensure valid dates.  You may need others.  For example, the number of days might be limited to 14 or 21 normally and require special override if longer.
Cancel = True

If IsDate(Me.txtStartDate) Then
    If IsDate(Me.txtEndDate) Then
        If Me.txtStartDate <= Me.txtEndDate Then
            If Me.txtStartDate >= Date Then
               If dCount("*", "tblAnnualLeave", "StartDate <= " & Me.txtEndDate & " AND EndDate >= " & Me.txtStartDate) > 0 Then
                  Msgbox "Leave overlaps with that of another employee.",vbOKOnly                  
                  Exit Sub
                End If
            Else
                Msgbox "Start date must be >= Today's Date.",vbOKOnly
                Me.txtStartDate.SetFocus
                Exit Sub
            End If
        Else
            Msgbox "Start date must be <= End Date.", vbOKOnly
            Me.txtStartDate.SetFocus
            Exit Sub
        End If
    Else
        Msgbox "End Date is required.",vbokOnly
        Me.txtEndDate.SetFocus
        Exit Sub
    End If
Else
    Msgbox "Start Date is required.", vbOKOnly
    Me.txtStartDate.SetFocus
    Exit Sub
End If

Cancel = False

Open in new window


PS - using the AfterUpdate event (even at the control level) for validation is akin to closing the barn door AFTER the horses have escaped.  It is too late to cancel the save at that point so bad data will get saved to your table.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LillyCAuthor Commented:
That's great, thank you very much.
0
PatHartmanCommented:
You're welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.