Restrict Entry of Hours in a Specific Date

[VKKT]

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

[HansV]

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

[VKKT]

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.

[HansV]

You might add the same code to the Before Update event of the Date_ text box.

[VKKT]

Greetings;

Thanks Mr. Hans, It is working perfectly.

Regards,
VKKT

[VKKT]

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

[burrina]

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

[VKKT]

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

[burrina]

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

[HansV]

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

[VKKT]

Greetings..
Thanks Mr. Hans for your support.
The code is working perfectly.
Thanks to burrina too
Regards,
VKKT