Data entry from spreadsheet type form 1

[SMHSleepy]

Hello all,

I have tblPayroll with fields Name, WorkDate, Hours.
I'd like to have a form in a spreadsheet type view with WorkDate as column headers and employees on the left as row names. Something like this:

Jan1 Jan2 Jan3 Jan4 Jan5 Jan6 Jan7
John S.
Jane D.
Bob S.

Then the user can enter the hours for each employee on their respective work dates. i.e.:

Jan1 Jan2 Jan3 Jan4 Jan5 Jan6 Jan7
John S. 8 12
Jane D. 5 8
Bob S. 8 8

tblPayroll should then look like this:

John S. Jan1 8
John S. Jan2 12
Jane D. Jan3 5
Jane D. Jan4 8
Bob S. Jan5 8
Bob S. Jan6 8

Sorry if my example columns don't match up on your screen, but you get the idea.

Any suggestions? Thanks.

 

[dhookom]

I would probably work with a temporary table that isn't normalized that could be used for data entry. Then use code and/or queries to update or append to the normalized table.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Same as Duane with some additional info.
I would make a place holder table. I use this table temporarly to hold values and then push them into your normalized table.

tblTempWorkHours
personName
hoursOne
hoursTwo
hoursThree
hoursFour
hoursFive
hoursSix
hoursSeven

Build a continous tabular form bound to this table. Pick a week to edit and open the form. When you open the form it runs an insert query to fill this table with all active persons names. The labels above the dates are determined dynamically. When done editing then you loop the recordset and write an insert query into your other table. Clear all records from the tblTempWOrkHours

Once you get a New week working you have to code loading an existing week. If you open an existing week it has to read your table and populate the tblTempWorkHours. Are you proficient with recordsets? Also with a little code you can make more tabular form function like a spread sheet with up, down, left, right arrows.

 

[SMHSleepy]

Thanks Duane and MajP,

I'll answer your question first by stating, no I'm not proficient with recordsets (or much else for that matter). I'm more of a hacker and pretty good at taking people's existing code and manipulating it for my needs.

I think I understand your suggestions; however, what happens if I need to make a change? Quite often, some employees will decide to switch a shift at the last minute. Will this method still work?

 

[dhookom]

For changes, I would update them in the normalized format. It's only the "batch" entry that typically needs to be made more efficient with a temporary table.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

If i get time I will try to do a demo. i have something very similar but more complex. i will see if I can tailor it to be more relavent.
The difficulty with doing something like this is that it requires several different pieces: table design, form design, and code. None is overly complicated, but a little tricky to see how they all work together.

 

[SMHSleepy]

Thank you. I eagerly await it.

 

[MajP]

Here is the demo.

http://www.4shared.com/document/XmRuBwYD/MajPSpreadSheetV1.html

Like I said no piece is overly complicated, but it requires a lot of parts working together. So this is really hard to explain without seeing it. However, it works well. I use this concept a lot to use a subform as a control. The alternative is to use an Unbound form. That has several advantages but you would have to have a set amount of Names (rows). Then you would have to scroll the names. You can also use a flexgrid control but also requires a lot of coding, but has some nice features.


Big Picture
1. The main form is bound to a table that has only one record and one field. This holds the date that the reporting period starts.
2. The subform is bound to the "grid" table that has a field for names, and fields for day1 - day7 workhours.
3. The labels on the subform are dynamic and based on the date in the main form. Date - Date+6
4. There is an insert query to insert all names into the "grid" table. One to delete all records from this table, and one to set all the hours to null.
5. When you load the main form it sets the labels in the subform. Reads the values in the normal table and inserts these values into the grid.
6. If you change the date or close the form the process is to save the grid values to the normal table, then clear the grid values, and reload the grid based on the new date locations. This makes the form appear as if it is scrollable through the days. When saving the values from the grid it determines if there already is a value for that person and that date. If so it does an update query, else it does an append query.

Here is the main form code.

Private Sub cboStartDate_AfterUpdate()
 'actually this is a text box now.
 If IsDate(Me.cboStartDate) Then
   Me.Requery
 End If
