Holidays Restriction to Users

**Shamli** · 09-12-2018, 12:49 PM

Hello Everyone
How do I send a warning message to the users inputting data for the holidays date.

Do I have to create a table that lists the holiday dates?

And I have to make a restriction from weekending date.
So when the user chooses the weekending date (Usually a Friday).
If the holiday date falls between the weekending date.
A warning message should be displayed.

**June7** · 09-12-2018, 01:18 PM

A Holidays table probably best. I have seen a thread that discussed code to calculate any holiday but can't find it again.

Code can do a DLookup on Holidays table for record with date between the begin and end week dates.

**Shamli** · 09-12-2018, 01:59 PM

I tried the below code:

Code:

 If Forms!Create_New_Entry_F!WeekEndingBox = DLookup("[HolidayDate]", "Holiday_T", "HolidayDate = #" & Format(Nz(Forms!Create_New_Entry_F!WeekEndingBox, 0), "Short Date") & "#") Then
     MsgBox "Please note the holiday falling on this week"
   Me.WeekEndingBox.SetFocus
   Cancel = True
   End If

This works well. How to check if holiday is falling within the week. When weekending is a Friday.

Originally Posted by June7

A Holidays table probably best. I have seen a thread that discussed code to calculate any holiday but can't find it again.

Code can do a DLookup on Holidays table for record with date between the begin and end week dates.

**orange** · 09-12-2018, 02:19 PM

For consideration:
Some logic along this line might be appropriate:

Code:

If HolidayDate between WeekEndingDate - 7 and WeekEndingDate

