Why Does Access Reports not execute in order?... HELP (Long Read)..

[rdjohnso]

(Issue explination at bottom)

Code:
Option Explicit

'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;"

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
---------------------------

*** See Screenshot

http://www.lnrconsulting.com/temp/BlackWhite.gif

---------------------------

Issue:

I have a button on a form that when clicked opens a report.....

i have a part of the report at the top.... looks somewhat like an excel

grid with monthly cells.... for a person's Hours & Rate per month.

To get the Hours... i pass the PersonID and StartDate and EndDate for the month to get that person's Hours and then run the persons hours through another function to get that persons Cost....

There are 12 months starting with the Current Month (right now it is Feb so it would go Feb 06 - Jan 07).

I have a Total Hours and Total cost at the end of those 12 months...

that all works fine....

The issue is: below that in another 'section' (still in Detail)... I have other cells that should break out the Hours and Cost for same Person broken out by year....

I have the calculation in my top function... (the values are set there) and then I have global var's set at the very top of the module.....

and those var's are incremented appropriately and then called and set in functions at the bottom of the module....

THE WEIRD Thing is: somehow the report calls the bottom 2 functions 1ST (bad bad) instead of following the report flow which would call the top function which ultimately sets the global variables that are used in the bottom functions....

What am I missing here?

Thanks, Ron

 

[rdjohnso]

ttt