Need help with one of my Time Clock forms (6 Viewers)

[Technics]

Pictured is my clockin/out form. I have it setup so when an employee hits the clockin button the form closes. When they reopen the form under the same name the form shows their clockin time, they click the clockout button and the form closes. It has recorded their time for that period. The problem happens when an employee forgets to clockin so they just click clockout which makes the total hours worked report column show #error even for employees that did clockin.

My thoughts, simple fix! Grey out the clockout button =false and make it only available if they have clocked in. If someone forgets to clockin they have to click clockin, turn around and clockout. Time can be adjusted manually. I can make this work as long as the form stays open but I need it to close on each click. When the form is reopened for clockout the button is still greyed out. I tried some form events procedures with opening/closing but still can't get it to work as I need. I come to the experts.

 

[Gasman]

Do not close the form if Time In is empty and the ClockIn button is pressed?
That is the simplest option?

 

[Technics]

Do not close the form if Time In is empty and the ClockIn button is pressed?
That is the simplest option?

The form doesn't close if no buttons are clicked. The close button at the bottom of form is just to close the form if no one is clicking in or out. If someone clicks the Clock In button it automatically puts in the time of clicking the clockin button and closes the form. Same with clock out. There is no need for the form to stay open once someone has clocked in or out.

Also after clocking in and the form closes the person reopens under their name and the clockin window shows the time that they did clockin and they clock out. The point is to make it so they can't clock out unless they have clocked in.

 

[plog]

Time can be adjusted manually.

So why harass users with a kludge fix that is ultimately pointless? If they forget to login, can't this be resolved without them further engaging with that form? If a manual adjustment is necessary, why make them do something with the form?

What exactly is going to occur in that manual adjustment? Do they do it? Does an admin? Does someone else who is better versed in Access?

 

[theDBguy]

The problem happens when an employee forgets to clockin so they just click clockout which makes the total hours worked report column show #error even for employees that did clockin.

Not sure how this happens but maybe you can modify the clockout button to force the user to also clockin before closing the form. Just a thought...

 

[Technics]

So why harass users with a kludge fix that is ultimately pointless? If they forget to login, can't this be resolved without them further engaging with that form? If a manual adjustment is necessary, why make them do something with the form?

What exactly is going to occur in that manual adjustment? Do they do it? Does an admin? Does someone else who is better versed in Access?

Admin, the person that takes care of the time report and writes the checks will adjust the time. The employee that forgot to clock in will merely let them know what should have been the correct time. This will not happen often but out of 30 clock ins it happen once and that one time was enough to error out the Total Hours Worked report column for every employee, even the ones that did clock in and out correctly. Maybe I should be looking at why that errors but I know if everyone clocks in the error doesn't occur so the simplest thing for me is to not allow clock out unless you have clocked in no matter if they are done together.

 

[Technics]

Not sure how this happens but maybe you can modify the clockout button to force the user to also clockin before closing the form. Just a thought...

What you are saying is exactly what I am trying to achieve by if an employee opens the form at the beginning of their work schedule the Clock Out button will not be available, only the Clockin button will be available. I can achieve this but when that same employee opens the form to Clock out the clockout button is still unavailable. I am trying to figure out how to make it available once that same employee opens the form for a second time later in the day to clock out.

 

[RonPaii]

Provided a punch the time clock button. Let your hours processing function handle matching in and out punches.

 

[Gasman]

Show how you are disabling the clock out button?

 

[Technics]

Show are you disabling the clock out button?

Yes, I disabled clock out button in properties and tried vb code to make it available when form reopened but still greyed out. I am trying to make it so an employee has to clockin before they can clock out. It doesn't matter when that is. In other words if they have been at work for eight hours and forgot to clock in they still have to clockin before they can clock out. The issue is when they clockin the form closes then they reopen it to clock out but the clockout button is still greyed out even though they have clock in.

 

[Gasman]

So disable in code and leave enabled in properties. Obviously your code is incorrect as it would just as easy work that way.
I would disable on clickin being empty.

 

[plog]

Maybe I should be looking at why that errors...

Definitely. I mean your solution to fallible employees is relying on those fallible employees. And you don't need to, you can catch these errors with the system itself.

