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

 

[TheAceMan1]

How are ya Triacona . . .

Your VBA syntax for detecting [blue]Null[/blue] is wrong, and you don't use a [blue]form reference[/blue] when referencing a control on your form. Example of proper syntax would be:

[blue]If Not IsNull([purple][b]Me[/b][/purple].TimeInMorning) Then[/blue]

[blue]Your Thoughts? ...[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Triacona]

Dear TheAceMan1,

Thanks so very much for a speedy reply.

I have change the code as requested to...

Private Sub TimeInOut_Click()
On Error GoTo Err_TimeInOut_Click

   If Not IsNull(Me.TimeInMorning) Then
        Me.TimeInMorning = Now()
    Else
        If Not IsNull(Me.TimeOutLunch) And Me.TimeOutLunch > Me.TimeInMorning Then
            Me.TimeOutLunch = Now()
        Else
            If Not IsNull(Me.TimeInLunch) And Me.TimeInLunch > Me.TimeOutLunch Then
                Me.TimeInLunch = Now()
            Else
                If Not IsNull(Me.TimeOutEvening) And Me.TimeOutEvening > Me.TimeOutLunch Then
                    Me.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 now gives an error message of...

This Recordset is not updateable

Is this because I am using a select query and not an update query??

Or because I am not using the tables directly?

Thanks again for all your help!



Thank you,

Kind regards

Triacona

 

[PHV]

What are the PrimaryKeys of your tables ?
Have a look here:

http://support.microsoft.com/kb/148581

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

 

[Triacona]

Dear PHV,

Thanks you for your help
EmployeeID is the primary key for both tables



Thank you,

Kind regards

Triacona

 

[PHV]

So, the Times table has a single row per EmployeeID ???

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

 

[Triacona]

Dear PHV,

Thank you

Yes, the Times Table does have a single row per EmployeeID...
Is that wrong?

They will obviously have many different times, over a period of time.
So Date will determine which day these times where entered in...

I can see where you're going...

Please help

Thank you

Thank you,

Kind regards

Triacona

 

[Triacona]

Dear All,

Thank you for all the help

I have now rethought my approach...

I have 3 tables now...

Employee
Date
Times

1 Employee has Many Dates which have Many Times.

Employee Table fields:

EmployeeID	
FirstName	
LastName	
TelephoneNumber

Data as such:

EmployeeID	FirstName	LastName	TelephoneNumber
4207		Matthew		Johnson		3455
4308		Jannet		Goldsworth	3488
4388		Jannet		Gould		3488

Dates Table fields

Date			
EmployeeID

Data as Such:

Date			EmployeeID
22/03/2011 11:14:49	4207
22/03/2011 11:14:52	4308
22/03/2011 11:15:04	4388

Times Table fields

EmployeeID	
TimeInMorning	
TimeOutLunch	
TimeInLunch	
TimeOutEvening

Data as such:

EmployeeID	TimeInMorning	TimeOutLunch	TimeInLunch	TimeOutEvening
4207		10:02:00		11:00:00		12:00:00		17:56:00
4308		10:50:00		11:50:00		12:50:00		18:16:00
4388		10:00:00		11:00:00		12:00:00		17:58:00

The Relationships are as follows.
1 Employee has Many Dates, and Dates has Many Times
Employee1-<Dates-<Times
EmployeeID is the Primary key for all tables

I have created a form with subform using the table wizard.
I pulled in all fields from all tables.

I have put the TimeInOut button on the subform Dates Subform
The records are now updateable, but now you have to select the particular record (row), and then click the button.

It only seems to update the record selected, and only the TimeInMorning??

Code behind button TimeInOut

Private Sub TimeInOut_Click()


[blue] If [/blue]IsNull(Me.Date) [blue] Then [/blue]
	Date.SetFocus [green] 'in the hope it will focus on the next blank date [/green]
	Me.Date = Now() [green] 'Entering that days date [/green]
[blue]		If [/blue]IsNull(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]		Else [/blue]

