What i am tasked with is to modify an existing process that calculates the hours and minutes worked, which is entered on a form using controls bound to fields in a table with simple functions running the calculations.
The initial process had 7 text boxes for data entry.
Each control had both hours and minutes entered, example: 7.2, and called the function
Expand|Select|Wrap|Line Numbers
- Public Function OC_Add_Hours()
- 'Dim ri, pfh, tth, pht, ht, postht, at, th As Variant
- 'ri = Forms![attorney statements]![releasee interview]
- 'pfh = Forms![attorney statements]![prep for hrg]
- 'tth = Forms![attorney statements]![travel to hrg]
- 'pht = Forms![attorney statements]![pre hrg time]
- 'ht = Forms![attorney statements]![hear time]
- 'postht = Forms![attorney statements]![post hrg time]
- 'at = Forms![attorney statements]![appeals time]
- 'th = Forms![Attorney Statements]![total hours]
- 'th = ri + pfh + tth + pht + ht + postht + at
- 'Forms![attorney statements]![total hours] = th
- End Function
In the function, th = "total hours" and this control holds the result. The data entered comes from a form sent in from clients with the time they spent working on a case. This time is currently being converted by the person entering the data using a conversion table showing,
"1 = 2 all the way up to 60 = 100". An example on the sheet states, Total hours worked is 6hrs 20 min. The conversion will be 6.33. This confused me!!
They want to get rid of this conversion and just calculate the time by 60 instead of 100.
What I did is separate the hours from the min by creating 7 new controls to capture the minutes. The new form now has the original control "releasee interview" for the hours worked entry and the new control "Rel Interview Min" for the minutes worked capture. I followed this process for all 7 controls. I am no loner calling the hours function and created the routine,
Expand|Select|Wrap|Line Numbers
- Private Sub Rel_Interview_Min_AfterUpdate()
- Dim ri, rim, rit As Long
- ri = Forms![attorney statements]![releasee interview]
- rim = Forms![attorney statements]![Rel Interview Min]
- 'rit = Forms![attorney statements]![Rel Interview Total]
- rit = ri * 60 + rim
- Forms![attorney statements]![Rel Interview Total] = rit
- End Sub
Then I created seven controls that will not be visible, using the same naming style, except they end with total to hold this result.
My thinking is to change everything into minutes to be held in the Total min controls and then in the "total hours" control, in the "On Enter" property run this routine:
Expand|Select|Wrap|Line Numbers
- Private Sub total_hours_Enter()
- Dim rit, pfht, ttht, phtt, htt, pohtt, att, th As Long
- rit = Forms![attorney statements]![Rel Interview Total]
- pfht = Forms![attorney statements]![Prep for Hrg Total]
- ttht = Forms![attorney statements]![Travel to Hrg Total]
- phtt = Forms![attorney statements]![Pre Hrg Time total]
- htt = Forms![attorney statements]![Hrg Time Total]
- pohtt = Forms![attorney statements]![Post Hrg Time Total]
- att = Forms![attorney statements]![Appeals Time Total]
- 'th = Forms![attorney statements]![total hours]
- th = rit + pfht + ttht + phtt + htt + pohtt + att
- Forms![attorney statements]![total hours] = th
- End Sub
and in the "On Exit" property run this routine:
Expand|Select|Wrap|Line Numbers
- Private Sub total_hours_Exit(Cancel As Integer)
- Dim th As Long
- 'th = Forms![attorney statements]![total hours]
- th = Forms![attorney statements]![total hours]
- Forms![attorney statements]![total hours] = th / 60
- End Sub
I hope what i am trying to do is clear, i can't seem to figure out how to keep the minutes under 60, and stop the division keeping the remainder.
Any help is greatly appreciated