I'd let them clock out and notify them via a message box that they need to contact an admin to fix it. Further, I'd have that report runner be notified if there is an issue with the data when they try to run their report. You need to fail gracefully.

 

[theDBguy]

What you are saying is exactly what I am trying to achieve by if an employee opens the form at the beginning of their work schedule the Clock Out button will not be available, only the Clockin button will be available. I can achieve this but when that same employee opens the form to Clock out the clockout button is still unavailable. I am trying to figure out how to make it available once that same employee opens the form for a second time later in the day to clock out.

I didn't realize that is the case because in the screenshot you provided, I didn't see anything was disabled.

 

[Technics]

I didn't realize that is the case because in the screenshot you provided, I didn't see anything was disabled.

Yea, since I couldn't get it to work I enabled it.

 

[Ken Sheridan]

The way I have handled clocking in or out is to firstly have a login form in which the user enters their login details , and then presses a Clock In/Out button. The code in this button's Click event procedure is:

Private Sub cmdClockInOut_Click()

    Dim strArgs As String
    Dim strCriteria As String
    
    strCriteria = "EmployeeID = " & Me.cboEmployee & " And TimeEnd  IS NULL"
    
    If Not IsNull(Me.cboEmployee) Then
        If IsNull(DLookup("EmployeeID", "TimeLog", strCriteria)) Then
            strArgs = "ClockIn"
        Else
            strArgs = "ClockOut"
        End If
    Else
        Exit Sub
    End If

    Me.Visible = False
    DoCmd.OpenForm "frmTimeClock", WindowMode:=acDialog, OpenArgs:=strArgs
    
End Sub

The code in the frmTimeClock form's module, which next opens is:

Option Compare Database
Option Explicit

Private Sub cmdCancel_Click()

    DoCmd.Close acForm, "frmLogin"
    DoCmd.Close acForm, Me.Name
    Forms("frmOpen").Visible = True

End Sub

Private Sub cmdConfirm_Click()

    Dim strSQL As String
    
    If Me.OpenArgs = "ClockIn" Then
        strSQL = "INSERT INTO TimeLog(EmployeeID,WorkDate,TimeStart) " & _
            "VALUES(" & Forms("frmLogin").cboEmployee & _
            ",#" & Format(Me.txtDate, "yyyy-mm-dd") & "#" & _
            ",#" & Format(Me.txtTime, "hh:nn:ss") & "#)"
            
        CurrentDb.Execute strSQL, dbFailOnError
    Else
        strSQL = "UPDATE TimeLog " & _
            "SET TimeEnd = #" & Format(Me.txtTime, "hh:nn:ss") & "# " & _
            "WHERE EmployeeID = " & Forms("frmLogin").cboEmployee & _
            " AND TimeEnd IS NULL"
            
        CurrentDb.Execute strSQL, dbFailOnError
    
    End If
    
    DoCmd.Close acForm, "frmLogin"
    DoCmd.Close acForm, Me.Name
    Forms("frmOpen").Visible = True

End Sub

Private Sub Form_Close()

    DoCmd.Close acForm, "frmLogin"
    
End Sub

