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]

Excellent! I was putting the single quotes in the wrong place, thanks for the help. Also, the shortcut control bar thing looks great, can't wait to play around with it.

 

[SMHSleepy]

Not as easy as I thought. I can modify the shortcut control bar db so that it shows me a list of my Work Codes instead of "products", but how to I get it to save that value to the appropriate place on my main db? Perhaps it may to be too much to ask, but could you try this for me? The table used to create the cascading menu should have the following fields: WorkCodeID, Shift, ShiftCode, ShiftDescription, and Hours. It should save the WorkCodeID in the field but show 'Shift'

 

[MajP]

when you build a commandbar control item you have these properties
Tag
Caption
onAction

Tag: The tag property is like any other tag property - you can stick the WorkCodeID in this property.
Caption: That would be the Shift
OnAction: name of a procedure to run when the item selected

In my code you can see me doing that here

Set cbSupCtrl = cbSup.Controls.Add(msoControlButton, , SupValue)
cbSupCtrl.Tag = SupValue
cbSupCtrl.caption = SupCaption
cbSupCtrl.OnAction = "SelectSupplier"

So yours would be something like
.OnAction = "AssingnWorkCode"

Public sub AssingnWorkCode
dim ctrl as access.control
set ctrl = me.activecontrol
ctrl.value = CommandBars.ActionControl.Tag
end sub

I have not tried this. I assume if you right click in a location to bring up the commandbar you also make that control the active control.

Also provide me your table in a standalone db with the real data for the commandbar.

If you think these are not going to change you can just hard code them into code and avoid looping a table. However the table gives a lot of flexibility.

 

[MajP]

http://www.4shared.com/document/-123kndj/MajPSpreadSheetV3.html

right click in a "cell".

 

[SMHSleepy]

Thanks for that! I will work with it and keep you posted.

 

[SMHSleepy]