then (there's a holiday this week)
    'Do something (change color, send message,......)
else ( no holiday this week)
    'Do regular stuff
End if

**Shamli** · 09-12-2018, 02:52 PM

In order to know the holiday list , I have created a holiday Table which contains the list of holidays.
Or Do I have to mention all the 8 federal holidays? Not sure how to proceed with your option

Originally Posted by orange

For consideration:
Some logic along this line might be appropriate:

Code:

If HolidayDate between WeekEndingDate - 7 and WeekEndingDate

then (there's a holiday this week)
    'Do something (change color, send message,......)
else ( no holiday this week)
    'Do regular stuff
End if

**Shamli** · 09-12-2018, 02:55 PM

I created a start date (Monday) and the end date as the weekending date Friday.
And used this code:

Code:

If Forms!Create_New_Entry_Menu_F!WeekendingCmbo >= DLookup("[HolidayDate]", "Holiday_T", "HolidayDate = #" & Format(Nz(Forms!Create_New_Entry_Menu_F!WeekendingCmbo, 0), "Short Date") & "#") And Forms!Create_New_Entry_Menu_F!Text32 <= DLookup("[HolidayDate]", "Holiday_T", "HolidayDate = #" & Format(Nz(Forms!Create_New_Entry_Menu_F!Text32, 0), "Short Date") & "#") Then
     MsgBox "Please note the holiday falling on this week"
   Me.WeekendingCmbo.SetFocus
   Cancel = True
   End If

This one doesn't show any error. Even though I have kept the weekending date 9/14/2018 as holiday in the holiday table.

Originally Posted by June7

A Holidays table probably best. I have seen a thread that discussed code to calculate any holiday but can't find it again.

Code can do a DLookup on Holidays table for record with date between the begin and end week dates.

**kd2017** · 09-12-2018, 03:17 PM

You're only concerned about holidays falling on a weekday? Not the weekends?

**orange** · 09-12-2018, 03:19 PM

You need some code/vba to invoke some logic to determine if a holiday or holidays exist within the week displayed on your form.
I suggest you work with the underlying tables (your calendar and your holidays).

There is an example of identifying Holidays, weekends and work days/business days in this thread that may be helpful.

**Shamli** · 09-12-2018, 03:26 PM

Thank you so much. I will look into that.

Originally Posted by orange

You need some code/vba to invoke some logic to determine if a holiday or holidays exist within the week displayed on your form.
I suggest you work with the underlying tables (your calendar and your holidays).

There is an example of identifying Holidays, weekends and work days/business days in this thread that may be helpful.

**Shamli** · 09-12-2018, 03:27 PM

As of now, I am trying to send a warning message or as @orange suggested a color change to the holidays falling on the weekday.

Originally Posted by kd2017

You're only concerned about holidays falling on a weekday? Not the weekends?

**kd2017** · 09-12-2018, 03:34 PM

[edit - delete. that was bad. stay tuned.]

The following query will try to find records's in a table called holidays that fall within the same week as an input date called [InputDate]

Code:

PARAMETERS InputDate DateTime;
SELECT Holidays.Holiday, Holidays.HolidayDate
FROM Holidays
WHERE (((Holidays.HolidayDate)>=[InputDate]-Weekday([InputDate])+2) AND ((Holidays.HolidayDate)<[InputDate]-Weekday([InputDate])+7));

Here's how you might use it in a generic function:

Code:

Public Function FindHolidayWeek(InputDate As Date) As Variant
On Error GoTo ErrHandler
    Dim rs As DAO.Recordset
    Dim mon As Date
    Dim qry As String
    
    mon = InputDate - Weekday(InputDate) + 2
    
    qry = "SELECT Holidays.HolidayDate" & _
          " FROM Holidays" & _
          " WHERE Holidays.HolidayDate>=#" & mon & "# AND Holidays.HolidayDate<#" & (mon + 5) & "#;"
          
    Set rs = CurrentDb.OpenRecordset(qry)
        If Not (rs.BOF And rs.EOF) Then
            FindHolidayWeek = rs!HolidayDate
        Else
            FindHolidayWeek = Null
        End If
    rs.Close
    
ExitHandler:
    Set rs = Nothing
    Exit Function
    
ErrHandler:
    MsgBox "Error"
    FindHolidayWeek = Err
    Resume ExitHandler
End Function

And you could call it from a textbox's BeforeUpdate event like so:

Code:

Private Sub txtInputDate_BeforeUpdate(Cancel As Integer)
    Dim holiday As Variant
    holiday = FindHolidayWeek(Me.txtInputDate)
    
    If Not IsNull(holiday) Then
        MsgBox "The input date is in the same week as a holiday on " & holiday & "."
    End If
End Sub

**Shamli** · 09-12-2018, 03:44 PM

Right now. I am using the below code. It works. Its just a temporary solution. But I am trying to read your thread and build that later. Thank you so much for the info.

Code:

If Text284 = DLookup("[HolidayDate]", "Holiday_T", "HolidayDate = #" & Format(Nz(Forms!Create_New_Entry_F!Text284, 0), "Short Date") & "#") Then
   Monday.BackColor = vbRed
  
   Else
  Monday.BackColor = vbWhite
   End If

Originally Posted by orange

You need some code/vba to invoke some logic to determine if a holiday or holidays exist within the week displayed on your form.
I suggest you work with the underlying tables (your calendar and your holidays).

There is an example of identifying Holidays, weekends and work days/business days in this thread that may be helpful.

**Shamli** · 09-12-2018, 05:03 PM

Thank you so much for the update.

I will try this one too.

Originally Posted by kd2017

[edit - delete. that was bad. stay tuned.]

The following query will try to find records's in a table called holidays that fall within the same week as an input date called [InputDate]

Code:

PARAMETERS InputDate DateTime;
SELECT Holidays.Holiday, Holidays.HolidayDate
FROM Holidays
WHERE (((Holidays.HolidayDate)>=[InputDate]-Weekday([InputDate])+2) AND ((Holidays.HolidayDate)<[InputDate]-Weekday([InputDate])+7));

Here's how you might use it in a generic function:

Code:

Public Function FindHolidayWeek(InputDate As Date) As Variant
On Error GoTo ErrHandler
    Dim rs As DAO.Recordset
    Dim mon As Date
    Dim qry As String
    
    mon = InputDate - Weekday(InputDate) + 2
    
    qry = "SELECT Holidays.HolidayDate" & _
          " FROM Holidays" & _
          " WHERE Holidays.HolidayDate>=#" & mon & "# AND Holidays.HolidayDate<#" & (mon + 5) & "#;"
          
    Set rs = CurrentDb.OpenRecordset(qry)
        If Not (rs.BOF And rs.EOF) Then
            FindHolidayWeek = rs!HolidayDate
        Else
            FindHolidayWeek = Null
        End If
    rs.Close
    
ExitHandler:
    Set rs = Nothing
    Exit Function
    
ErrHandler:
    MsgBox "Error"
    FindHolidayWeek = Err
    Resume ExitHandler
End Function

And you could call it from a textbox's BeforeUpdate event like so:

Code:

Private Sub txtInputDate_BeforeUpdate(Cancel As Integer)
    Dim holiday As Variant
    holiday = FindHolidayWeek(Me.txtInputDate)
    
    If Not IsNull(holiday) Then
        MsgBox "The input date is in the same week as a holiday on " & holiday & "."
    End If
End Sub

**Shamli** · 09-12-2018, 05:17 PM

I have one more question in this:
The below query I am trying to give a warning message when user enters values greater than zero on a text box, based on the date value.
Its checking the date criteria but even if I enter 0, it still throws the message.
What is the problem here.?

Code:

Private Sub Monday_Change()
If Text284.Value > 0 And Text284 = DLookup("[HolidayDate]", "Holiday_T", "HolidayDate = #" & Format(Nz(Forms!Create_New_Entry_F!Text284, 0), "Short Date") & "#") Then
    MsgBox "Please note this day is a holiday. Do you still want to charge on this day? "
  Me.Monday.SetFocus
   Cancel = True
   End If
End Sub

**Shamli** · 09-14-2018, 03:05 PM

Thank you so much for the inputs.
I have taken the below code and its working.
I have created a holiday table. and Dlookup the values to it.

Orange and kd2017 thread are worth reading . Because of time constraint I am using the below code.

Code:

Private Sub Monday_AfterUpdate()
Dim LResponse As Integer
 
If Monday.Value > 0 And Text284 = DLookup("[HolidayDate]", "Holiday_T", "HolidayDate = #" & Format(Nz(Forms!Create_New_Entry_F!Text284, 0), "Short Date") & "#") Then
LResponse = MsgBox("Please note this day is a holiday. Do you still want to charge on this day? ", vbYesNo, "CONTINUE")
If LResponse = vbYes Then
Me.Monday.SetFocus
Else
Me.Monday.Value = 0
End If
   End If
End Sub

Holidays Restriction to Users

Thread Tools

Holidays Restriction to Users

Similar Threads

ComBo Box Restriction

InPut restriction

Does a SharePoint DB have the same 2BG size restriction?

Field Restriction

Filtering restriction on tables

Posting Permissions