Add 7 records

[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

 

[Allen Browne]

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

 

[DebbieG]

Allen,

Thanks so much for your response. I couldn't get your code to work but I
got the following to work. It works fine but it you see a glaring error,
please let me know.

Thanks,
Debbie

Private Sub Employee_combo_AfterUpdate()
Dim x As Variant
x = DLookup("[EmpNo] & [WorkDate]", "tblHoursWorked", _
"[EmpNo] = '" & Me.Employee_combo.Column(0) & "'" & _
"and [WorkDate] between #" & Me.txtBeginDate & "# and #" &
Me.txtEndDate & "#")
If IsNull(x) Then
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim dt As Date
Dim lngCount As Long
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("tblHoursWorked", dbOpenDynaset)
For dt = Me.txtBeginDate To Me.txtBeginDate + 6
With rs
.AddNew
!EmpNo = Me.Employee_combo.Column(0)
!WorkDate = dt
.Update
End With
lngCount = lngCount + db.RecordsAffected
Next
rs.Close
Set rs = Nothing
Set db = Nothing
End If

Me.Requery
End Sub


| 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
|
| --
| Allen Browne - Microsoft MVP. Perth, Western Australia.
|
| Reply to group, rather than allenbrowne at mvps dot org.
|
| | >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
|
|