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
Entry restrict for limited period
-
- Administrator
- Posts: 78658
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Entry restrict for limited period
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
Hans
-
- 2StarLounger
- Posts: 120
- Joined: 22 Feb 2022, 09:04
Re: Entry restrict for limited period
Perhaps concatenate maxhours into msgbox string as well?
Using Access 2007/2019.
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.
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.
-
- Administrator
- Posts: 78658
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Entry restrict for limited period
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
Hans
-
- 2StarLounger
- Posts: 184
- Joined: 13 Jun 2018, 07:50
Re: Entry restrict for limited period
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
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
-
- Administrator
- Posts: 78658
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Entry restrict for limited period
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?
Can you explain as clearly as you can what you want?
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 184
- Joined: 13 Jun 2018, 07:50
Re: Entry restrict for limited period
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
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
-
- Administrator
- Posts: 78658
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Entry restrict for limited period
As far as I can tell, that's what the version I proposed does...
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 184
- Joined: 13 Jun 2018, 07:50
Re: Entry restrict for limited period
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
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.
-
- Administrator
- Posts: 78658
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Entry restrict for limited period
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
Hans
-
- 2StarLounger
- Posts: 184
- Joined: 13 Jun 2018, 07:50
Re: Entry restrict for limited period
Thanks Mr. Hans, yes you are correct, when I changed the date to date_ it worked.
regards,
VKKT
regards,
VKKT
-
- 2StarLounger
- Posts: 120
- Joined: 22 Feb 2022, 09:04
Re: Entry restrict for limited period
Better named fields would help? , easy to miss TBH.
My thoughts mirrored HansV's thoughts.
My thoughts mirrored HansV's thoughts.
Using Access 2007/2019.
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.
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.