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.

 

[MajP]

Play with shortening the delay. Also if you can show where it helped I would be interested.

This to me is a big shortfall in Access. Access needs a native unbound grid control. There is an active x control called a flexgrid, but versioning it and registering it is a real bear. If access had an native unbound grid control this would be easier.

 

[MajP]

See if you can get this to work. These may are may not even load.

http://www.rogersaccesslibrary.com/forum/topic429.html

http://www.rogersaccesslibrary.com/forum/flex-grid-demo_topic511_post518.html

 

[SMHSleepy]

Hey MajP, guess what? That time delay thing didn't work after all. I thought it did, but after playing around a bit more it screwed up even more.

HOWEVER, I finally figured out why your db worked on some computers and not on others. I'm from Canada and some of our computers have the regional settings set as English (Canada) and some are English (U.S.). Well, it works on the U.S. ones but not on the Canada ones. I guess it has something to do with the date format. I tried keeping the regional settings as Canada and just changing all the code to dd/mm/yyyy but that didn't work well. Therefore, I waved the white flag and conceded U.S. defeat over Canada and simply changed our regional settings to U.S. Another victory for Obama!

Now I just hope this doesn't affect my other database projects.....

Thanks again for all the help.

 

[MajP]

You may not have had to change the regional settings. When working with sql dates from code, you have to force the format into mm/dd/yyyy to ensure proper interpretation. I did it in most places, but may not have done that everywhere. Then regardless of the regional settings, Access will interpret properly.

 

[MajP]

I will take a look at the code because, the regional settings could definitely cause these problems based on some of the logic that I used.

 

[MajP]

This may be the issue. If you look, everywhere I write a SQL string I format the date to be mm/dd/yyyy. You need to do this regardless of your regional settings.

I did not do it in the code below
strSql = "Select * from tblPersonWorkHours where dtmDate between #" & startDate & "# AND #" & endDate & "#"

change to

strSql = "Select * from tblPersonWorkHours where dtmDate between #" & Format([startDate], "MM/DD/YYYY") & "# AND #" & Format([endDate], "MM/DD/YYYY") & "#"


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

 

[MajP]

Stick this in your code library it comes in very handy especially for Canadians who often have both US and European regional settings. This is what I normally use. I was a little lazy.

Function SQLDate(varDate As Variant) As Variant
     If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function

then whenever you use a date in a sql string
example:

strSql = "Select * from tblPersonWorkHours where dtmDate between #" & startDate & "# AND #" & endDate & "#"

to

strSql = "Select * from tblPersonWorkHours where dtmDate between " & sqlDate(startDate) & " AND " & sqlDate(endDate)

Please read.

http://allenbrowne.com/ser-36.html

I am not sure if this holds for SQL Server. I am not a SQL developer, but from what I read it is a yyyy-mm-dd format

http://www.connx.com/products/connx/Connx 8.5 UserGuide/sql_time.htm

 

[SMHSleepy]

Okay, I made the correction. I can enter hours anywhere and use the calendar control to move around to different dates without any problems. When I close the form, the correct data is in tblPersonWorkHours. However, when I open the form again, it deletes everything from tblPersonWorkHours. I'm assuming it's something to do with the open event or load event on the form but I can't figure out what's causing this. Can you take a look? Btw, just as you said, the regional settings make no difference now that the code has been corrected. Thanks.

 

[MajP]

Did you do what was described in thread 3Jun 10:18? Previously before that fix it when it opened it would write to the table before the grid was loaded with existing data. Hence it was deleting everything. It need to load the grid first then save. After that it saves the data then reloads.

Post an updated version of your db.

 

[SMHSleepy]

oops, you're right. I mistakenly reverted back to a previous version before that change. Works well now, thank you!

 

[SMHSleepy]

Hey MajP,

Is it too much trouble to change "hoursWorked" to a Text (String) field instead of numeric (single)? I changed it to Text in the tables and changed "dim hoursWorked as String" in the code but I'm still getting errors. I think it has something to do with incorrect format of the following.

 hoursWorked = rs.Fields(fldName)
       If DateExists(personID, dateWorked) Then
         Call editRecord(personID, dateWorked, hoursWorked)
       Else
         Call addRecord(personID, dateWorked, hoursWorked)
       End If

 

