Entry restrict for limited period

VKKT
2StarLounger
Posts: 184
Joined: 13 Jun 2018, 07:50

Entry restrict for limited period

Post by VKKT »

Greeting:
Hi
I have the below code to restrict entry of hours 8 in one field:
Is it possible to restrict to 12 hours, only for a limited period, for eg. from 09-May-22 to 21-June-22 (keeping the 8 hours limit as it is)
regards,
VKKT
--------------------

Private Sub Hours_BeforeUpdate(Cancel As Integer)
If Nz(Me.NoOfHours, 0) + Nz(DSum("NoOfHours", "TIME SHEET DATA", "StaffID=" & Me.StaffID & _
" AND ID<>" & Me.ID & " AND Date_=#" & Format(Me.Date_, "yyyy-mm-dd") & "#"), 0) > 8 Then
MsgBox "The total number of hours per day should not exceed 8!", vbExclamation
Cancel = True
End If
End Sub

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Entry restrict for limited period

Post by HansV »

For example:

Code: Select all

Private Sub Hours_BeforeUpdate(Cancel As Integer)
    Dim MaxHours As Long
    If Date >= #5/9/2022# And Date <= #6/21/2022# Then
        MaxHours = 12
    Else
        MaxHours = 8
    End If
    If Nz(Me.NoOfHours, 0) + Nz(DSum("NoOfHours", "TIME SHEET DATA", "StaffID=" & Me.StaffID & _
            " AND ID<>" & Me.ID & " AND Date_=#" & Format(Me.Date_, "yyyy-mm-dd") & "#"), 0) > MaxHours Then
        MsgBox "The total number of hours per day should not exceed 8!", vbExclamation
        Cancel = True
    End If
End Sub
Best wishes,
Hans

Gasman
StarLounger
Posts: 81
Joined: 22 Feb 2022, 09:04

Re: Entry restrict for limited period

Post by Gasman »

Perhaps concatenate maxhours into msgbox string as well?
Using Access 2007.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Entry restrict for limited period

Post by HansV »

Thanks, good idea!

Code: Select all

Private Sub Hours_BeforeUpdate(Cancel As Integer)
    Dim MaxHours As Long
    If Date >= #5/9/2022# And Date <= #6/21/2022# Then
        MaxHours = 12
    Else
        MaxHours = 8
    End If
    If Nz(Me.NoOfHours, 0) + Nz(DSum("NoOfHours", "TIME SHEET DATA", "StaffID=" & Me.StaffID & _
            " AND ID<>" & Me.ID & " AND Date_=#" & Format(Me.Date_, "yyyy-mm-dd") & "#"), 0) > MaxHours Then
        MsgBox "The total number of hours per day should not exceed " & MaxHours & "!", vbExclamation
        Cancel = True
    End If
End Sub
Best wishes,
Hans

VKKT
2StarLounger
Posts: 184
Joined: 13 Jun 2018, 07:50

Re: Entry restrict for limited period

Post by VKKT »

Greetings!
Thanks Hans.

12 hours are allowed to enter for all dates, not restricted only for the given dates.

Message appears as "The total number of hours per day should not exceed 12!, need to show message also as "The total number of hours per day should not exceed 8! for other than the restricted date.

regards,
VKKT

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Entry restrict for limited period

Post by HansV »

Sorry, you've lost me. "12 hours are allowed to enter for all dates, not restricted only for the given dates." appears to contradict your original request.
Can you explain as clearly as you can what you want?
Best wishes,
Hans

VKKT
2StarLounger
Posts: 184
Joined: 13 Jun 2018, 07:50

Re: Entry restrict for limited period

Post by VKKT »

Greetings,

Sorry for the confusion Hans.

Restrict entry as 12 maximum in one day for the limited period mentioned, for other days only 8 maximum required.

Hope it is clear.

regards,
vkkt

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Entry restrict for limited period

Post by HansV »

As far as I can tell, that's what the version I proposed does...
Best wishes,
Hans

VKKT
2StarLounger
Posts: 184
Joined: 13 Jun 2018, 07:50

Re: Entry restrict for limited period

Post by VKKT »

Greetings,

Thanks Mr. Hans it is working well, when I changed the "Date" to Date_ >= #5/9/2022# And Date <= #6/21/2022#, it worked. that was the correct field name.

Thanks a lot.
regards,
vkkt
Last edited by VKKT on 12 May 2022, 11:02, edited 1 time in total.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Entry restrict for limited period

Post by HansV »

Ah, I interpreted your question differently. Try this version:

Code: Select all

Private Sub Hours_BeforeUpdate(Cancel As Integer)
    Dim MaxHours As Long
    If Me.Date_ >= #5/9/2022# And Me.Date_ <= #6/21/2022# Then
        MaxHours = 12
    Else
        MaxHours = 8
    End If
    If Nz(Me.NoOfHours, 0) + Nz(DSum("NoOfHours", "TIME SHEET DATA", "StaffID=" & Me.StaffID & _
            " AND ID<>" & Me.ID & " AND Date_=#" & Format(Me.Date_, "yyyy-mm-dd") & "#"), 0) > MaxHours Then
        MsgBox "The total number of hours per day should not exceed " & MaxHours & "!", vbExclamation
        Cancel = True
    End If
End Sub
Best wishes,
Hans

VKKT
2StarLounger
Posts: 184
Joined: 13 Jun 2018, 07:50

Re: Entry restrict for limited period

Post by VKKT »

Thanks Mr. Hans, yes you are correct, when I changed the date to date_ it worked.

regards,
VKKT :cheers:

Gasman
StarLounger
Posts: 81
Joined: 22 Feb 2022, 09:04

Re: Entry restrict for limited period

Post by Gasman »

Better named fields would help? :sad: , easy to miss TBH.
My thoughts mirrored HansV's thoughts.
Using Access 2007.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.