Time Recording for employees 1

[Triacona]

Dear All,

I have a problem with my attempt to create a time recording program.

I have 2 tables:
Employees
Times

Fields within Employees:

EmployeeID - Number
FirstName - Text
LastName - Text
TelephoneNumber - Number

Fields within Times:

EmployeeID - Number
TimeInMorning - Date/Time
TimeOutLunch - Date/Time
TimeInLunch - Date/Time
TimeOutEvening - Date/Time
Date - Date/Time {Has default value of NOW()}
TimeDoneMorning - Date/Time
Lunch - Date/Time
TimeDoneEvening - Date/Time

I have then created a query from the both tables above called:
TimeRecording
It has all the above fields in it.
Within the TimeRecording Query the Tables are linked as follows:
Employee is linked to Times via Employee ID, as a Number 2 join(Include ALL records from Employees and only those from Times where joined fields are equal)

I have now created a form TimeRecording
It is linked to the query TimeRecording
I put in some test data and it displays.

I have created a find button to find the relevant employee.

I have now also created a Time In/Out button
It has the following code behind it.

Private Sub TimeInOut_Click()
On Error GoTo Err_TimeInOut_Click

   If TimeInMorning Is Not Null Then
        TimeInMorning = Now()
    Else
        If TimeOutLunch Is Not Null And TimeOutLunch > TimeInMorning Then
            TimeOutLunch = Now()
        Else
            If TimeInLunch Is Not Null And TimeInLunch > TimeOutLunch Then
                TimeInLunch = Now()
            Else
                If TimeOutEvening Is Not Null And TimeOutEvening > TimeOutLunch Then
                    TimeOutEvening = Now()
                End If
            End If
        End If
    End If

Exit_TimeInOut_Click:
    Exit Sub

Err_TimeInOut_Click:
    MsgBox Err.Description
    Resume Exit_TimeInOut_Click
    
End Sub

It gives me the following error message.

Object required

Any help would be greatly appreciated!

The form TimeRecording has the following text boxes:

EmployeeID
FirstName
LastName
TelephoneNumber
TimeInMorning
TimeOutLunch
TimeInLunch
TimeOutEvening
Date

I want the button to write into the text boxes within the form and save them.
Will this also then save it in the table??

I am quite new to this, is my approach to this problem right?

Again any help will be massively appreciated!


Thank you,

Kind regards

Triacona

 

[gcDataTechnology]

Triacona, I wanted to write my code as close to yours as possible, but I'm having some trouble with that. I did the auto forms like you did.



Issue: I usually make my own forms and link them together. I am unable to utilize "me." to reference the time fields nested in the subform. I ran The auto form builder not the wizard so I will continue to try again. When I ran the form wizard it gave me 1 form with 2 subforms and I'll start from there.

I'm happy to help, but at this point I'm learning as I go because I'm trying to duplicate what you have. I can create the forms the way I normally do then show you how the code looks. That may be quicker.

Hope I help


Learnin', Growin' and Failing Forward

 

[gcDataTechnology]

Triacona, I have a solution for you. Looking at your code to punch out for lunch this code cannot run because it looks to compare a date that is null

'your code from above
If IsNull(Me.TimeOutLunch) And Me.TimeOutLunch > Me.TimeInMorning Then 'checks if TimeOutLunch IsNull and TimeOutLunch is greater than TimeInMorning
Me.TimeOutLunch = Now() 'If above true then TimeOutLunch = Current time

Try instead
If IsNull(me.TimeOutLunch) Then
Me.TimeOutLunch = Now()
'Add an error handler below
If Me.TimeOutLunch > Me.TimeInMorning Then
'all is well

What do you want the program to do if the time is incorrect? You need to determine what would cause that to happen and how to handle this situation. This is the same situation for punching TimeInLunch and TimeOutEvening.

For the new record add Me.Dates_Date = Now() after creating the new record.

If Not IsNull(Me.Dates_Date) And Not IsNull(Me.TimeInMorning) And Not IsNull(Me.TimeOutLunch) And Not IsNull(Me.TimeInLunch) And Not IsNull(Me.TimeOutEvening) Then
'Tests if all the fields in the last Record are not null DoCmd.GoToRecord , , acNewRec 'if record all the above is Not IsNull, creates new record
me.Dates_Date = Now()

For your date field why do you use a long date (3/28/2011 7:00:34) instead of a short date (3/28/2011)? It is the difference of Date() vs. Now(), but is there a need or a special purpose?

