Hi,
I have a database for users to clock-in and clock-out that is working, but I need to add a few safe guards to it yet. Note, the second form ( frmEmp ) is opened from the frmMain and on some of the frmEmp there is data in there for testing, so if you try it - you'll see what I mean.

What I need accomplished is this;

1) I need to be able to prevent a user from clocking in twice if they haven't clocked out yet. When a user selects their name from the EmpID combo box, it will add the record to the "ListIncomplete" listbox, if they select their name from the "ListIncomplete" listbox, it moves it to the ListIncomplete list box and works as it should.

Lets assume that the user is already clocked in, if they select their name but don't clock-in, it will add it to the combo box without a time stamp. Note: You will not see the record added until you close the form frmEmp and go back into it. It places the record into the "ListIncomplete" list box without a time.

There will be multiple users at each computer, each computer has it's own Facilitator/Supervisor assiqned. No user will have to change the Facilitator at their PC, so they will be able to just leave the frmEmp open all day.


2) I also need to have it where the List boxes only show entries for the current month only.

Note, the second form ( frmEmp ) is opened from the frmMain and on the frmEmp there is data in there you can use for testing ( frmMain ) is the starting form. Not all of the Facilitators don't have user data entered in, but there are some that do or you can enter your own. Also, the Export button to Excel does work, but I don't have the Excel file attached.

Please I'm trying to keep this simple for the users to use, so I'm not expecting a total rewrite of the database. I'm just looking for the answer to my (2) questions.

Please see the attached.

Thank you!

Kelley -

1. In the code for your Clock In and Clock Out buttons, you should check to see if there is a record in the ClockTimeIncomplete, and if it has a time stamp. If the employee is clocking in, then there should be no records in that table. If the employee is clocking out, then there should be a record in that table with a date/time stamp before Now(). You need to decide what to do if the condition isn't met. For example, if the employee is Clocking In, then if there already is a incomplete record for them, do you want to give them a message saying they are already clocked in? If they forgot to clock out, then will you give them a way to fix that? Similar thoughts for clocking out when there is no record of them clocking in...

2. You can restrict the entries to the current month (remember to restrict to the current year and current month) in your row source. Just add criteria that Year([YourDateField]) = Year(Date()) AND Month([YourDateField]) = Month(Date())

I gave you a working solution for this requirement to prevent clocking in and out without finishing an existing time sheet in your previous post on this subject. Previous thread here.

Why not see if you can implement it into YOUR database. It's really pretty simple. Code on the controls in the time sheet form check for the existence of previous records and alerts the user to that fact, if one is found. I realize you think the entire approach is too "complex" for your users, but that part, at least, should work for you in a "simpler" database because it uses the same contols and events.

George, You're right - it looks like it's all here for me ( created by an expert! ). I'll see what I can do.

Thank you again!

Sincerely,

Daryl,

Thank you for your response and expample, I sure need all the help I can get.

Daryl,

A very nice description and easy to follow. I'm just finishing up at work and about to leave. I'll be working on it tonight and for sure will study/follow your procedure.

Thank you,

Hi Daryl

Currently my Row Source has sql information in it and I can't quite get it working correctly. I was able to get it working where it did only show the current month correctly, but when it worked it seemed that my "Sum" Expression that I have in the query no longer worked.

Here's my current sql that is in the row source.

