new record once new employee added

[Aidanb]

Hi all,

I am wondering if it is possible and how it would be done that if a new employee is entered in to a database table that it will add a new record in another table so we can enter in the hours that they do each week the date will act as the unique identifier through out the database as this will be a weekly updated item.
This date will be selected on the main screen and then we just click a button to move to the next form such as takings or employee hours.

any help on this matter would be greatly appriciated just hope i have put in the correct part of the forum.

Regards

Aidanb

 

[Pat Hartman]

It is not correct to do this. The addition of an employee is independent of time entry and should not be dependent on each other. The act of entering the weekly hours should create the record in the time table. Use a left join when joining these two tables so you will get all employees regardless of whether or not they have hours entered.

 

[Aidanb]

so how would this be represented in a form if i have say 10 staff and and i have a new employee start would i have to update the form or would there be some code that would check for current employees and then alter the form to suit this?.

Regards

Aidan

 

[Pat Hartman]

I would use an unbound main form where I enter the week start date. The subform would be used for data entry. I would probably put a button on the main form that will create the time records for the week so I didn't miss anyone. Then scroll through the records in the subform entering the hours for each person.

If the people filled out timesheets, I would create timesheets in Excel that I can import rather than retype. If you have a mix of office and plant or outside personal so that some have the ability to use Excel and some don't, I would add an indicator to the employee record so I could identify which group a person belonged to so that the create button would only create records for the employees that could not submit spreadsheets.

 

[Aidanb]

Hi Pat,

Thanks for your help on this matter this is for a kitchen so all the staffs hours would be filled in by one person this would then be collated and sent to my self to keep a trak of the hours.

how would this button collate the information from staff table would you have to wirte it through vba or would macro do the job just as well if you can give me some key words to search for i would be very greatful.

Regards

Aidan

 

[Pat Hartman]

Create a select query that selects the employees who will be expected to enter hours. Change the query type to append to append to the hours table. take the EmployeeID from the employee table and append it to the EmployeeID in the hours table. then for the week start date (or end date, whichever you use), use an argument:
[EnterStartDate]

Save this query and run it from a button on the form using the .execute method since you will need to supply a parameter. After running the append query, requery the subform.

 

[gemma-the-husky]

out of interest, when you say you have another form with hours worked, do you have this set up as

a) a single record for each employee, with a slot for every day or week to be entered or
b) multiple daily or weekly records for each employee, each record storing one entry only.

it ought to be b) , but it sounds like you may have it set up as a)

 

[Aidanb]

what i have is a form with multipul records on it and this is then linked back to an employee table through the employeeID the weekstartdate links back to the takings table.

what i want is to have all the employees names put in a table then this will populate the employeeID in the labour table so i can enter in the start and finish time for each day.

 

[gemma-the-husky]

I would have thought that you dont need to prepopulate the employee hours table ahead of time (ie with null values for hours worked etc), in the way you descdribe on your original post

you just need a routine that inserts new records for a given week for employees that are still employed - A simple append query will do that for all your employees