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.

 

[SMHSleepy]

No problems with arrows. I don't care about being able to scroll through the calendar or even arrows for that matter. I'm happy to just click the date with the mouse pointer. I'll try the debug code now.

 

[SMHSleepy]

Okay, I inserted the code above. Here's the step by step I took to produce the error.

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

 

[MajP]

It appears that the errors are a chain of events. unfortunately if I do exactly what you show, it works fine.

I think there may be a problem with your computer and the calendar control. Please provide the results from the debug window after an error I added some additional information. I am guessing that when you change the calendar it is returning a null value, and therefore the update query fails. To test this try the arrows at the top of the form and see if you still get an error. Also put this in your code for more debugging. Couple more debugs and prints. I am suspecting an issue with the startDate.

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
msgbox "StartDate: " & startDate
debug.print "start Date " & startDate
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)
msgbox "I " & I
debug.print "I " & I
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

 

[SMHSleepy]

Okay, this is very frustrating. The database is located on a network drive. When I use my main computer to run it, I get all those errors. This morning, I tried using a different computer and it works like a charm (well, except the inability to delete previously entered hours - which we discussed way back). So there is obviously no problem with the code. Could I have some settings set up differently on my main computer? This will be a bugger to figure out. Any suggestions?

 

[SMHSleepy]

Just a thought, is there a way I can replace the MSCAL.Calendar.7 with a different calendar? I think you're right that it's a problem with that active x control specifically on that computer.

 

[MajP]

Yes you can simply use any control bound to the date field. For test purposes you could simply try a textbox.

I am not sure however if it is the control or if it is a timing issue when run on the network. The fix for most timing issues is a doevents. Sometimes this requires doevents within a timer loop to delay the execution of a procedure. The other thing to help would also to add more checks for null values and some more error checking in general. You are getting into conditions I did not expect therefore wrote no code to check.

 

[MajP]

Here is the fix to return values to null

change this

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"
     dateWorked = (startDate + I) - 1
     If Not IsNull(rs.Fields(fldName)) Then
       hoursWorked = rs.Fields(fldName)
       If DateExists(personID, dateWorked) Then
         Call editRecord(personID, dateWorked, hoursWorked)
       Else
         Call addRecord(personID, dateWorked, hoursWorked)
       End If
       'Debug.Print personID & " " & dateWorked & " " & hoursWorked
     Else
       'changed to null
       If DateExists(personID, dateWorked) Then
         Call deleteRecord(personID, dateWorked)
       End If
     End If
   Next I
   rs.MoveNext
 Loop
End Sub

and add this

Public Sub deleteRecord(personID As Long, dateWorked As Date)
  Dim strSql As String
  strSql = "Delete * from tblPersonWorkHours WHERE personID_fk = " & personID & " AND dtmDate = #" & Format([dateWorked], "MM/DD/YYYY") & "#"
  Debug.Print strSql
  CurrentDb.Execute strSql
End Sub

 

[SMHSleepy]

Hey MajP, I did as you said above but now it deletes everything as soon as I exit or change dates. I need it to delete the record only if I delete the hours (i.e. make it null). Thanks.

 

[MajP]

Is this on the same computer giving you the problem? Because the problem you described early would cause it not to load into the grid, but records exist. So when you update it would set everything to null. Try it on the local machine first? You can post your new db.

BTW you should never run Access from a network drive. If you want you can split the database and put the backend with just the tables on the network. The front end with the code, forms, reports, macros should be on a local machine. Then you can give multiple users a copy of the front end linked to the tables on the backend.

 

[SMHSleepy]

I tried it on the local computer but still get the same problem. Here's the db, can you please take a look? Thanks.

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

 

[MajP]

What was happening was when you load a form the next event is the forms on current event. The on current was first saving any data first then reloading the grid. Since there was nothing loaded in the grid at the very beginning it basically assumed everything was null. So when you open the form the first thing is to load the grid. Then all the changes can follow the order of the current event. So change this.

Private Sub Form_Load()
'DoCmd.Maximize
Me.subFrmCtlWorkHours.Form.txtBxStartDate = Me.dtmDate
loadSubFrmLabels
'When you first open the form need to load the grid
'before the On current event tries to save it
LoadGrid (Me.dtmDate)
Me.Requery
End Sub

 

[MajP]

Once you get this working, I suggest pulling out the code from the on current event and putting it into more specific location. Probably the after update of each control. The code is lazy because basically with the on current it occurs whenever moving to a new record. However, this is very inefficient because it is saving and reloading the grid all the time.

 

[SMHSleepy]

Hi MajP, thanks for your help. I know it must be frustrating for you since it works fine on your computer but not on mine. I tried the code above but it still deletes either everything, or everything after the first entry (not sure how I got it to do that).

 

[MajP]

Try my version.

http://www.4shared.com/document/UXvEclxN/MajPSpreadSheetVSMH_Edit-2.html

If this does not work. Give me a step by step to recreate.

 

[SMHSleepy]

okay, tried again on a different computer. It works on 2 computers so far and doesn't work on 2 computers. I can't figure out what the heck is different about the computers. I'll try to figure it out but right now I'm so confused!

 

[MajP]

It really appears to be a timing issue. In attempt to fix your problem I added a delay loop. The construct is something like this:

X = Timer()
Do While X + 10 > Timer()
DoEvents
Loop

This actually caused the problem on my machine, because I put the timer in the wrong location. So basically what happens is the form events are competing with the code execution so things do not happen in the correct order. You are reading and writing to tables before the events finish. Working off the network can cause a delay.

I think this is the solution, just need to figure out where to put it. Different machines at difference speeds can cause the issue.

 

[SMHSleepy]

I can't figure out where to put the code. I tried and got an undefined variable error. Then I put Dim X As Integer and got some other error. Can you please spoon feed me and put the code in already and send the db again? Thanks!

 

[SMHSleepy]

Another confusing thing: All the computers are exactly the same (hardware and software). It makes no sense to me why it works on some computers and not others.

 

[MajP]

Here is a .25 second delay timer.

then just call addDelay.

Public Sub addDelay()
'add timer loop for unknown
Const delay = .25
Dim x As Long
x = Timer()
Do While x + delay > Timer()
DoEvents
Loop
End Sub

I have not found the correct place to put it, but have found where not to put it.

 

[SMHSleepy]

MajP, you are brilliant. I added the code and called on the addDelay in a few different places. Now it works! The best part is, now I understand the reason behind the error and can apply it as necessary. Thanks again. I think we can finally close this thread!