[blue]		If [/blue]IsNull(Me.TimeOutLunch) [blue]And [/blue]Me.TimeOutLunch > Me.TimeInMorning [blue]Then [/blue] [green] 'checks if TimeOutLunch IsNull and TimeOutLunch is greater than TimeInMorning [/green]			

		Me.TimeOutLunch = Now() [green] 'If above true then TimeOutLunch = Current time [/green]
	[blue]	Else [/blue]

	[blue]	If [/blue]IsNull(Me.TimeInLunch) [blue] And [/blue] Me.TimeInLunch > Me.TimeOutLunch [blue] Then [/blue] [green] 'checks if TimeInLunch IsNull and TimeInLunch is greater than TimeOutLunch [/green]
			Me.TimeInLunch = Now() [green] 'If above true then TimeInLunch = Current time [/green]
	[blue]	Else [/blue]

	[blue]	If [/blue] IsNull(Me.TimeOutEvening) [blue] And [/blue] Me.TimeOutEvening > Me.TimeInLunch [blue] Then [/blue] [green] 'checks if TimeOutEvening IsNull and TimeOutEvenign is greater than TimeInLunch[/green]
			Me.TimeOutEvening = Now() 'If above true then TimeOutEvening = Current time
[blue] End If
		End If
		End If
		End If
		End If [/blue]

Exit_TimeInOut_Click:
    Exit Sub

Err_TimeInOut_Click:
    MsgBox Err.Description
    Resume Exit_TimeInOut_Click
    
End Sub

When the button is clicked it enters a copy of the Previous record (cached record?)???

Even if I delete all Time and Date records from the employee, exit access and then open it again, it inserts 10:00:00 11:00:00 12:00:00 17:58:00?
The above is attached to EmployeeID 4388, it looks like it enters in what ever was before...this is clearly not what I was going for.....


Please help, I am at a loss
Thank you
Thanks for your help in advance!







Thank you,

Kind regards

Triacona

 

[PHV]

Some discrepancy between
1 Employee has Many Dates, and Dates has Many Times
and
EmployeeID is the Primary key for all tables

Have a look here:

http://r937.com/relational.html

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

 

[gcDataTechnology]

best regards, Triacona

I know you've moved onto 3 tables now, but the error "Quote (Error Message): This Recordset is not updateable" is usually the query property setting. Check your query TimeRecording by opening in design mode, right click, open properties look at "Recordset Type" & set to Dynaset (inconsitant updates).

That has allowed me to update query data in the past.

Hope you get it working

Learnin', Growin' and Failing Forward

 

[Triacona]

Dear gcDataTechnology,

Thank you for your reply!

I have scrapped the query and just used the form wizard to pull through all values from all tables, including the ability to update them.

The data is updating fine now

It is just what the button TimeInOut is doing that is not correct.

Dear PHV,

Thank you for your reply![tumbsup]!

What do you mean by discrepancy?

The data seems all to be linked, and showing within all the tables.

The relationships are Employee1-<Dates1-<Times
Table Employee has 1 to many relationship with Table Dates.
Table Dates has 1 to many relationship with Table Times.

Data from table Employee

EmployeeID	FirstName	LastName	TelephoneNumber
4207		Jonathan	Sevral			3455
4308		Jannet		Goldsworth		3488
4388		Levian		Gould			3488

As below there are Plus symbols that expand, showing the other relational data...

Plus symbol
(Link to other
Tables)	EmployeeID	FirstName	LastName	TelephoneNumber
+	4207		Etienne		Pienaar			3455
+	4308		Jannet		Goldsworth		3488
+	4388		Levian		Gould			3488

So they all Link via EmployeeID, so that per date each employee has times.

That seems to be working, i.e. each day(date) has times allocated to it...although that date can also have many alotment of times, so e.g. 23/03/2011 can have many rows of times.

If I should not use the EmployeeID as my primary key, and link for all tables, what do you think I should use?


Am I doing this right?
This is my first real attempt at creating tables