SELECT tblTempTime.FacilitatorID, tblEmployee.FirstName, tblEmployee.MiddleInitial, tblEmployee.LastName, Round(Sum(DateDiff("n",[StartTime],[EndTime])/60),2) AS TotalHrs
FROM (tblEmployee INNER JOIN tblFacilitators ON tblEmployee.FacilitatorID = tblFacilitators.FacilitatorID) INNER JOIN tblTempTime ON (tblFacilitators.FacilitatorID = tblTempTime.FacilitatorID) AND (tblEmployee.EmpID = tblTempTime.EmpID)
GROUP BY tblTempTime.FacilitatorID, tblEmployee.FirstName, tblEmployee.MiddleInitial, tblEmployee.LastName, tblTempTime.StatusID
HAVING (((tblTempTime.FacilitatorID)=[Forms]![frmMain]![FacilitatorID]) AND ((tblTempTime.StatusID)=1);

I tried adding this to the end of the query, but that didn't work

Where Year([Starttime]) = Year(Date()) AND Month([Starttime]) = Month(Date())

I wasn't sure what I should put in so I tried "Where"

Do you know where or how I should add this?

Thank you,

Kelly -

Try this:

SELECT tblTempTime.FacilitatorID, tblEmployee.FirstName, tblEmployee.MiddleInitial, tblEmployee.LastName, Round(Sum(DateDiff("n",[StartTime],[EndTime])/60),2) AS TotalHrs
FROM (tblEmployee INNER JOIN tblFacilitators ON tblEmployee.FacilitatorID = tblFacilitators.FacilitatorID) INNER JOIN tblTempTime ON (tblFacilitators.FacilitatorID = tblTempTime.FacilitatorID) AND (tblEmployee.EmpID = tblTempTime.EmpID)
WHERE (((tblTempTime.FacilitatorID)=[Forms]![frmMain]![FacilitatorID]) AND ((tblTempTime.StatusID)=1)
AND Year([Starttime]) = Year(Date()) AND Month([Starttime]) = Month(Date())
GROUP BY tblTempTime.FacilitatorID, tblEmployee.FirstName, tblEmployee.MiddleInitial, tblEmployee.LastName, tblTempTime.StatusID;

You should be able to click the ellipses (...) at the end of the row and have it bring the query up in query design where you can test it out to make sure it gives you what you want. You can adjust it there if query design is easier for you to work with. When you close the query design window, it will ask if you want to save the changes to the record source, so answer Yes.

Hi Daryl,

I have tried the example sql code and I'm unable to get it to work correctly. I will have to take another look at what my control source really has in it. What I did was give you what my initial query had in it and it looks to be different than what the control source states.

I'm still tring to figure out the conditional statement for the onClick() event of the start time button. I still can't get it. I tried the example from George's updated db, but I notice that the procedure for controlling multiple users from signing in isn't doing what I need it to do - allow multiple users to clock-in simultaniously. The code only allows for (1) user to clock-in/clock-out at a time. I need multiple users to be able to clock-in and then later in the day come back and clock out.

If the user's clocked in, where their information is in the Incompleted Time list box, prevent this "button click" from adding the same user into the Incompleted Time list box.

I'm not sure of the vb syntax to use?

I will keep trying, the facilitators want to use it starting Thursday.

If you or anyone can help me out, I would be so greatful.

Thank you,

Kelly -

Here is one way to code the ClockIn button to prevent someone from clocking in twice. You woud do something similar to check if they are clocked in before clocking them out.

Hi Daryl,

I thank you for your time and help.

I was able to try the code, but for some reason on every line that contained "Loop" it had an issue with during the Debug and Compile. I then tried remarking them out, but it didn't like it then. All the lines that are remarked out are from me, they were not in here when I tried to run the compile.

Private Sub cmdClockIn_Click()
' Check to see if there is already a clock-in record
Dim rstIncomplete As DAO.Recordset
'Dim intLoop As Integer
Set rstIncomplete = Me.ListIncomplete.Recordset
'intLoop = 0
'Do While intLoop < rstIncomplete.RecordCount
If rstIncomplete!EmpID = Val(Me.EmpID.Column(0)) Then
MsgBox ("You are already clocked in")
ClockedIn = True
End If
'intLoop = intLoop + 1
'LoopIf ClockedIn = False Then
Me.SetStart = Now()
DoCmd.GoToRecord , , acNewRec
'End If

End Sub

Kelly -

intLoop is just an integer variable. Maybe you have something else in your code with that name. If so, change all the 'intLoop' instances to something else, maybe LoopCounter, iLoop. They need to be there for the code to work...

Hi Daryl,

No I'm sure I don't have anything with that name. BTW; I'm using Access2007, but running in compatibilty mode ( not sure if the version make a difference )?

Thank you,

Kelly -

What is the issue or error when you compile? I am on Access 2003, so I don't know about if Access 2007 has any issues. I can't imagine that, as this is just a normal variable. Please uncomment the intLoop lines, then re-compile and tell us where the issue is and what the compile message is. Maybe compact/repair your database to see if that clears up a random issue.

Daryl,

Here's the compile error that I receive without any commecnted lines

If the image doesn't show, I did attach it also.

Thank you

OK, just missing a carriage return...

Change:
LoopIf

To:
Loop
If

Daryl,

That worked!

I'm sorry for not having a return in the right placed. When I copied it from your example, it was all together.

It works great, but there is still a problem that when you just lets say, highlight a user, but don't click the clock-in button. It will still throw them in the Incompleted Time list box. I'm thinking possibly because of some code I may have in the cboBox?

Otherwise, it's wonderful - very good work.

Thank you,

Kelly -

You have a lot of pieces of code firing for this app. I expect it is from one of the 'Dirty = False' pieces. You might alwo want to require the Start field in the table - do not let it be blank. You will get an error message when trying to add a record without a start date, and then you can Ctrl-Break to find the code and fix it. It could be in more than one place.

Daryl,

Yes, That seems to be the problem is that it isn't required in the table. I'll make sure it isn't soemthing else, but with it set to required, it doesn't take a blank record. I'll also check it out with the ctrl + break to see if it's anything else. Maybe I can use an error handle with this.

Thanks for all your help - Great Work! I'm impressed.

Thank you so much!

Kelly

Daryl, if you want I can post it as a new topic. You have help me out greatly. I still have one last problem and that is with the query that is running for the total. I tried your example even as a new query and I can get this to filter by month. I have the example attached.

Please let me know if you don't have time, I'd understand.

Here is my current query;

SELECT tblTempTime.FacilitatorID, tblEmployee.FirstName, tblEmployee.MiddleInitial, tblEmployee.LastName, Round(Sum(DateDiff("n",[StartTime],[EndTime])/60),2) AS TotalHrs
FROM tblFacilitators INNER JOIN (tblEmployee INNER JOIN tblTempTime ON tblEmployee.EmpID = tblTempTime.EmpID) ON tblFacilitators.FacilitatorID = tblTempTime.FacilitatorID
GROUP BY tblTempTime.FacilitatorID, tblEmployee.FirstName, tblEmployee.MiddleInitial, tblEmployee.LastName, tblTempTime.StatusID
HAVING (((tblTempTime.FacilitatorID)=[Forms]![frmMain]![FacilitatorID]) AND ((tblTempTime.StatusID)=1));

I'm thinking since I already have an expression in it, it may be too much? Here is the expression;
TotalHrs: Round(Sum(DateDiff("n",[StartTime],[EndTime])/60),2)

I'm not sure if I can incorporate the information to the query statement above with what you gave me - "Year([YourDateField]) = Year(Date()) AND Month([YourDateField]) = Month(Date())"

The Row Source for the List Box TotalHrs contains this - SELECT [qryEmpTotalTime].[FirstName], [qryEmpTotalTime].[MiddleInitial], [qryEmpTotalTime].[LastName], qryEmpTotalTime.[TotalHrs] FROM qryEmpTotalTime ORDER BY [FirstName];

Kelly -

Did you try adding the criteria? That should not be a problem.

If you don't need to show the Facilitator's name, then you can remove the Facilitators table, which will simplify the query.

If you run into a problem with the query being too complex, I would remove the Round function on the Sum. Instead, I would format the TotalHrs column to display the number as fixed, 2 decimal places.

Let us know!

Hi Daryl,

I wanted to let you know that the query criterea ([YourDateField]) = Year(Date()) AND Month([YourDateField]) = Month(Date()) does work, but in my instance becuase I'm using the SUM in the query, it for some reason won't SUM and it will listthe same user for all times they clocked in. I'm trying to group each users time.

I tried to modify the code for the Clockin button for the Clockout button but, I couldn't get it to work. Here's the Clockin code. Can you tell me what needs to be changed?


Private Sub cmdClockin_Click()
' Check to see if there is already a clock-in record
Dim rstIncomplete As DAO.Recordset
'Dim intLoop As Integer
Set rstIncomplete = Me.ListIncomplete.Recordset
'intLoop = 0
'Do While intLoop < rstIncomplete.RecordCount
If rstIncomplete!EmpID = Val(Me.EmpID.Column(0)) Then
MsgBox ("You are already clocked in")
ClockedIn = True
End If
'intLoop = intLoop + 1
'LoopIf ClockedIn = False Then
Me.SetStart = Now()
DoCmd.GoToRecord , , acNewRec
'End If

End Sub


Here's what I thought it should be;

Private Sub cmdClockout_Click()
' Check to see if there is already a clock-in record
Dim rstIncomplete As DAO.Recordset
'Dim intLoop As Integer
Set rstIncomplete = Me.ListIncomplete.Recordset
'intLoop = 0
'Do While intLoop < rstIncomplete.RecordCount
If rstIncomplete!EmpID = Val(Me.EmpID.Column(0)) Then
MsgBox ("You need to clock in first")
ClockedIn = False
End If
'intLoop = intLoop + 1
'LoopIf ClockedIn = True Then
Me.SetStart = Now()
DoCmd.GoToRecord , , acNewRec
'End If


Thanks for the help, everything else works great.

Kelly

Kelly -

I thought you had the Clocked_in working with the loop - maybe you started with the wrong code. The Clockedin should be like this:

Private Sub cmdClockin_Click()
' Check to see if there is already a clock-in record
Dim rstIncomplete As DAO.Recordset
Dim intLoop As Integer
Set rstIncomplete = Me.ListIncomplete.Recordset
intLoop = 0
Do While intLoop < rstIncomplete.RecordCount
If rstIncomplete!EmpID = Val(Me.EmpID.Column(0)) Then
MsgBox ("You are already clocked in")
ClockedIn = True
End If
intLoop = intLoop + 1
Loop
If ClockedIn = False Then
Me.SetStart = Now()
DoCmd.GoToRecord , , acNewRec
'End If


The Clocked_Out should now be something like this:

Private Sub cmdClockout_Click()
' Check to see if there is already a clock-in record
Dim rstIncomplete As DAO.Recordset
Dim intLoop As Integer
Set rstIncomplete = Me.ListIncomplete.Recordset
intLoop = 0
Do While intLoop < rstIncomplete.RecordCount
If rstIncomplete!EmpID = Val(Me.EmpID.Column(0)) Then
ClockedIn = True
End If
intLoop = intLoop + 1
Loop
If ClockedIn = False Then
msgbox("You are not clocked in, so you can't clock out")
Else
' Do what you do to update the records...
End If

Hi Daryl,
Oops, Yes I did fix the Clockin button, sorry, I was in a hurry and copied it from this topic page.

Thank you for the Clockout code, I sure do appreciate it.

For the code, below that is commented out, I'm thinking that this I would change to something I would like it to say?


Else
' Do what you do to update the records...
End If


Thank you Daryl, and have a wonderful weekend!

Kelly

Kelly -

This should be where you put the code you originally had to clock out with.

Daryl,

Got it - Thanks!

Daryl,

That worked great. I did need to change both the ClocIn and ClockOut feids to Required, once this was done everything worked perfect.

All I have left to figure out is the query, where Hrs need Summing and by the month.

Maybe a sub query?

Thank you again,

Kelly

Note;
I had to change back the table EndTime back to not Required

Hi Daryl,

I guess I respond to quickly to your help, but here's the problem I'm running into now.

If I add the code to the onClick event for the ClockOut button, this does work in the current session that is running. However, when the page is closed and when you go back in, if the users selects his name to ClockOut, a message appears stating that " you're not clocked in yet. This only happens after the users clock in and close the form and go back in to clock out.

Thanks,

Kelly

Hi Daryl,

I was able to finally get the correct output of the query, by running a query off the origonal query. So I'm good now.

Thanks for all your expert help!

Kelly

Glad I could help earlier. It looks like you figured out the rest yourself - way to go!

Thank you Daryl for all your help!

Surely couldn't have done it without the help of you and UtterAccess. Wonderful Site!