End Sub
Private Sub cmdPrevious_Click()
  On Error GoTo Err_cmdPrevious_Click
    Me.cboStartDate = Me.cboStartDate - 1
    Me.Requery
Exit_cmdPrevious_Click:
    Exit Sub
Err_cmdPrevious_Click:
    MsgBox Err.Description
    Resume Exit_cmdPrevious_Click
End Sub
Private Sub cmdNext_Click()
On Error GoTo Err_cmdNext_Click
    Me.cboStartDate = Me.cboStartDate + 1
    Me.Requery
Exit_cmdNext_Click:
    Exit Sub
Err_cmdNext_Click:
    MsgBox Err.Description
    Resume Exit_cmdNext_Click
End Sub

Private Sub cmdSaveHours_Click()
  SaveHoursWorked
End Sub
Private Sub Form_Close()
  SaveHoursWorked
  clearAll
End Sub

Private Sub Form_Current()
  Me.Dirty = False
  loadSubFrmLabels
  SaveHoursWorked
  clearHours
  LoadGrid (Me.dtmDate)
  Me.subFrmCtlWorkHours.Form.txtBxStartDate = Me.dtmDate
End Sub
Private Sub Form_Load()
  DoCmd.Maximize
  Me.subFrmCtlWorkHours.Form.txtBxStartDate = Me.dtmDate
  Me.Requery
End Sub
Private Sub Form_Open(Cancel As Integer)
 Call loadNames
End Sub
Private Sub ocxCalendar_Click()
  Me.cboStartDate.SetFocus
  Me.Requery
End Sub
Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click
    DoCmd.Close
Exit_cmdClose_Click:
    Exit Sub
Err_cmdClose_Click:
    MsgBox Err.Description
    Resume Exit_cmdClose_Click
End Sub
Public Sub loadSubFrmLabels()
  Dim frm As Access.Form
  Set frm = Me.subFrmCtlWorkHours.Form
  With frm
    .lblDay1.Caption = Me.dtmDate
    .lblDay2.Caption = Me.dtmDate + 1
    .lblDay3.Caption = Me.dtmDate + 2
    .lblDay4.Caption = Me.dtmDate + 3
    .lblDay5.Caption = Me.dtmDate + 4
    .lblDay6.Caption = Me.dtmDate + 5
    .lblDay7.Caption = Me.dtmDate + 6
  End With
End Sub

Public Sub SaveHoursWorked()
 Dim rs As DAO.Recordset
 Dim I As Integer
 Dim personID As Long
 Dim hoursWorked As Single
 Dim fldName As String
 Dim startDate As Date
 Dim dateWorked As Date
 Set rs = Me.subFrmCtlWorkHours.Form.RecordsetClone
 rs.MoveFirst
 startDate = Me.subFrmCtlWorkHours.Form.txtBxStartDate
 Do While Not rs.EOF
   personID = rs!personID_fk
   For I = 1 To 7
     fldName = "day" & I & "hours"
     If Not IsNull(rs.Fields(fldName)) Then
       hoursWorked = rs.Fields(fldName)
       dateWorked = (startDate + I) - 1
       If DateExists(personID, dateWorked) Then
         Call editRecord(personID, dateWorked, hoursWorked)
       Else
         Call addRecord(personID, dateWorked, hoursWorked)
       End If
       'Debug.Print personID & " " & dateWorked & " " & hoursWorked
     End If
   Next I
   rs.MoveNext
 Loop
End Sub

Public Sub loadNames()
  CurrentDb.Execute "qryLoadNames"
  Me.subFrmCtlWorkHours.Form.Requery
End Sub

Public Sub clearAll()
  CurrentDb.Execute "qryClearAll"
End Sub

Public Sub clearHours()
  CurrentDb.Execute "qryClearHours"
End Sub

here is the standard module code

Public Function getSelectedDate() As Date
  getSelectedDate = DLookup("dtmDate", "tblDummyDate")
End Function

