Using Form to update multiple records?

[Guest]

I'm creating an employee DB in Access 2007 and need to be able to track time
submissions. I would like to be able to enter hours for each day (M-F), and
the hours need to be categorized (vacation time vs. billable time). Ideally I
would also like to generate a report from this information that will tell me
the total hours worked in a given week (as well as a breakdown of how many
hours were vacation, billable, etc). Initially, I tried creating a table that
has this information, but I wasn't able to include a separate field for the
day of the week, without having to manually enter it each time (and it is
cumbersome having to enter a new record for each day of the week). Is there a
way to use an Access Form to enter this information for multiple days
(therefore creating multiple records when info is submitted)? I couldn't
figure it out in Access, but I found an InfoPath form template that has all
of the fields I would need (and then some). The template is here:
http://office.microsoft.com/en-us/t...ID=CT101172771033&av=ZIP000&AxInstalled=1&c=0

My questions:
1. Can this be done just using Access? Or do I need the InfoPath form?
2. If I can just use Access, how on earth do I do this?

If I have to use the InfoPath form:
1. Can I set the form up so that it submits to the DB (just locally, on my
computer)?
2. Do I need to create a table and create all the relationships in my DB
before specifying that data connection, and how do I map the fields in the DB
table to those on the InfoPath form? I have held off creating that table
because I wasn't sure how to make this work.
3. Assuming that the answer to #2 is Yes, how do I determine which fields to
use when creating my table? Can I use whatever works for me?

Thanks in advance for any advice!

 

[Guest]

You could reproduce the InfoPath form easily enough in Access. You could
either base the form on a query on an Employees table sorted by LastName,
FirstName, in which case the controls for the employee data would be bound
ones, or you could use an unbound combo box, cboEmployee, to look up the
employee; this would be bound to a numeric EmployeeID primary key hidden
column in its RowSource, but show the names. The other employee data unbound
controls would be filled by looking up the data from the Employees table once
an employee is selected.

The 'week of' combo box, cboWeek, would be an unbound control. I'll assume
it lists the Sundays for the start of each week, so would draw its list from
a table of Sunday dates (more about how to create this below). If you set
its value to VBA.Date - WeekDay(VBA.Date) + 1 when the form opens it will
show the Sunday of the current week by default.

Each of the 'cells' in the grid of times would be an unbound text box,
txtRegHoursSun, txtRegHoursMon etc.

To insert the rows in the table you'd execute a series of SQL statements in
code via a 'Confirm' button on the form. The table, TimeRecords say, in
question would have columns EmployeeID, WorkDate, HoursWorked, TimeCategory
(the last for 'regular hours', 'overtime etc'. The primary key of this table
should be a composite one of the EmployeeID, WorkDate and TimeCategory
columns. The code to insert the rows would be along these lines:

Dim cmd As ADODB.Command
Dim strSQL As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

If Not IsNull(Me.txtRegHoursSun) Then
strSQL = "INSERT INTO TimeRecords" & _
"(EmployeeID,WorkDate,HoursWorked,TimeCategory " & _
"VALUES(" & Me.cboEmployee & "," & _
"#" & Format(cboWeek,"mm/dd/yyyy") & "#," & _
Me.txtRegHoursSun & ", ""Regular Hours"")"

cmd.CommandText = strSQL
cmd.Execute
End If

If Not IsNull(Me.txtRegHoursMon) Then
strSQL = "INSERT INTO TimeRecords" & _
"(EmployeeID,WorkDate,HoursWorked,TimeCategory " & _
"VALUES(" & Me.cboEmployee & "," & _
"#" & Format(cboWeek,"mm/dd/yyyy") & "# + 1," & _
Me.txtRegHoursMon & ", ""Regular Hours"")"

cmd.CommandText = strSQL
cmd.Execute
End If

'''' and so on to''''

If Not IsNull(Me.txtBereavementSat) Then
strSQL = "INSERT INTO TimeRecords" & _
"(EmployeeID,WorkDate,HoursWorked,TimeCategory " & _
"VALUES(" & Me.cboEmployee & "," & _
"#" & Format(cboWeek,"mm/dd/yyyy") & "# + 6," & _
Me.txtBereavementSat & ", ""Bereavement"")"

cmd.CommandText = strSQL
cmd.Execute
End If

To clear the form's controls ready for the next set of times you could have
a button which loops through them, excluding the cboWeek which you'd probably
want to remain the same by default, and sets each one to Null.

To create a CalendarOfSundays table for use by the cboWeek combo box paste
the following function into a standard module. Note that you'll first need
to create a reference to the ADOX library:

Public Function MakeCalendar(strTable As String, _
dtmStart As Date, _
dtmEnd As Date, _
ParamArray varDays() As Variant)

' Accepts: Name of calendar table to be created: String.
' Start date for calendar: DateTime.
' End date for calendar: DateTime.
' Days of week to be included in calendar
' as value list, e,g 2,3,4,5,6 for Mon-Fri
' (use 0 to include all days of week)

Dim cmd As ADODB.Command
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim strSQL As String
Dim dtmDate As Date
Dim varDay As Variant
Dim lngDayNum As Long

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

Set cat = New Catalog
cat.ActiveConnection = CurrentProject.Connection

' does table exist? If so get user confirmation to delete it
On Error Resume Next
Set tbl = cat(strTable)
If Err = 0 Then
If MsgBox("Replace existing table: " & _
strTable & "?", vbYesNo + vbQuestion, _
"Delete Table?") = vbYes Then
strSQL = "DROP TABLE " & strTable
cmd.CommandText = strSQL
cmd.Execute
Else
Exit Function
End If
End If
On Error GoTo 0

' create new table
strSQL = "CREATE TABLE " & strTable & _
"(calDate DATETIME, " & _
"CONSTRAINT PrimaryKey PRIMARY KEY (calDate))"
cmd.CommandText = strSQL
cmd.Execute

' refresh database window
Application.RefreshDatabaseWindow

' refresh catalog
cat.Tables.Refresh

If varDays(0) = 0 Then
' fill table with all dates
For dtmDate = dtmStart To dtmEnd
lngDayNum = lngDayNum + 1
strSQL = "INSERT INTO " & strTable & "(calDate) " & _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#)"
cmd.CommandText = strSQL
cmd.Execute
Next dtmDate
Else
' fill table with dates of selected days of week only
For dtmDate = dtmStart To dtmEnd
For Each varDay In varDays()
If Weekday(dtmDate) = varDay Then
lngDayNum = lngDayNum + 1
strSQL = "INSERT INTO " & strTable & "(calDate) " & _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#)"
cmd.CommandText = strSQL
cmd.Execute
End If
Next varDay
Next dtmDate
End If

End Function

and call it like so from the Debug window to create a calendar from this
year to the end of 2015 for instance:

MakeCalendar "CalendarOfSundays", #01/01/2007#, #12/31/2015#, 1)

Ken Sheridan
Stafford, England