Using the long date allows you to keep it a primary key in your Date table right now. If you have people punching in from 2 computers there is the possibility to duplicate values causing an error and unsaved records. If you only have 1 computer for people to punch in, it should not be a problem. A better practice is to add an auto number field to your Date table that links to your time table.

Lastly to get you thinking, my solution works, however it does not take into account entry error or deleted times. See what you can do to make this error proof.

Happy Learning! Hope I helped. This was fun to look at!

Learnin', Growin' and Failing Forward

 

[Triacona]

Dear gcData,

Thank you again for all the help you have provided
Thank you for all the hard work


You used an autonumber to link Date to Times...

What was the Dates Autonumber field called?

And did the Times table also have an autonumber, if not which field did you link/create to link, the Dates auto number.

When I linked 2 autonumbers the data would not link.

This would also create 2 subforms instead of one and a one to many relationship.


So that is why I linked Date (in Date) to Date (in Times).

This works well.

I have solved my Time entry problem by the following code:

Private Sub TimeInOut_Click()
On Error GoTo Err_TimeInOut_Click

DoCmd.GoToRecord , , acLast ' goes to last entry
    
[blue]If Not IsNull[/blue](Me.Dates_Date)[blue] And Not IsNull[/blue](Me.TimeInMorning)[blue] And Not IsNull[/blue](Me.TimeOutLunch) [blue]And Not IsNull[/blue](Me.TimeInLunch) [blue]And Not IsNull[/blue](Me.TimeOutEvening)[blue] Then [/blue][green]'Tests if all the fields in the last Record are not null[/green]

    DoCmd.GoToRecord , , acNewRec [green]'if record all the above is Not IsNull, creates new record[/green]
    
[blue]ElseIf IsNull[/blue](Me.Dates_Date) [blue]Then [/blue][green]'checks if Date field IsNull[/green]

    Me.Dates_Date = Now() [green]'if IsNull then Date gets filled with Current date[/green]
    
[blue]ElseIf IsNull[/blue](Me.TimeInMorning) [blue]Then[/blue] [green]'checks if TimeInMorning IsNull[/green]

    Me.TimeInMorning = Now() [green]'if IsNull then it changes the time to current time[/green]
    
[blue]ElseIf IsNull[/blue](Me.TimeOutLunch) [blue]Then[/blue] [green]'checks if TimeOutLunch IsNull[/green]
    
    Me.TimeOutLunch = Now() [green]'If above true then TimeOutLunch = Current time[/green]
    
[blue]ElseIf IsNull[/blue](Me.TimeInLunch) [blue]Then[/blue] [green]'checks if TimeInLunch IsNull[/green]
    
    Me.TimeInLunch = Now() [green]'If above true then TimeInLunch = Current time[/green]
    
[blue]ElseIf IsNull[/blue](Me.TimeOutEvening) [blue]Then[/blue] [green]'checks if TimeOutEvening IsNull[/green]
    
    Me.TimeOutEvening = Now() [green]'If above true then TimeOutEvening = Current time[/green]
    

[blue]End If[/blue]



Exit_TimeInOut_Click:
    Exit Sub

Err_TimeInOut_Click:
    MsgBox Err.Description
    Resume Exit_TimeInOut_Click
    
End Sub

Is there a better way than =Now()?
You've mentioned =Date(), does this enter current date and time?

So the above code executes each time I press the button, it enters Date as current date and time (now())
Then after next click it enters TimeInMorning as NOW().
Then after next click it enters TimeOutLunch as NOW().
Then after next click it enters TimeInLunch as NOW().
Then after next click it enters TimeOutEvening as NOW().

Then after next click it focuses on the next record, but does nothing, it is supposed to create a new record, and then do the above again.

Etc.
So each user only has to click the button to enter in their times and dates.




Thanks for all your help and forthcoming help!





Thank you,

Kind regards

Triacona

 

[gcDataTechnology]

Triacona, Glad to hear your program enters dates correctly!! Creating the next record only added a record to the current employee. It does not advance to the next employee record. It sound like you want to use the TimeInOut button to do this.

I would encourage you to use an employee selection of some kind on the Employee Form...built in navigation buttons that show up on the bottom of the form or a programmed combobox to select a specific employee.

Happy Learning.

Learnin', Growin' and Failing Forward

 

[PHV]

Anyway, I don't see why you've a Date and a Times tables.
What about this schema ?

[u]Employee[/u]
EmployeeID  PK
FirstName
LastName
TelephoneNumber

