Need some help..... Order of functions executed in Access?
Have my control source calling the 2 'calculating Total' functions at the bottom to populate total current year & Next Year's HOURS...... (not sure how else to do this)....
I would think this should work only because the main function is called and summated in the report's section above the Total's section ..... but the end result...
The totals calculate correctly on the second pull of the report and is continually 1 report behind on the totals...
sorry for the long post... any help (here or outside of this forum) at this point would be greatly apprecited.
CODE:
'Get Total running Hours for currentHours and nextHours
Public mHPersonID As Integer
Public mTotHoursCrtYr As Long
Public mTotHoursNxtYr As Long
'Get # of Allocation records for a PersonID 'passed in'
Public Function PersonHrs(PersonID As Integer, StartDate As Date, EndDate As Date) As Integer
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rsPerson As ADODB.Recordset
Dim sConnString As String
Dim iCtr As Integer
Dim iStartDate As Date
Dim iEndDate As Date
Dim iAllocation As Double
Dim iHoursPerDay As Integer
Dim iPersonHours As Integer
If mHPersonID <> PersonID Then
mTotHoursCrtYr = 0
mTotHoursNxtYr = 0
End If
'Set hours per day to use in calculations
iHoursPerDay = 8
sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentProject.Path & "\Blue&White.mdb;"
'sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Ron Johnson\My Documents\Blue&White.mdb;"
conn.Open sConnString
Set cmd.ActiveConnection = conn
'REPLACE MYTABLE WITH YOUR OWN TABLE
cmd.CommandText = "SELECT * FROM tbl_PersonAllocation where PersonID = " & PersonID
cmd.CommandType = adCmdText
Set rsPerson = cmd.Execute
Do While Not rsPerson.EOF
For iCtr = 0 To rsPerson.Fields.Count - 1
'OutPuts Name and Value of each field
Debug.Print rsPerson.Fields(iCtr).Name & ": " & _
rsPerson.Fields(iCtr).Value
Next
'StartDate and EndDate before (Less Than) AllocStDt - ignore the record
If StartDate < rsPerson!AllocationStartDate And EndDate < rsPerson!AllocationStartDate Then
'Do Nothing
'StartDate and EndDate after (Greater Than) AllocEndDt - ignore the record
ElseIf StartDate > rsPerson!AllocationEndDate And EndDate > rsPerson!AllocationEndDate Then
'Do Nothing
Else
If StartDate >= rsPerson!AllocationStartDate Then
If EndDate <= rsPerson!AllocationEndDate Then
iStartDate = StartDate
iEndDate = EndDate
iAllocation = rsPerson!Allocation
End If
Else
'if StartDate before AllocStDt --- iStartDate = AllocStDt
'else iStartDate = StartDate
If StartDate <= rsPerson!AllocationStartDate Then
iStartDate = rsPerson!AllocationStartDate
iAllocation = rsPerson!Allocation
Else
iStartDate = StartDate
iAllocation = rsPerson!Allocation
End If
'if EndDate after AllocEndDate --- iEndDate = AllocEndDate
'else iEndDate = EndDate
If EndDate >= rsPerson!AllocationEndDate Then
iEndDate = rsPerson!AllocationEndDate
iAllocation = rsPerson!Allocation
Else
iEndDate = EndDate
iAllocation = rsPerson!Allocation
End If
End If
End If
rsPerson.MoveNext
Loop
'compute hours
iAllocation = iAllocation / 100
iPersonHours = (iHoursPerDay * iAllocation) * DeltaDays(iStartDate, iEndDate)
If Year(StartDate) = Year(Date) Then
mTotHoursCrtYr = mTotHoursCrtYr + iPersonHours
Else
mTotHoursNxtYr = mTotHoursNxtYr + iPersonHours
End If
'Cleanup
rsPerson.Close
conn.Close
Set conn = Nothing
mHPersonID = PersonID
PersonHrs = iPersonHours
End Function
-------------------
Public Function TotHoursCrtYr() As Long
'this is executing before the main code above....
TotHoursCrtYr = mTotHoursCrtYr
End Function
-------------------
Public Function TotHoursNxtYr() As Long
'this is executing before the main code above....
TotHoursNxtYr = mTotHoursNxtYr
End Function
Have my control source calling the 2 'calculating Total' functions at the bottom to populate total current year & Next Year's HOURS...... (not sure how else to do this)....
I would think this should work only because the main function is called and summated in the report's section above the Total's section ..... but the end result...
The totals calculate correctly on the second pull of the report and is continually 1 report behind on the totals...
sorry for the long post... any help (here or outside of this forum) at this point would be greatly apprecited.
CODE:
'Get Total running Hours for currentHours and nextHours
Public mHPersonID As Integer
Public mTotHoursCrtYr As Long
Public mTotHoursNxtYr As Long
'Get # of Allocation records for a PersonID 'passed in'
Public Function PersonHrs(PersonID As Integer, StartDate As Date, EndDate As Date) As Integer
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rsPerson As ADODB.Recordset
Dim sConnString As String
Dim iCtr As Integer
Dim iStartDate As Date
Dim iEndDate As Date
Dim iAllocation As Double
Dim iHoursPerDay As Integer
Dim iPersonHours As Integer
If mHPersonID <> PersonID Then
mTotHoursCrtYr = 0
mTotHoursNxtYr = 0
End If
'Set hours per day to use in calculations
iHoursPerDay = 8
sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentProject.Path & "\Blue&White.mdb;"
'sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Ron Johnson\My Documents\Blue&White.mdb;"
conn.Open sConnString
Set cmd.ActiveConnection = conn
'REPLACE MYTABLE WITH YOUR OWN TABLE
cmd.CommandText = "SELECT * FROM tbl_PersonAllocation where PersonID = " & PersonID
cmd.CommandType = adCmdText
Set rsPerson = cmd.Execute
Do While Not rsPerson.EOF
For iCtr = 0 To rsPerson.Fields.Count - 1
'OutPuts Name and Value of each field
Debug.Print rsPerson.Fields(iCtr).Name & ": " & _
rsPerson.Fields(iCtr).Value
Next
'StartDate and EndDate before (Less Than) AllocStDt - ignore the record
If StartDate < rsPerson!AllocationStartDate And EndDate < rsPerson!AllocationStartDate Then
'Do Nothing
'StartDate and EndDate after (Greater Than) AllocEndDt - ignore the record
ElseIf StartDate > rsPerson!AllocationEndDate And EndDate > rsPerson!AllocationEndDate Then
'Do Nothing
Else
If StartDate >= rsPerson!AllocationStartDate Then
If EndDate <= rsPerson!AllocationEndDate Then
iStartDate = StartDate
iEndDate = EndDate
iAllocation = rsPerson!Allocation
End If
Else
'if StartDate before AllocStDt --- iStartDate = AllocStDt
'else iStartDate = StartDate
If StartDate <= rsPerson!AllocationStartDate Then
iStartDate = rsPerson!AllocationStartDate
iAllocation = rsPerson!Allocation
Else
iStartDate = StartDate
iAllocation = rsPerson!Allocation
End If
'if EndDate after AllocEndDate --- iEndDate = AllocEndDate
'else iEndDate = EndDate
If EndDate >= rsPerson!AllocationEndDate Then
iEndDate = rsPerson!AllocationEndDate
iAllocation = rsPerson!Allocation
Else
iEndDate = EndDate
iAllocation = rsPerson!Allocation
End If
End If
End If
rsPerson.MoveNext
Loop
'compute hours
iAllocation = iAllocation / 100
iPersonHours = (iHoursPerDay * iAllocation) * DeltaDays(iStartDate, iEndDate)
If Year(StartDate) = Year(Date) Then
mTotHoursCrtYr = mTotHoursCrtYr + iPersonHours
Else
mTotHoursNxtYr = mTotHoursNxtYr + iPersonHours
End If
'Cleanup
rsPerson.Close
conn.Close
Set conn = Nothing
mHPersonID = PersonID
PersonHrs = iPersonHours
End Function
-------------------
Public Function TotHoursCrtYr() As Long
'this is executing before the main code above....
TotHoursCrtYr = mTotHoursCrtYr
End Function
-------------------
Public Function TotHoursNxtYr() As Long
'this is executing before the main code above....
TotHoursNxtYr = mTotHoursNxtYr
End Function