Private Sub Form_Open(Cancel As Integer)

    ' note that values such as #12:05:00 AM# in the following code are because
    ' Access formats time literals in AM/PM format,and does not allow time literals
    ' such as #00:05# representing a duration of 5 minutes to be entered in code
    
    Dim dtmDateTime As Date
    
    If Me.OpenArgs = "ClockIn" Then
        Me.lblClockInOut.Caption = "Clock in at:"
    Else
        Me.lblClockInOut.Caption = "Clock out at:"
    End If
    
    ' round time  to nearest 5 minutes slot
    If Format(RoundDown(VBA.Now, #12:02:30 AM#), "ss") = "00" Then
        dtmDateTime = RoundDown(VBA.Now, #12:05:00 AM#)
    Else
        dtmDateTime = RoundDown(VBA.Now, #12:05:00 AM#) + #12:05:00 AM#
    End If
    
    Me.txtDate = dtmDateTime
    Me.txtTime = TimeValue(dtmDateTime)
    
End Sub

Consequently a user can only clock in if they have not already done so since last clocking out, and can only clock out if they have already clocked in, but not yet clocked out.

The following function is called in the above to round the time down.

Public Function RoundDown(dblVal As Double, dblTo As Double) As Double

    Dim lngTestValue As Long
    Dim dblTestValue As Double
    Dim dblDenominator As Double
    
    dblDenominator = dblTo
    dblTestValue = dblVal / dblDenominator
    lngTestValue = Int(dblTestValue)
    RoundDown = lngTestValue * dblTo
 
End Function

 

[The_Doc_Man]

The method I used was a bit different. When a person would launch my app, I would do a DLookup for their last in/out action, whatever it was, based solely on person ID and MAX(date/time). I would see whether the last action was an IN or and OUT. If they wanted to log IN, either the last action was an OUT and nothing much was wrong, or it was an IN and I could pop up a message box saying there was an anomaly - two INs with no intervening OUT, and therefore please notify the DB admin team. Ditto, if they were clocking out and the last action was an IN, no harm because no foul - but if the last action was an OUT then up popped the message to call the DB admin. I didn't add this next part, but if I wanted, I would have been able to populate a label or unbound text box with the message, "Thanks, Dave, for logging in." Or "out" as the case could have been. I could also, had I wanted, automate the notification that I had two identical actions in a row at different times. The point is, if you have a person ID, a date/time of transaction, and the sense of the transaction, you have enough info to take any of several different form and notification options.

 

[MarkK]

My 2c is get the employee to provide a name, and then present--as a subform--the correct UI for the active task for that employee for that time period. Then each UI element is completely focused on its own responsibility and everything gets way simpler.
• The MainForm is responsible for 1) employee selection, 2) logic to determine the current task, and 3) loading the correct UI for that task.
• The ClockIn subform is responsible for 1) clocking in.
• The ClockOut subform is responsible for 1) clocking out.

The complexity in this problem arises from the MainForm presenting--at the same time--a UI for two conflicting and potentially dependent operations, ClockIn and ClockOut. As a result, after your MainForm performs step 2) logic to determine current task, you are forced to write a bunch of UI state code to enable/disable, select/deselect, and/or validate against a moving target.

If you just swap out subforms, each completely focussed on a single task, then everything becomes much simpler. MainForm UI state management on Employee selection becomes...
1) LoadSubform
...not...
1) enable correct textbox
2) ensure correct textbox focus
3) disable incorrect textbox
4) enable correct button
5) disable incorrect button

This also simplifies your bindings. The mainform can be bound to tblEmployee. Subforms can be bound to tblHours. If you set LinkMasterFields and LinkChildFields of your subform control, the EmployeeID from the MainForm can now be provided automatically to the new record in tblHours.

Also, for very simple calculation of hours, use this table structure...
tblHours
HoursID (PK)
EmployeeID (FK)
Direction (-1 for clock in, +1 for clock out)
DateTime

And then the query to calculate time...

SELECT Sum(Direction * DateTime) As TotalHours
FROM tblHours
WHERE EmployeeID = [prmEEID]
    AND DateTime BETWEEN [prmStartDate] AND [prmEndDate]

Using Direction = -1 and +1 as a multiplier for ClockIn and ClockOut respectively has the effect of negating the ClockIn value, so your Sum() subtracts ClockIn from ClockOut.

 

[The_Doc_Man]

Just as an added thought, my biggest problem for login/logout was when someone failed to log out, but just left the system running and still connected for a while - at which time the domain's policies would impose an automatic domain logout - which guaranteed that the DB was not properly logged out.

 

[arnelgp]

copy MainMenu form and TCForm form from the attached.

 

[Technics]

copy MainMenu form and TCForm form from the attached.

Hey arnelgp, thanks. As usual, you have it down. It works and is exactly what I'm looking for but when I choose an employee and do just a clock in, forgetting to do a clock out, which I'm sure someone will do, and look at the report, I am getting a run-time error 94, Invalid Use of Null. I took a look at debug but just not sure what needs to be done to not mess up your fine work. Error occurs in ModuleDay.