Okay, here's what I have so far. Everything works as I asked but of course what I really need is something more complicated. I tried to work with it to suit my needs but can't figure it out. Perhaps you could help? I've added a couple of fields to tblPersonWorkHours. The fields are now: ID, personID_fk, dtmDate, shift, hours, explanation. "shift" is the work code (i.e. N for Night shift, D for day shift, etc.). So far, the db ignores the hours and explanation and allows me to right click to select the type of shift and have it enter the code in the box. What I need is to be able to:
1. make multiple entries for one employee on the same day (i.e. SH (stat holiday) + 3.75 hrs of LOA on same day)
2. manually assign the hours and make an explanation if necessary (e.g. if someone works and extra 2 hours for a day shift, the code is still "D" but the hours change from 7.5 to 9.5 and I type "stayed late for extra work" in the explanation section.

Could you take a look? Thanks.

sorry, will send links to files in a separate post. Can't do it on this computer..

 

[SMHSleepy]

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

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

 

[MajP]

To do this I would make the cells in the grid uneditable (locked), and get rid of the shortcut menu. To add/edit data you would enter a cell and either click/doubleclick, key down or some other event. It would open a small popup. I would be a continous form with
Shift, hours, notes. So you can add/modify/delete as many records for that person for that day. You close the form and the grid would show some sort of concatenation. Example. In the popup
SH 6 Some note
LOA 3.75 Some Other Note

The grid could concatenate to show
SH(6)/LOA(3.75) or simply SH/LOA

In truth this greatly simplify things. The grid is now used for display purposes only, and you never write from the grid to the table (that was problematic) You Actually edit the table tblPersonWorkHours directly in the pop up. Writing to the grid becomes only slightly more complicated in that you have to concatenate a record from multiple records.

If that sounds OK, then design the popup form. When you open the pop up pass in the personID and date in open Args. You can pass in like "1;1/1/2011". In the forms on load event use the split command to split the open args so you can get the personID and Date. Set the default value for personID, dtmDate controls to the personID and date. So if you add a new record they would get the correct values. Or you can just set these values in code.


Disable any code that calls the "savedHoursWorked", that writes from the grid.

If you can figure out the pop up form, then provide me an update. I can then help with concatenating the values and putting in the grid.

 

[SMHSleepy]

Thanks MajP,

I think I follow what you're saying and it sounds like the way to go. Would the pop up form be able to show all the shifts for that person on that date? Also, can I delete an individual shift or does it have to be all shifts for that person on that date? I think I may need a little more help with this.

 

[MajP]

If you click in a cell you can determine the person and the date. You would pop open a continous form bound to some query of the person work hours table. It would be filtered to that person and that date so you would see each shift. You could add,delete, modify any of the shifts.

 

[SMHSleepy]

Great. I'll get working on that next week and keep you posted. Thanks and have a great weekend!

 

[SMHSleepy]

Okay, I made qryClickCell but only put in temporary conditions, later to be replaced with the split components.

I also made subFrmPopUp based on qryClickCell. When you right click on the shift field, the drop down choices are available.

I can't figure out where to put the code to call up subFrmPopUp.

Also, if there's no existing entry when I call up subFrmPopUp, I'd like it to prefill the date worked.

Here's the db so far. Can you take a look?

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

 

[MajP]

http://www.4shared.com/document/1P225tz0/Maj_P_Schedule_Payroll_v2.html

I put the pop up into a subform. This simplifies some things. Double click in a grid and the form pops up. Any records you add default to the current user and date.
If you change the combo you can keep the pop up open and add records for a different user or view records of a different user. You can also add a record and change the date.

Each cell in the grid calls this function on the double click event and passes the number of days to add.

Public Function addShifts(dayToAdd As Integer)
  Dim personID As Long
  Dim dtmDate As Date
  personID = Me.personID_fk
  dtmDate = getSelectedDate + dayToAdd
  enterShifts personID, dtmDate
End Function

That function calls this procedure passing in the corresponding person ID and date for a given cell

Public Sub enterShifts(personID As Long, dtmDate As Date)
  Dim strArgs As String
  If IsNumeric(personID) And IsDate(dtmDate) Then
    strArgs = personID & "; " & Format(dtmDate, "MM/DD/YYYY")
    DoCmd.OpenForm "frmPopUpShifts", , , "personID_fk = " & personID & " AND dtmDate = #" & dtmDate & "#", , acDialog, strArgs
  End If
End Sub

this procedure pops open the form passing an openArgs argument in the format "personID;date" (ex 1;1/1/2011 )

When the form opens it splits the open args into personID and the selcted date setting the control values. The subform is linked to the controls. So the records in the subform correspond to that person and that date. Any new records would get values for the given person and date.

Private Sub Form_Open(Cancel As Integer)
  If Not Trim(Me.OpenArgs & " ") = "" Then
    Me.cmboPerson = Split(Me.OpenArgs, ";")(0)
    Me.txtBxDate = Split(Me.OpenArgs, ";")(1)
  Else
    Me.txtBxDate = Date
    Me.cmboPerson = Me.cmboPerson.ItemData(0)
  End If
End Sub

Only thing left to code is the concatenation of the multiple shifts. What do you want to show if the grids if there is a single record? Multiple records?
N:6/SH:4
N/SH

 

[SMHSleepy]

This is awesome! Nice touch with the ability to change the person in the popup form. This will make data entry easier and faster. Iam so grateful for all your help.

The N/SH format for concatenation would be wonderful.

Also, is it possible to have a default number of hours put in the field for each shift choice, but have the ability to change it if necessary? For example, when I choose D it automatically puts 7.50 in the hours field but I can change it to 8.50 if the person works an extra hour. When I choose any of the "other" codes however, it leaves the hours blank and setfocus is put on hours so I can enter them.

Thanks again!

 

[MajP]

Also, is it possible to have a default number of hours put in the field for each shift choice, but have the ability to change it if necessary? For example, when I choose D it automatically puts 7.50 in the hours field but I can change it to 8.50 if the person works an extra hour. When I choose any of the "other" codes however, it leaves the hours blank and setfocus is put on hours so I can enter them.

Here is the code that assigns the code. So after you assingn it you can do whatever you want.

Public Sub assignWorkCode()
  Forms("FrmPopUpShifts").subFrmPopUpShifts.Form.ActiveControl = CommandBars.ActionControl.Tag
End Sub

to

Public Sub assignWorkCode()
  Dim frm As Access.Form
  Set frm = Forms("FrmPopUpShifts").subFrmPopUpShifts.Form
  frm.shift = CommandBars.ActionControl.Tag
  Select Case CommandBars.ActionControl.Tag
   Case "D"
     frm.hours = 7.5
  'if you have other cases add here
  'Case somehthing else
  End Select
  frm.hours.SetFocus
End Sub

With that said the command bar thing (although cool) is probably a little unnecessary at this time. Just change the shift to a combo box that shows the description and saves the code.

Make it a combo and you can keep the current width.

rowsource:SELECT tblWorkCodes.WorkCodeID, description FROM tblWorkCodes;
column count : 2
column widths: 0.25";2"
list width: 2.25"

when you pull down you will see
D Day Shift
LD Leave of Absence Day
LN Leave of Absence Night
LO Leave of Absence Other (enter hours)
N Night Shift
SD Sick Day
SH Stat Holiday
SN Sick Night
SO Sick Other (enter hours)
VD Vacation Day
VN Vacation Night
VO Vacation Other (enter hours)

but is will show only the code when not expanded. I think this is better and less complicated than the shortcut menu

Now you can put all kinds of code in the after update of the combo

 

[MajP]

To add more functionality to your pop, up you can modify the following properties of the subform control through code

link masterfields: cmboPerson;txtBxDate
link childfield: personID_fk;dtmDate

If you had a button (Show all shifts for given person) on the form that changed it to
link masterfields: cmboPerson
link childfield: personID_fk

you could show all records for a given person. At the same You could also change the subform recordsource to a query between the startdate (default date) and the enddate (default date + 13 for 2 week form)

or
If you had a button (Show all shifts for selected date) on the form that changed it to
link masterfields: txtBxDate
link childfield: dtmDate

That would show all records for a given date.

With that said you may want a calendar control or some next previous buttons associated with txtBxDate. You may also want a combo box for the person that shows last Name, First name and bound to person id. Then you could from the pop up, do records for multiple people on multiple dates, show/add/edit all shifts for a given person, and show/add/edit all records for a given date.

 

[MajP]

This should do the concatenation.
basically the records are sorted by person id and date. As it loops through if the next record has the same id and date it concatenates the results.

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
  Dim strShift as string
  Dim tempPersonID as long
  Dim tempDate as date
  EndDate = startDate + 13
  'strSql = "Select * from tblPersonWorkHours where dtmDate between #" & startDate & "# AND #" & endDate & "#"
  strSql = "Select * from tblPersonWorkHours where dtmDate between #" & Format([startDate], "MM/DD/YYYY") & "# AND #" & Format([EndDate], "MM/DD/YYYY") & "# ORDER BY personID_fk, dtmdate"
  Set rs = CurrentDb.OpenRecordset(strSql)
  if not rs.eof and rs.bof then

  end if
  Do While Not rs.EOF
     I = DateDiff("d", startDate, rs!dtmDate)
     updateField = "day" & I + 1 & "Hours"
     'check to see if new person and date, else concatenate
     if tempDate = rs!dtmDate and tempPersonID = rs!personID_fk then
       strShift = strShift & "/" & rs!Shift
     else
       strShift = rs!shift
     end if
     strSql = "UPDATE tblTempHours SET " & updateField & " = '" & strShift & "' WHERE personID_fk = " & rs!personID_fk
     CurrentDb.Execute strSql
     tempPersonID = rs!personID_fk
     tempDate = rs!dtmDate
     rs.MoveNext
  Loop
End Sub

 

[SMHSleepy]

Thank you! I'm putting it all together now and will let you know how it goes.

 

[SMHSleepy]

Okay, got it working very nicely. Changed shift to a combo box instead of using command bar. I added a DefaultHrs column to tblWorkCodes and brought the values forward in the AfterUpdate event of my combobox with Me.hours = Me.shift.Column(2).

The concantenation sub works like a charm also.

The only thing now is when I click on an existing entry and delete it in the subform, it still leaves the code in the grid even though it no longer exists. If I redraw the grid, it goes away as it should. I tried using a requery command in various events but can't get it to work. Can you guide me in the right direction? Thanks again!

 

[MajP]

move the procedure
clearHours
from the payroll form module into the mdlUtilities.

Now when you close the popup

Private Sub Form_Close()
clearHours
LoadGrid getSelectedDate
End Sub

It was rewritting to the grid, but not clearing existing values.