Add 7 records

  • Thread starter Thread starter DebbieG
  • Start date Start date
D

DebbieG

I am in the process of creating a database that will track employees hours
worked.

One of my tables (HoursWorked) is:
Emp. No.
WorkDate
Regular Hours
Overtime Hours
Vacation
Sick
Holiday
etc.

They will enter this information a week at a time. I'm picturing a form
appearing asking which week they want to work on (i.e., 12/20 - 12/26).
Once they decide which week, I'm picturing a form appearing where they can
choose which employee. I'm OK with all of this.

Here's my question. Once they choose an employee, I want to see 7 records -
one for each day of the week they chose for/from the HoursWorked table. If
an employee does not have any records for that week in the HoursWorked
table, how can I automatically add a record for each date for that employee?
I don't want the user to have to manually enter the dates. If they do have
records for each date of that week I just want them to appear. How can I
accomplish this? The form would look similar to this:

Hours Worked for week beginning 12/20/2004

Emp. No. 123 John Doe

WorkDate Reg Hrs O/T Hrs Vacation Sick Holiday etc.
12/20/2004 4.00 4.00
12/21/2004 8.00 2.00
12/22/2004 8.00
12/23/2004 8.00
12/24/2004 8.00
12/25/2004
8.00
12/25/2004
12/26/2004

Thanks in advance for any help/suggestions,
Debbie
 
Presumably you have an Employee table, and you want to programmatically
create a record in the HoursWorked table for each day of the week for each
employee.

Paste the function below into a module.
You could then set up a form with a text box named (say) txtStartDate, and a
command button that has this in its On Click property:
=HoursForWeekStarting([txtStartDate])

Function HoursForWeekStarting(StartDate As Date) As Long
Dim db As DAO.Database
Dim dt As Date
Dim strSql As String
Dim lngCount As Long

Set db = dbEngine(0)(0)

For dt = StartDate to StartDate + 6
strSql = "INSERT INTO HoursWorkEd ( WorkDate, EmployeeID ) SELECT "
& Format(dt, "\#mm\/dd\/yyyy\#") & " AS WorkDate, Employee.EmployeeID FROM
Employee;"
db.Execute strSql, dbFailOnError
lngCount = lngCount + db.RecordsAffected
Next

Set db = Nothing
HoursForWeekStarting = lngCount
End Function
 
Back
Top