On the other hand the data seems to be linking, and working...

It is just what the button TimeInOut is doing that is not correct.
It is still doing as I have described in a previous post.

When the button is clicked it enters a copy of the Previous record (cached record?)???Even if I delete all Time and Date records from the employee, exit access and then open it again, it inserts 10:00:00 11:00:00 12:00:00 17:58:00?

How to I get each individual field to update when the button is clicked, I.e. person comes in morning clicks TimeInOut button;
code then checks if TimeInMorning is null then if null inserts current time into field TimeInMorning, whereas if TimeInMorning is not null then it moves on to the next field, TimeOutLunch and fills that in; etc. until the row is full, then creates a new row and starts again?
Should I use a loop rather??

Thanks for your replies, they are greatly appreciated


Thank you,

Kind regards

Triacona

 

[Triacona]

Dear All,

Another thing I double checked the relationships, and Access states that the relationship type between...
Dates and Times is Indeterminate ????

How do I fix this??

Thanks for all the help!

Thank you,

Kind regards

Triacona

 

[Triacona]

Dear All,

I have now changed the realtionship to One To Many, and when in the table Employee, the plus symbol (+) that opens the other table data is there and I open + it then goes to Dates table which has a +, I click to open that and it opens the Times table, showing Times table data, but then has an additional + and shows dates, and then another + which shows Times etc....


Please help, very confused at the moment...

Thank you

Thank you,

Kind regards

Triacona

 

[gcDataTechnology]

Triacona, Happy Learning. This is a good exercise for you to start understanding relational data. I KNOW the frustration. You'll get good advice at tek-tips!!

In response to "Please help, very confused at the moment..."

Let's start with your tables. [Employees], [Dates], [Times]. It is good practice to name them with a prefix convention that identifies what they are. tbl is typcial for a table, qry for a query, frm for a form. this will make it less confusing to program later. tblEmployees, tblDates, tblTimes.

I believe this is what you've done already. In the Employees table set the EmployeeID field to be the primary key. In Relationships Linking on the field EmployeeID, create a 1->many between [Employees] -> [Dates] then create another 1->many between [Dates] -> [Times]. Be sure to link on EmployeeID for all relationships.

In the Employees table the + sign now allows you to look at each employee record in the Dates table. From there the + sign allows you to see the records in the Time table.

If that is what happens, it seems to be the correct relationship. I'm still looking at the code to see what is happening, but please confirm this is the relationships you have.

Learnin', Growin' and Failing Forward

 

[gcDataTechnology]

Triacona, My Appologies!!! You need to have EmployeeID as the primary key in the table [Employees] linking to the field EmployeeID in the table [Dates] BUT it cannot be a primary key in both tables. You need another unique identifier in the table [Dates] to link to the table [Times]. Again I'm sorry for the error. EXAMPLE : You have as a primary key EmployeeID linking to EmployeeID you could use as a primary key DateID to link to DateID



Learnin', Growin' and Failing Forward

 

[Triacona]

Dear gcDataTechnology,

Thank you so much for your help!

It is really great!

I will test your solution and get back to you.

Thanks again!

Thank you,

Kind regards

Triacona

 

[Triacona]

Dear gcDataTechnology,

I have now modified my tables.
A few points I have realised...
1. One date needs one set of times.
So in response I linked the Date field in Dates to the Date Field in Times and they are both Primary keys.

I have removed DateID as this created a linking problem between Dates and Times.

Basically this records the Clock in/out times of employees.

So the days date can only contain one set of times.

So on this basis, I created the following relationships:

Employees1-<Dates1-1Times
Employees One To Many Dates One To One Times
So One Employee has Many Dates and Each Date has One Set of Times.


The tables fields are as follows
Employee

[b]EmployeeID [/b]
FirstName    
LastName    
TelephoneNumber

Date

[b]Date            [/b]
EmployeeID

Times

[b]Date[/b]
TimeInMorning    
TimeOutLunch    
TimeInLunch    
TimeOutEvening

