Hi All,
Greetings:
I have the attached time sheet db and in which I want to Restrict entry of hours as maximum 8 in a specific date for a specific person. If entered more than 8 hours need to pop-up a message to notify the same.
Hope this is possible and awaiting a solution to the above issue.
Regards,
VKKT
Restrict Entry of Hours in a Specific Date
-
- 2StarLounger
- Posts: 184
- Joined: 13 Jun 2018, 07:50
Restrict Entry of Hours in a Specific Date
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Restrict Entry of Hours in a Specific Date
Create the following event procedure for the Before Update event of the NoOfHours text box on the TIME SHEET DATA-AGA form:
Code: Select all
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 should not exceed 8!", vbExclamation
Cancel = True
End If
End Sub
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 184
- Joined: 13 Jun 2018, 07:50
Re: Restrict Entry of Hours in a Specific Date
Greetings:
Thanks a lot Mr. Hans for your response and solution to my query, Yes it is working.
But I notice that at the time of initial entry of date it is restricting the total hours as 8. But if I change the already entered another date later it is accepting more than 8 hours.
Eg. I made 4 entries for 27-Jan-20 with a total of 8 hours and later if I change the entry date of 26-Jan-20 (or any other date) to 27-Jan-20 it is not restricting the total hours as 8.
Is there any solution to this issue.
regards,
VKKT.
Thanks a lot Mr. Hans for your response and solution to my query, Yes it is working.
But I notice that at the time of initial entry of date it is restricting the total hours as 8. But if I change the already entered another date later it is accepting more than 8 hours.
Eg. I made 4 entries for 27-Jan-20 with a total of 8 hours and later if I change the entry date of 26-Jan-20 (or any other date) to 27-Jan-20 it is not restricting the total hours as 8.
Is there any solution to this issue.
regards,
VKKT.
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Restrict Entry of Hours in a Specific Date
You might add the same code to the Before Update event of the Date_ text box.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 184
- Joined: 13 Jun 2018, 07:50
Re: Restrict Entry of Hours in a Specific Date
Greetings;
Thanks Mr. Hans, It is working perfectly.
Regards,
VKKT
Thanks Mr. Hans, It is working perfectly.
Regards,
VKKT
-
- 2StarLounger
- Posts: 184
- Joined: 13 Jun 2018, 07:50
Re: Restrict Entry of Hours in a Specific Date
Greetings to all,
Hope all are doing fine during these difficult time of corona pandemic.
Here I have a new request related to the same subject.
Is it possible, for Eg. on Saturday, restrict the entry of hours only to 5 hours unlike other days which is limited to 8 hours.
Regards,
VKKT
Hope all are doing fine during these difficult time of corona pandemic.
Here I have a new request related to the same subject.
Is it possible, for Eg. on Saturday, restrict the entry of hours only to 5 hours unlike other days which is limited to 8 hours.
Regards,
VKKT
-
- 4StarLounger
- Posts: 550
- Joined: 30 Jul 2014, 23:58
Re: Restrict Entry of Hours in a Specific Date
Question? Do you have the code already for the 8 hours? If so why not just subtract 3 from it?
Also, what happens if user tries to access the form again your code fires? You would need to check for the date and compare the value against that.
Hi, this is air code so untested.
Dim varElapsed As Variant
If varElapsed >= 18000 Then ' Time Expired
' Do Something
End If
OnTimerEvent
TimerInterval = 1000
HTH
Also, what happens if user tries to access the form again your code fires? You would need to check for the date and compare the value against that.
Hi, this is air code so untested.
Dim varElapsed As Variant
If varElapsed >= 18000 Then ' Time Expired
' Do Something
End If
OnTimerEvent
TimerInterval = 1000
HTH
-
- 2StarLounger
- Posts: 184
- Joined: 13 Jun 2018, 07:50
Re: Restrict Entry of Hours in a Specific Date
Thanks for the immediate response.
I have the above code to restrict 8 hours for all the days. but now I need only on Saturday the hours should not exceed 5.5 (not 5 as mentioned earlier) hours, all the other days need 8 hours. If I subtract by 3 all the days the hours will be changed? If the above code revised with these change will be highly appreciated.
Regards,
VKKT
I have the above code to restrict 8 hours for all the days. but now I need only on Saturday the hours should not exceed 5.5 (not 5 as mentioned earlier) hours, all the other days need 8 hours. If I subtract by 3 all the days the hours will be changed? If the above code revised with these change will be highly appreciated.
Regards,
VKKT
-
- 4StarLounger
- Posts: 550
- Joined: 30 Jul 2014, 23:58
Re: Restrict Entry of Hours in a Specific Date
Add text field named txtsaturday
Me.txtsaturday =Format(CDate(Date())-(Weekday(CDate(Date())))-1,"m/d/yy")
If Me.txtsaturday = True And ' Use Hans's Code Here and adjust to 5.5
HTH
Me.txtsaturday =Format(CDate(Date())-(Weekday(CDate(Date())))-1,"m/d/yy")
If Me.txtsaturday = True And ' Use Hans's Code Here and adjust to 5.5
HTH
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Restrict Entry of Hours in a Specific Date
I'd do it like this:
Code: Select all
Private Sub Hours_BeforeUpdate(Cancel As Integer)
Dim MaxHours As Double
Select Case Weekday(Me.Date_)
Case vbSaturday
MaxHours = 5.5
Case Else
MaxHours = 8
End Select
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 should not exceed 8!", vbExclamation
Cancel = True
End If
End Sub
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 184
- Joined: 13 Jun 2018, 07:50
Re: Restrict Entry of Hours in a Specific Date
Greetings..
Thanks Mr. Hans for your support.
The code is working perfectly.
Thanks to burrina too
Regards,
VKKT
Thanks Mr. Hans for your support.
The code is working perfectly.
Thanks to burrina too
Regards,
VKKT