[u]DatePunch[/u]
EmployeeID  FK
Date  (default Date())
TimeInMorning    
TimeOutLunch    
TimeInLunch    
TimeOutEvening

PK=(EmployeeID,Date)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Triacona]

Dear all,

Thank you for your help!

I have solved it now, it was a problem with the first line of code.

DoCmd.GoToRecord , , acLast ' goes to last entry
This prevented the If statement tests.
Removing this command makes it work! YAY!

Why don't you use this schema?

@PHV Thanks for your help.

I have created a separate database and used your schema, and added my button with the code as above and it comes up with the
Works pretty much the same
Thanks for your input though, because it is valued


@gcDataTechnology

Creating the next record only added a record to the current employee. It does not advance to the next employee record. It sound like you want to use the TimeInOut button to do this.

Um not exactly, what I want is on the same employee record to record another Date and Times In or Out.



Thank you,

Kind regards

Triacona

 

[PHV]

Works pretty much the same
But simpler, don't you think ?

 

[Triacona]

Dear All,

I have a further problem...

I have a field in my Subform DatesSubform called txtRollingTime

It is calculated from the field:
txtExtraTime
txtExtraTime
Is created from fields:
txtTimeDoneMorning = [TimeOutLunch]-[TimeInMorning]
txtTimeDoneEvening = [TimeOutEvening]-[TimeInLunch]
txtTotalTimeDone = [txtTimeDoneMorning]+[txtTimeDoneEvening]
txtExtraTime =[txtTotalTimeDone]-[txtTimeRequired]

txtRollingTime =[txtExtraTime]+[txtExtraTime]


I want txtRollingTime to all extra times added together on each consecutive Record.

1:00
2:00
-0:05

etc.

Please help

Thank you




Thank you,

Kind regards

Triacona

 

[Triacona]

Dear All,

further development:
RollingTime =IIf([RollingTime] Is Null,([txtTotalTimeDone]-[txtTimeRequired]),([txtExtraTime]+[txtExtraTime]))

This is the rolling time (extra time adding onto eachother)

Any news on the negative times??

Thanks for all the help

Thank you,

Kind regards

Triacona

 

[Triacona]

Dear All,

Have now put RollingTime = ([txtExtraTime]+([txtExtraTime])) On the form footer.

RollingTime is a table value added in Times (table)

Seems to be adding it up but for each record in a weird way.

I.e. if I click on the first record in the SubForm - DatesSubform

It shows one total in RollingTime (0.05)
Then I click in the next record it shows (0.10)
(0.10 is correct, but then the problem ensues with the next record)(and Why only when I click in the specified record does it add up)
Then if I click the next record (which has a negative extra time of -0.10) it displayes -0.20 INSTEAD of 0.00???

Please can somebody help
I am banging against a brick wall



Thank you,

Kind regards

Triacona

 

[gcDataTechnology]

Triacona, you can check out the faq's faq702-5248 is how to calculate a running total. Other than this I am unable to help. I have little experience with this currently.

I will be interested to see your solution. It may be better if you post this question in a seperate post.

Have fun learning!!

Learnin', Growin' and Failing Forward

 

[cwhite23]

Good morning.

I have a problem with a subform locking up the database on a multi-user application. The details for the data is as follows:

Header Table
Employee ID (Primary)
Date

Subform Table
Employee ID (Linked to Primary above)
Date (Linked above)
Time_In
Time_Out
Activity
Notes

The form that I designed has a header with fields for the header details to be entered by the users; and a subform for them to enter the times, activities, etc. The parent/child relationships are on the Employee ID and the Date.

The funny thing is that the subform will lock up and give the user an error saying that the data can't be updated, but the header will still accept data.

Has anyone encountered anything like this before? I've tried changing the settings in Access that control how often the data is polled without success, and have removed record locking as well. Anybody have any ideas?

 

[MichaelRed]

just curious, why the multiple times?

seems, to me, that a single date/time stamp for each occurance provides ample information

as one must (obviously?) come in before going out, the first time stamp per enmployee (mayhap even per date?) must be in. logically, once in, employee cannot come in again until 'he' has gone out, so these tow successive times become the (interval) 'he' was in

a simple datediff between these successive pairs is the total time "in" over any period?


of course some care might be taken to observe exceptions (failure to check in / out properly) but these may not be unnsecssaruly more difficult than with the more elaborate multiple times per record.

as a partial simplification toward this, at least remove the concept of "lunch" as a specific pair of times to log in/out

who really cares about WHY employee is off the clock? it could be a personal issue, or a suden onset of health or ...



MichaelRed