[MajP]

I do not have the database in front of me but in places where you are doing inserts, strings have to have 'single quotes' around them.

strSql = "UPDATE tblTempHours SET " & updateField & " = '" & rs!hoursWorked & "' WHERE personID_fk = " & rs!personID_fk

so that it resolves to '8' and not 8

 

[SMHSleepy]

Arrrgh! Going crazy trying to change it to text. Can you take a look for me? I've renamed the db and added a 6 week version as well. Thank you.

http://www.mediafire.com/?5md8a4s353mta5d

 

[SMHSleepy]

Oh, you probably need this dll as well. I found it to help me temporarily prevent mouse wheel scrolling.

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

 

[MajP]

Can you verify the link? I am unable to download. Could be my end.

 

[SMHSleepy]

I'll try that again. Here are the files:

http://www.mediafire.com/?1fjz14hz19n2bq4

and

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

 

[MajP]

Before doing this can you explain the purpose? What would go in there for text. There may be a different approach. By making those fields text you give up the ability to do things like calculate hours worked/assigned, and you increase complexity. There is a lot of ripple in the queries, code, and tables to do this.

I think if I was doing this I would change the grid to text fields. Then I would add a new text field ("dayNote") in the "tblPersonWrkHours" table. I would leave the existing hours worked field as numeric

So when you load the grid you read the appropriate days, if there is a value in the "hoursWorked" field you insert the value into the grid converted to text, if there is text in the "dayNote" field you insert the text into the grid.

Now when you save from the grid to the "tblPersonWrkHours" you do the same as you always do if the grid field value is numeric. If the value in the grid is alphabetic then you add new procedures to save the string to the the new "dayNoteField". If you look at the current code you have three cases you have to write code for:
"Record exists in the hours worked table but null in the grid"
"Record exists in the grid and not in the hours worked table"
"Record exists in the grid and in the hours worked table, and the value has changed"
So the code would have to be written for these three cases again, but now for a text string.
Oh yeah you can probably double that because you have to check if a note exists and now it is numeric and vice versa.
This is doable, and no more complex than the existing code, but the changes are not trivial and would take some time. There are a lot of if checks.

With that said, I would do it that way because I know there is always mission creep and eventually you will want to do lots of queries on the hours worked. So I would take the design complexity to ensure a more flexible model. Also saving numeric values in a text field makes me cringe.

However, since this is not mine, changing this simply to a text field in the tblPersonWrkHours and in the grid is a much easier solution to implement. Although a poorer approach.

 

[MajP]

http://www.4shared.com/document/5Jl1-Sgg/MajP_Schedule_Payroll_V1.html

With that all said it took about 3 minutes to just make them text fields. It required single quotes in the procedures and change the datatype of the function parameters.

 

[SMHSleepy]

Sorry for the lack of explanation. It's actually more complicated than just having hours worked per day. I didn't want to go into that because I thought I could just change it around to suit my needs. Our work shifts consist of either a night shift (N) or day shift (D). Plus there are other codes for vacation, sick, etc. Since this database will be used as both a schedule and a payroll calculator, I want to be able to enter the text code and then create a payroll report which extracts the appropriate codes and converts them to the associated numeric hours. Another complicated step is that the Night shift is actually split into evening (4 hrs) and overnight (8 hrs) so that for payroll, it should be written as 2 separate work hours and dates. On the schedule, however, this looks confusing so I just want it to show as 'N' on the day the shift starts.

Thank you very much for all your help. I will now start the process of taking your framework and customizing it to my needs.

 

[MajP]

If you change all your grid textboxes to combos then you can have pulldown choices for the codes or type in a number. It is a little ugly.

However you may find this a more pleasing approach. This is using a shortcut menu for entering data.

http://www.4shared.com/document/hiAlBmC9/ShortCut_Control_Bar_V3.html

This would allow you to right click a textbox and get a cascading menu of codes.
click the demo button to see. Then select a radio button and right click into the textbox at the bottom.