Public Function DateExists(personID, dtmDate) As Boolean
  Dim recCount As Integer
  recCount = DCount("*", "tblPersonWorkHours", "personID_fk = " & personID & " AND dtmDate = #" & Format(dtmDate, "mm/dd/yyyy") & "#")
  If recCount > 0 Then DateExists = True
End Function

Public Sub editRecord(personID As Long, dateWorked As Date, hoursWorked As Single)
  Dim strSql As String
  strSql = "UPDATE tblPersonWorkHours SET hoursWorked = " & hoursWorked & " WHERE personID_fk = " & personID & " AND dtmDate = #" & Format([dateWorked], "MM/DD/YYYY") & "#"
  Debug.Print strSql
  CurrentDb.Execute strSql
End Sub

Public Sub addRecord(personID As Long, dateWorked As Date, hoursWorked As Single)
  Dim strSql As String
  strSql = "Insert into tblPersonWorkHours (personID_FK,dtmDate,hoursWorked) values (" & personID & ", #" & Format([dateWorked], "MM/DD/YYYY") & "#, " & hoursWorked & ")"
  Debug.Print strSql
  CurrentDb.Execute strSql
End Sub
Public Sub LoadGrid(startDate As Date)
  Dim rs As DAO.Recordset
  Dim strSql As String
  Dim endDate As Date
  Dim dateWorked As Date
  Dim I As Integer
  Dim updateField As String
  endDate = startDate + 6
  strSql = "Select * from tblPersonWorkHours where dtmDate between #" & startDate & "# AND #" & endDate & "#"
  Set rs = CurrentDb.OpenRecordset(strSql)
  Do While Not rs.EOF
     I = DateDiff("d", startDate, rs!dtmDate)
     updateField = "day" & I + 1 & "Hours"
     strSql = "UPDATE tblTempHours SET " & updateField & " = " & rs!hoursWorked & " WHERE personID_fk = " & rs!personID_fk
     CurrentDb.Execute strSql
    rs.MoveNext
  Loop
End Sub

 

[SMHSleepy]

Thank you so much, this is perfect! I'm just going through and modifying it for my project, but you've done exactly what I needed. Will let you know if I run into any problems. Thanks again!

 

[SMHSleepy]

Hey MajP, I just noticed something on your sample. If I try to delete some of the hours, it doesn't keep the changes. It only allows me to add hours. Is there a way I can make more dynamic? Thanks.

 

[MajP]

It supposed to handle that. I will check the bug.

 

[SMHSleepy]

Thanks MajP,

Since our payroll period is 2 weeks, I made some changes. Having problems with errors now:

1. scroll mouse down gives error 94 (invalid use of null)
2. clicking a date on calendar more than 2 weeks away gives error 3144 (syntax error in UPDATE statement)
3. I enter some hours and close form. When I open form again, the hours are missing unless I choose a different week view.

I realize these are new bugs introduced by my modifications but could you take a look and try to fix? Thanks!

http://www.mediafire.com/?vjy4sd681wto24t

 

[MajP]

I will try to take a look at this tomorrow. With any errors you find please provide as many specifics as possible so I can recreate. This was never really a working datbase, only a demo. It was pulled from a much more complicated interface, but the basic concept of using a form bound to a "temp table" to populate a normal table was the same. So there are probably quite a few bugs. Mainly this was just to demonstrate the concept.

 

[MajP]

When you set a value back to Null there is a logic error.

The way it works is when you make a change on the "temp table" two things happen.
If there is not a record for that person and that date in the normal table it does an append query to the normal query entering a value for that person and that date. If there is a record in the normal table then it does an update query, changing the hours for that person and that date. Here is the missing logic. If a record is null in the temp table and a record exists in the normal table for that person and that date, it would then need to do a delete query to remove that record from the normal table. Currently it does nothing. When I get time I will fix.

I however can not replicate the other issues. Can you provide specifics? I can select any date on the calendar or scroll to any date. If I add or modify values they appear when I Open the form.

 

[SMHSleepy]