So the linking is working now, I hope, well is from a testing point of view.

I have a form with a subform.
Employee
Dates Subform

The Form (Employee) contains Employee Table Data
The Subform (Dates Subform) contains the Dates and Times Table data.

I have a TimeInOut button on Dates Subform.

This is meant to automate entering Date and Times.

Here is the code.

Private Sub TimeInOut_Click()
On Error GoTo Err_TimeInOut_Click

DoCmd.GoToRecord , , acLast ' goes to last entry
    
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
    
ElseIf IsNull(Me.Dates_Date) Then 'checks if Date field IsNull
    Me.Dates_Date = Now() 'if IsNull then Date gets filled with Current date
    
ElseIf IsNull(Me.TimeInMorning) Then 'checks if TimeInMorning IsNull
    Me.TimeInMorning = Now() 'if IsNull then it changes the time to current time
    
ElseIf 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
    
ElseIf IsNull(Me.TimeInLunch) And Me.TimeInLunch > Me.TimeOutLunch Then 'checks if TimeInLunch IsNull and TimeInLunch is greater than TimeOutLunch
    Me.TimeInLunch = Now() 'If above true then TimeInLunch = Current time
    
ElseIf IsNull(Me.TimeOutEvening) And Me.TimeOutEvening > Me.TimeInLunch Then 'checks if TimeOutEvening IsNull and TimeOutEvenign is greater than TimeInLunch
    Me.TimeOutEvening = Now() 'If above true then TimeOutEvening = Current time
    

End If

Exit_TimeInOut_Click:
    Exit Sub

Err_TimeInOut_Click:
    MsgBox Err.Description
    Resume Exit_TimeInOut_Click
    
End Sub

The button is not working properly.

It is meant to GoTo the last record,
and then check if any of that records in the fields for Dates and Times are not null
If they are not null then it will create a new Record
If they are null then it will fill in sequentially the fields starting with Date,
then TimeInMorning
then TimeOutLunch
then TimeInLunch
then TimeOutEvening


Well that is what it is supposed to do...
What it does now is if the record is blank fills in the Date [green] (correct)[/green]
Then clicked a second time fills in TimeInMorning [green](correct)[/green]
Then if I click it again nothing!
If I manually fill in the rest of the fields, and then click the button,
what it is supposed to do is create a new record and then fill in the Date TimeInMorning etc....but nothing!


Please help...






Thank you,

Kind regards

Triacona

 

[gcDataTechnology]

Triacona, At this time, how do you select the employee on the employee form? Do you use the built in record selectors at the bottom of the form window or have you built your own buttons to navigate to an employee's data?

I'm building the table structure and forms as you have described them. Once that is done I'll look into the code more closely to see what happens.

Have you looked at other forums or web search's to see if anyone else has run into similar issues? I find the more code samples I can look at, the more I learn better techniques.

Learnin', Growin' and Failing Forward

 

[gcDataTechnology]

Triacona, what do you use as a record source for your dates subform? Is it one form or does it have a subform for the Times table? From your code it looks like your subform's record source would be from a query pulling data from both the Date and Times tables.

Learnin', Growin' and Failing Forward

 

[Triacona]

Dear gcDataTechnology,

Thank you so much for your replies and patience

I have form (Employees) with 1 subform (Dates Subform).

I used the form wizard and selected my tables (Employees, Dates and Times) to display, update/delete data.

The wizard then created Employees and Dates Subforms and the Data was appearing correctly and I could amend data.

Yes the subform does pull data from both dates and times tables.

I would also open the table and check if the data was saving there and it was...

The TimeInOut button is within the Dates Subform

I don't have a query, as I could not work out how to update the data to the tables.

The subform says its data(source object) is: Dates Subform.
With Link Child fields as: EmployeeID
With Link Master fields as: EmployeeID
Enabled: Yes
Locked: no

Do I need to create a query that the forms come from (and that query must then be able to edit/delete fields?

Thanks for all your help and forthcoming help


Thank you,

Kind regards

Triacona