Time Clock Code

[MakaiKing0]

Ok before you tell me to use a text box and [text_box] = Now() with a timer of 1 sec on my form.... Don't, wrong type of time clock. Lol



I'm actually just now learning Access for a project for work, took a couple days and read through Access 2010: The Missing Manual, great book very informative. Except when it comes to VBA. I'm a software developer by profession, so I fully understand software very quickly, but I need an information source to get a better grip on how things work. Never took the time to learn VBA. Trying to learn it now.

So enough back story, here's the first part of my project:

I'm making a time clock for clicking in and out of work for the associates of my site. I've seen several examples and many that look very crude. But I've opted to go with a very simplistic design for the moment. The clock will be setup on a form, obviously, with a ComboBox that contains the associates name. Under that will be a simple text field to enter a password. Then 3 little boxes, one for Clocking In, Clocking Out and on Break. Each with it's own little code. I know this isn't the most compact and probably not the most efficient but that was what was requested. So until I can change the higher up's minds that's what we're going with.

I have 2 tables, one called Associates and the other Times. The Associates table contains the following:
- ID (Key)
- Name (Text)
- Privileges (Boolean)
- Password (Number) - (A simple 4 digit pin)

The Times table contains:
- ID (Key)
- Associate_ID (Number)
- TimeStamp (Date/Time)
- Break (Text) - (Will contain one of 3 text values: Break, In, or Out; for another use later)

I currently have a 1-Many relationship set for the ID field of the Associates table to the Associate_ID field of the Times table.

Here is what I need:
When the associate selects their name from the combo box and enters their password, the buttons check the Associates table and verifies that the correct name and password is used. If so then enter the selected associate's ID in the Associate_ID field, create a time stamp in the TimeStamp field and place it's corresponding value in the Break field (ie, Clock In = In, Clock Out = Out, Break = Break)

I would like this done completely in VBA, as much as I do enjoy the Macro builder, the purpose of this is to help me become more accustomed to VBA and learn the code, so seeing an example like this will help in figuring out VBA.

P.S. Any suggestions on a better design for this concept is also appreciated.

Thanks,
Makai

[June7]

Can use DLookup to validate associate.

If IsNull(DLookup("Password","tablename","Associate_I D=" & Me.Associate_ID & " AND Password='" & Me.Password & "'") ) Then
MsgBox "Invalid user/password."
Else
'do something like determine if this is In or Out or Break then close form
End If

Why is tracking break necessary? Aren't breaks paid time?
Do you want to rely on employee to correctly select In or Out?

Go one step at a time. Get the validation code working then move on to the time stamping.

[MakaiKing0]

I figured it had something to do with DLookup....

Tracking of breaks was a request by my employers, thus it is necessary.

I don't think that it's necessary to rely on the employee to correctly select in or out. That can be setup as a single button that simply provides a time stamp I suppose and then for later reports and such I can use some sort of equation to determine everything else. The Break setting could be changed to a simple Boolean that states whether the stamp was for a break or not....

[June7]

Using calculation later to determine the In and Out pairs daily could fail in cases where employee checks In but never checks out or checks out and never returns.

[Rawb]

I'm actually working on a Timeclock App myself!

Originally, I went with a table setup similar to yours, however, after putting in some data and trying to generate Reports off of it, I decided to make a few changes to the Times Table (in mine, I call it the Timeclock Table):

Timeclock Table:
TCEntryID - Autonumber, PK
TCUserID - Long Integer, FK (to Users.UserID), Required
TCDateIn - Date/Time, Formatted as Short Date, Required
TCTimeIn - Date/Time, Formatted as Long Time, Required
TCDateOut - Date/Dime, Formatted as Short Date, Not Required
TCTimeOut - Date/Time, Formatted as Long Time, Not Required
TCModUserID - Long Integer, Not Required
TCModTimestamp - Date/Time, Not Required

Basically, this gives me a "clock in" and "clock out" Field for each Record, making it much easier to calculate hours worked and to modify if, say, an employee forgot to clock in/out. And it makes it easier to generate the various "hours worked" Reports.

Also, instead of having a username/password setup, I just assign a (random) PIN to each user that they use to log entries. Its much easier (and quicker) to type and people (my users at least) are more used to keeping a PIN secret than a password so they're less likely to share it with others.

The VBA of my clock in/out button is as follows:

Private Sub Button_Enter_Click()
  On Error GoTo Error_Button_Enter_Click

  DoCmd.Hourglass True

  Dim db1 As DAO.Database

  Dim rstLoginAttempts As DAO.Recordset
  Dim rstTimeclock As DAO.Recordset
  Dim rstUsers As DAO.Recordset

  Dim boolRst As Boolean
  Dim dteNow As Date
  Dim nbrUserID As Long

  Set db1 = CurrentDb()

  Set rstLoginAttempts = db1.OpenRecordset("SELECT * FROM LoginAttempts WHERE False", dbOpenDynaset)
  Set rstUsers = db1.OpenRecordset("SELECT [UserID] FROM Users WHERE [UserPIN]='" & Me!PIN & "'", dbOpenForwardOnly)

  dteNow = fGetServerTime() ' Get the current time from the local Domain Controller (to prevent a user from changing their local time if they're late)

  With rstLoginAttempts
    .AddNew
    !AttemptDate = Format(dteNow, "Short Date")
    !AttemptTime = Format(dteNow, "Long Time")
    !AttemptSysUser = Get_Username() ' Get Domain user currently logged on to workstation
    !AttemptSysWorkstation = Get_Hostname() ' Get workstation's WINS name
    .Update
  End With

  If rstUsers.RecordCount = 0 Then
    ' If the PIN entered doesn't match that of any users
    MsgBox "Incorrect PIN entered. Please try again."

    Me!PIN.SetFocus
  Else
    ' If a valid PIN has been entered

    ' Search for a timeclock entry for the user with no clock-out time
    Set rstTimeclock = db1.OpenRecordset("SELECT * FROM Timeclock WHERE [TCUserID]=" & rstUsers("UserID") & " AND [TCDateOut] Is Null AND [TCTimeOut] Is Null", dbOpenDynaset, dbSeeChanges)
    boolRst = True

    If rstTimeclock.RecordCount = 0 Then
      ' If an entry with no clock-out time IS NOT found, create a new clock-in entry
      With rstTimeclock
        .AddNew
        !TCUserID = rstUsers("UserID")
        !TCDateIn = Format(dteNow, "Short Date")
        !TCTimeIn = Format(dteNow, "Long Time")
        .Update
      End With

      MsgBox "Clocked in at " & Format(dteNow, "h:mm AMPM") & "."
    Else
      ' If a clock-in entry is found, add a corresponding clock-out entry by filling in the two blank fields in the Record
      With rstTimeclock
        .Edit
        !TCDateOut = Format(dteNow, "Short Date")
        !TCTimeOut = Format(dteNow, "Long Time")
        .Update
      End With

      MsgBox "Clocked out at " & Format(dteNow, "h:mm AMPM") & "."
    End If

    ' Reset the Form for the next user/entry
    Me!PIN.SetFocus
    Me.PIN = vbNullString
  End If

Function_Closing:
  On Error Resume Next

  DoCmd.Hourglass False

  If boolRst = True Then
    boolRst = False

    rstTimeclock.Close
    Set rstTimeclock = Nothing
  End If

  rstLoginAttempts.Close
  Set rstLoginAttempts = Nothing

  rstUsers.Close
  Set rstUsers = Nothing

  Set db1 = Nothing

  On Error GoTo 0

  Exit Sub

Error_Button_Enter_Click:
  ' If there's an error, save it to the database for debugging purposes
  Log_Error Err.Number, Err.Description, nbrUserID, "frmTimeclock", "Button_Enter_Click", nbrUserID & ";" & Now()

  ' Alert the user of an error
  MsgBox "Unable to clock in/out. Please contact your Systems Administrator."

  Resume Function_Closing
End Sub

I'm currently working on adding encryption support so I can secure the PINs. It's a pain though, because about half of the locations are running Access 2000 which doesn't natively support the Cryptographic API :/

[Rawb]

Using calculation later to determine the In and Out pairs daily could fail in cases where employee checks In but never checks out or checks out and never returns.

That was one of the problems I ran into when generating my Reports (and thus why I changed my Table design).

With my current design, you can filter out items where the employee never clocked out by just looking for Records where [TCDateOut] Is Not Null. You can also query who's currently logged in (or who forgot to log out) by doing the opposite ([TCDateOut] Is Null).

One of the drawbacks with my setup though, is the inability to handle Breaks. We currently have to clock out for break and then clock back in afterwards.

[orange]

Rawb,

Just looking at your form and wondering (re your Break issue) could you have another button

eg Break Start/Stop that could record the times and not force a full Clock Out/Clock In?

[MakaiKing0]

Yah, I wasn't sure until you confirmed it but I had a feeling that keeping individual records of each log in and out would become a hassle later on.... The reason I'm using an Associates table with username and pass(Pin #) is because there is going to be further functionality on top of just the time clock. But that's a story for another day. For the moment I simply want an efficient and working method to clock people in and out and insure their security. I'm using Microsoft Access 2010.

Also your setup doesn't allow for a Break log, but I suppose that can be integrated fairly easily. Although.... only half of your code makes sense to me.... lol I really do need to learn VBA

[Rawb]

Also your setup doesn't allow for a Break log, but I suppose that can be integrated fairly easily. Although.... only half of your code makes sense to me.... lol I really do need to learn VBA

Yeah, I was thinking about it a little over the weekend and it would probably be possible to make a separate "Break" Table with a similar layout. Then you could just take the Timeclock Table values - Break Table values to figure hours.

Also, some of my code won't make sense because I'm using several custom functions that aren't explained. The fGetServerTime(), Get_UserID(), Get_Username(), and Get_Workstation() functions, as well as the Log_Error Sub are all custom VBA Code, not built-in Functions.