Records

[MS_Access_Amature]

This is my function:

Function PunchInOrOut(sMyCallingForm As String, MyPunchInOrOut As String, MyUserCodeAs String, MyUsername As String, MyPunchDate As Date, MyPunchTime As Date, ForFutureUse1$, ForFutureUse2$, ForFutureUse3$)

On Error GoTo MyErrorControl

Dim DB As DAO.Database, MyPunchID As Long
Set DB = CurrentDb

Select Case MyPunchInOrOut
Case "IN"

' Does a punch OUT exist for current user and current date
MyPunchID = Nz(DLookup("[PunchID]", "Punches", "

 = '" & MyUserCode & "' AND [DateofWork] = #" & MyPunchDate & "# AND IsNull([TimeIn]) = True"), 0) 
 
[COLOR=seagreen]' A punch OUT record exist, then update the record[/COLOR]
[COLOR=navy]If[/COLOR] MyPunchID > 0 [COLOR=navy]Then[/COLOR]
[INDENT]DB.Execute "UPDATE Punches SET [TimeIn] = #" & MyPunchTime & "# WHERE [PunchID] = " & MyPunchID
[/INDENT][COLOR=navy]Else[/COLOR]
[INDENT]DB.Execute "INSERT INTO Punches ([code], [DateOfWork], [TimeIn]) VALUES ('" & MyUserCode & "', #" & MyPunchDate & "#, #" & MyPunchTime & "# )"
[/INDENT][COLOR=navy]End If[/COLOR]
 
Forms(sMyCallingForm).ShortLog = "Last Punch IN: " & UCase(MyUsername) & " on " & MyPunchDate & " " & MyPunchTime
[/INDENT][COLOR=navy]Case[/COLOR] "OUT"
[/INDENT][INDENT][INDENT][COLOR=seagreen]' Does a punch IN exist for current user and current date[/COLOR]
MyPunchID = Nz(DLookup("[PunchID]", "Punches", "[code] = '" & MyUserCode & "' AND [DateofWork] = #" & MyPunchDate & "# AND IsNull([TimeOut]) = True"), 0)
 
[COLOR=navy]If[/COLOR] MyPunchID > 0 [COLOR=navy]Then[/COLOR]
[/INDENT][/INDENT][INDENT][INDENT][COLOR=seagreen]' Yes - then update the existing punch in with this punch out[/COLOR]
DB.Execute "UPDATE Punches SET TimeOut = Now() WHERE [PunchID] = " & MyPunchID
[/INDENT][COLOR=navy]Else[/COLOR] 
[/INDENT][INDENT][INDENT][COLOR=seagreen]' No - then create a new record without a punch in[/COLOR]
DB.Execute "INSERT INTO Punches (Code, DateOfWork, TimeOUT) VALUES ('" & MyUserCode & "',#" & MyPunchDate & "#,#" & MyPunchTime & "# )"
[/INDENT][COLOR=navy]End If[/COLOR] 
 
Forms(sMyCallingForm).ShortLog = "Last Punch OUT: " & UCase(MyUsername) & " on " & MyPunchTime & " " & Now()
[/INDENT]
 
[COLOR=navy]Case Else[/COLOR]
[INDENT]MsgBox "Sorry, option not implemented: " & MyPunchInOrOut
[/INDENT][COLOR=navy]End Select[/COLOR]
 
[COLOR=navy]Set[/COLOR] DB = [COLOR=navy]Nothing[/COLOR]

[COLOR=navy]Exit Function[/COLOR]
 
MyErrorControl:
 
DoCmd.SetWarnings [COLOR=navy]True[/COLOR]
 
[COLOR=navy]Select Case[/COLOR] Err.Number
[COLOR=darkslateblue]Case[/COLOR] 0
[COLOR=navy]Resume Next[/COLOR]
[COLOR=darkslateblue]Case[/COLOR] 52 [COLOR=seagreen]'cannot find a file[/COLOR]
[COLOR=navy]Resume Next[/COLOR]
[COLOR=navy]Case Else[/COLOR]
MsgBox "Error number " & Err.Number & ": " & Err.Description
[COLOR=navy]Resume Next[/COLOR]
[COLOR=navy]Exit Function[/COLOR]
[COLOR=navy]End Select[/COLOR]
[COLOR=navy]End Function[/COLOR]
 
I want to add to it for when people forget to punch in/out. For example: Say John punches in. He leaves the day and forgot to punch out. The next day he comes in and punches in again. The program is allowing this. I want it to be able to prevent punching in twice without punching out. And your can't punch out unless in your last record you punched out. So if John comes in the next day and punches in i want the program to automatically punch him out and put in 0:00 for yesterdays punch out so he could punch in for today. How can i go about this??

 

[boblarson]

First up, let's get the parameters worked out. You have ForFutureUse1$, etc. which should not be there. Instead let's use a Parameter Array. This allows you to pass whatever you require (as many parameters as needed) without knowing how many you will need in advance.

So, change this:

[COLOR=#000080]Function[/COLOR] PunchInOrOut(sMyCallingForm[COLOR=navy] As String[/COLOR], MyPunchInOrOut [COLOR=navy]As String[/COLOR], MyUserCode[COLOR=navy]As String[/COLOR], MyUsername [COLOR=navy]As String[/COLOR], MyPunchDate[COLOR=navy] As Date[/COLOR], MyPunchTime [COLOR=navy]As Date[/COLOR], ForFutureUse1$, ForFutureUse2$, ForFutureUse3$)

to this:

[COLOR=#000080]Function[/COLOR] PunchInOrOut(ParamArray MyArr() As Variant)
' 0 = [COLOR=magenta][B]MyCallingForm[/B][/COLOR]
' 1 = [COLOR=darkred][B]MyPunchInOrOut[/B][/COLOR] 
' 2 = [COLOR=blue][B]MyUserCode[/B][/COLOR]
' 3 = [COLOR=seagreen][B]MyUsername [/B][/COLOR]
' 4 = [COLOR=red][B]MyPunchDate[/B][/COLOR]
' 5 = [COLOR=darkorchid][B]MyPunchTime[/B][/COLOR]

Then you can change the rest of the code to:

[COLOR=#000080]Function[/COLOR] PunchInOrOut(ParamArray MyArr() As Variant)
' 0 = [COLOR=magenta][B]MyCallingForm[/B][/COLOR]
' 1 = [COLOR=darkred][B]MyPunchInOrOut[/B][/COLOR] 
' 2 = [COLOR=blue][B]MyUserCode[/B][/COLOR]
' 3 = [COLOR=seagreen][B]MyUsername [/B][/COLOR]
' 4 = [COLOR=red][B]MyPunchDate[/B][/COLOR]
' 5 = [COLOR=darkorchid][B]MyPunchTime[/B][/COLOR]
 
[COLOR=#000080]On Error GoTo[/COLOR] MyErrorControl
 
[COLOR=navy]Dim[/COLOR] DB [COLOR=navy]As[/COLOR] DAO.Database,[COLOR=black] MyPunchID[/COLOR] [COLOR=navy]As Long[/COLOR]
[COLOR=navy]Set[/COLOR] DB = CurrentDb 
[INDENT][COLOR=navy]Select Case[/COLOR] [COLOR=darkred][B]MyArr(1)[/B][/COLOR]
[COLOR=navy]Case[/COLOR] "IN"
[INDENT][COLOR=seagreen]' Does a punch OUT exist for current user and current date[/COLOR]
MyPunchID = Nz(DLookup("[PunchID]", "Punches", "[ code ] = '" & [B][COLOR=blue]MyArr(2) [/COLOR][/B]& "' AND [DateofWork] = #" & [B][COLOR=red]MyArr(4)[/COLOR][/B] & "# AND IsNull([TimeIn]) = True"), 0) 
 
[COLOR=seagreen]' A punch OUT record exist, then update the record[/COLOR]
[COLOR=navy]If[/COLOR] MyPunchID > 0 [COLOR=navy]Then[/COLOR]
[INDENT]DB.Execute "UPDATE Punches SET [TimeIn] = #" & [COLOR=darkorchid][B]MyVar(5)[/B][/COLOR] & "# WHERE [PunchID] = " & MyPunchID
[/INDENT][COLOR=navy]Else[/COLOR]
[INDENT]DB.Execute "INSERT INTO Punches ([ code ], [DateOfWork], [TimeIn]) VALUES ('" & [B][COLOR=blue]MyArr(2) [/COLOR][/B]& "', #" & [B][COLOR=#ff0000]MyArr(4)[/COLOR][/B]& "#, #" & [B][COLOR=#9932cc]MyVar(5) [/COLOR][/B]& "# )"
[/INDENT][COLOR=navy]End If[/COLOR]
 
Forms([B][COLOR=magenta]MyArr(0)[/COLOR][/B]).ShortLog = "Last Punch IN: " & UCase([COLOR=seagreen][B]MyArr(3)[/B][/COLOR]) & " on " & [B][COLOR=#ff0000]MyArr(4)[/COLOR][/B]& " " & [B][COLOR=#9932cc]MyVar(5)[/COLOR][/B]
[/INDENT][COLOR=navy]Case[/COLOR] "OUT"
[/INDENT][INDENT][INDENT][COLOR=seagreen]' Does a punch IN exist for current user and current date[/COLOR]
MyPunchID = Nz(DLookup("[PunchID]", "Punches", "[ code ] = '" & [B][COLOR=blue]MyArr(2) [/COLOR][/B]& "' AND [DateofWork] = #" & [B][COLOR=#ff0000]MyArr(4)[/COLOR][/B]& "# AND IsNull([TimeOut]) = True"), 0)
 
[/INDENT][COLOR=navy]If[/COLOR] MyPunchID > 0 [COLOR=navy]Then[/COLOR]
[/INDENT][INDENT][INDENT][COLOR=seagreen]' Yes - then update the existing punch in with this punch out[/COLOR]
DB.Execute "UPDATE Punches SET TimeOut = Now() WHERE [PunchID] = " & MyPunchID
[/INDENT][COLOR=navy]Else[/COLOR] 
[/INDENT][INDENT][INDENT][COLOR=seagreen]' No - then create a new record without a punch in[/COLOR]
DB.Execute "INSERT INTO Punches (Code, DateOfWork, TimeOUT) VALUES ('" & [B][COLOR=blue]MyArr(2) [/COLOR][/B]& "',#" & [B][COLOR=#ff0000]MyArr(4) [/COLOR][/B]& "#,#" & [B][COLOR=#9932cc]MyVar(5) [/COLOR][/B]& "# )"
[/INDENT][COLOR=navy]End If[/COLOR] 
 
Forms(sMyCallingForm).ShortLog = "Last Punch OUT: " & UCase([B][COLOR=#2e8b57]MyArr(3)[/COLOR][/B]) & " on " & [B][COLOR=#9932cc]MyVar(5) [/COLOR][/B]& " " & Now()
[/INDENT][COLOR=navy]Case Else[/COLOR] 
[INDENT]MsgBox "Sorry, option not implemented: " & MyPunchInOrOut
[/INDENT][COLOR=navy]End Select[/COLOR]
 
[COLOR=navy]Set[/COLOR] DB = [COLOR=navy]Nothing[/COLOR]
 
[COLOR=navy]Exit Function[/COLOR]
 
MyErrorControl:
 
DoCmd.SetWarnings [COLOR=navy]True[/COLOR]
 
[COLOR=navy]Select Case[/COLOR] Err.Number
[COLOR=darkslateblue]Case[/COLOR] 0
[COLOR=navy]Resume Next[/COLOR]
[COLOR=darkslateblue]Case[/COLOR] 52 [COLOR=seagreen]'cannot find a file[/COLOR]
[COLOR=navy]Resume Next[/COLOR]
[COLOR=navy]Case Else[/COLOR]
MsgBox "Error number " & Err.Number & ": " & Err.Description
[COLOR=navy]Resume Next[/COLOR]
[COLOR=navy]Exit Function[/COLOR]
[COLOR=navy]End Select[/COLOR]
[COLOR=navy]End Function[/COLOR]

 

[boblarson]

Now, Is there a problem with them punching in and forgetting to punch out? If so, then why would setting it to be 00:00 be right? It should open up to have them put in the valid value. Shouldn't it?