Thanks again. Did you try to replicate the errors using my modified version? I've attached a Word document with some screen captures of the errors I get. I know the "disappearing numbers" has something to do with my modification because that doesn't happen in the original; however, the other errors happen in both.

http://www.mediafire.com/?eveeo8z6adbqx5w

 

[MajP]

I cannot replicate the "values not appearing" problem. You version seems to work fine. It may be a timing issue on your computer, which can be fixed sometimes with doevents. Try adding the following.

Private Sub Form_Current()
Me.Dirty = False
loadSubFrmLabels
SaveHoursWorked
clearHours
LoadGrid (Me.dtmDate)
Me.subFrmCtlWorkHours.Form.txtBxStartDate = Me.dtmDate
'try adding this
DoEvents
Me.subFrmCtlWorkHours.Form.Requery
End Sub

This code is currently real innefficient because it is doing this everytime you move a record in the. How many employees will there be? It is ok for this small amount.

can you provide the line of code where the scroll wheel errors?

For debug of the update (which is probably the cause of the first problem as well)add this debug statement:

Public Sub LoadGrid(startDate As Date)
Dim rs As DAO.Recordset
Dim strSql As String
Dim endDate As Date
Dim dateWorked As Date
Dim I As Integer
Dim updateField As String
endDate = startDate + 13
strSql = "Select * from tblPersonWorkHours where dtmDate between #" & startDate & "# AND #" & endDate & "#"
Set rs = CurrentDb.OpenRecordset(strSql)
Do While Not rs.EOF
I = DateDiff("d", startDate, rs!dtmDate)
updateField = "day" & I + 1 & "Hours"
strSql = "UPDATE tblTempHours SET " & updateField & " = " & rs!hoursWorked & " WHERE personID_fk = " & rs!personID_fk
'add this
debug.print strSql
CurrentDb.Execute strSql
rs.MoveNext
Loop
End Sub

when this crashes print the sql string from the immediate window. There is a problem with the update query and my guess it has to do with the date value. Probably returning a null for some reason. you can also put a debug.print for "I".

 

[SMHSleepy]

Tried adding those codes but didn't get any sql string appearing, just the same old error box. Also, disappearing hours still there. I ran into another error, please see attachment.

I noticed that tblDummyDate sometimes gets filled with multiple dates. Is that supposed to happen? I thought they were supposed to get cleared afterwards like tblTempHours.

http://www.mediafire.com/?0zdg65lmi3oxa1j

 

[SMHSleepy]

Oh yeah, and this is pretty much the total number of employees we will ever have +/- one or two.

 

[MajP]

replace this for debug purposes:
Public Sub LoadGrid(startDate As Date)
On Error GoTo errLbl
Dim rs As DAO.Recordset
Dim strSql As String
Dim endDate As Date
Dim dateWorked As Date
Dim I As Integer
Dim updateField As String
endDate = startDate + 13
strSql = "Select * from tblPersonWorkHours where dtmDate between #" & startDate & "# AND #" & endDate & "#"
Set rs = CurrentDb.OpenRecordset(strSql)
Do While Not rs.EOF
I = DateDiff("d", startDate, rs!dtmDate)
updateField = "day" & I + 1 & "Hours"
strSql = "UPDATE tblTempHours SET " & updateField & " = " & rs!hoursWorked & " WHERE personID_fk = " & rs!personID_fk
CurrentDb.Execute strSql
rs.MoveNext
Loop
Exit Sub
errLbl:
MsgBox Err.Number & " " & Err.Description & " in Load Datea" & vbCrLf & "See debug"
Debug.Print "Error in load data"
Debug.Print strSql
Debug.Print Nz(rs!dtmDate, "NULL")
End Sub


The table should not add a date, but do not think it makes a difference.

All the problems seem to be the same in that the date is returned as a null or possibly 0. Not sure how this is happening. I still can not replicate it.

On the main form set allow additions = false in the properties.

 

[MajP]

also there could be a problem on your computer with the calendar control. It is an Active X control and sometimes they are problematic. Do you still get the crashes if you scroll the date with the arrows?