Still very new to this VB Module coding thing.... need point in the right dir.
I have a report with a need to populate a grid of information using 2 other modules I've written....
Goal: I need to cumlitivly get totals per month of persons hours and cost grouped by organization....
I have a table: tbl_Person with every employee and has their Organization
stored in there as a string....(may not be best normalized but have reasons why)..
I have a module that will get a single person's hours for a given month
and another module that willt ake those hours and get their cost for
same month.
I figure if I get a DISTINCT list of Organizations and then loop that around the Person table ... calling the Hours and Rate modules... I can get total hours and cost per month groupe by Organization...
Here is the code I started..... not even sure how to create or add to an array in Access... any help would be appreciated!!!
--------------------------------------------------------------------
Option Explicit
'Get Total running Hours for currentHours and nextHours
Public mPersonID As Integer
'Get Array of Person ID's grouped by Organization'
Public Function ResourceForecast() As Integer
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rsResourceData As ADODB.Recordset
Dim sConnString As String
Dim iCtr As Integer
Dim iOrg As String
sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentProject.Path & "\Blue&White.mdb;"
conn.Open sConnString
Set cmd.ActiveConnection = conn
cmd.CommandText = "SELECT DISTINCT Organization FROM tbl_Person"
cmd.CommandType = adCmdText
Set rsResourceData = cmd.Execute
'Outside Loop - get Distinct list of Orgaization's
Do While Not rsResourceData.EOF
For iCtr = 0 To rsResourceData.Fields.Count - 1
'OutPuts Name and Value of each field
Debug.Print rsResourceData.Fields(iCtr).Name & ": " & _
rsResourceData.Fields(iCtr).Value
'** CREATE An Array called iOrg
iOrg = rsResourceData!Organization
Next
'Inside Loop - get/add to person hours and cost modular variable
'** Use this new array to loop over persons while calling the other 2 functions 1) PersonHrs(PersonID, StartDate, EndDate) and 2) PersonCost(PersonID, PersonHrs, StartDate, EndDate) to get my data
Loop
End Function
I have a report with a need to populate a grid of information using 2 other modules I've written....
Goal: I need to cumlitivly get totals per month of persons hours and cost grouped by organization....
I have a table: tbl_Person with every employee and has their Organization
stored in there as a string....(may not be best normalized but have reasons why)..
I have a module that will get a single person's hours for a given month
and another module that willt ake those hours and get their cost for
same month.
I figure if I get a DISTINCT list of Organizations and then loop that around the Person table ... calling the Hours and Rate modules... I can get total hours and cost per month groupe by Organization...
Here is the code I started..... not even sure how to create or add to an array in Access... any help would be appreciated!!!
--------------------------------------------------------------------
Option Explicit
'Get Total running Hours for currentHours and nextHours
Public mPersonID As Integer
'Get Array of Person ID's grouped by Organization'
Public Function ResourceForecast() As Integer
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rsResourceData As ADODB.Recordset
Dim sConnString As String
Dim iCtr As Integer
Dim iOrg As String
sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentProject.Path & "\Blue&White.mdb;"
conn.Open sConnString
Set cmd.ActiveConnection = conn
cmd.CommandText = "SELECT DISTINCT Organization FROM tbl_Person"
cmd.CommandType = adCmdText
Set rsResourceData = cmd.Execute
'Outside Loop - get Distinct list of Orgaization's
Do While Not rsResourceData.EOF
For iCtr = 0 To rsResourceData.Fields.Count - 1
'OutPuts Name and Value of each field
Debug.Print rsResourceData.Fields(iCtr).Name & ": " & _
rsResourceData.Fields(iCtr).Value
'** CREATE An Array called iOrg
iOrg = rsResourceData!Organization
Next
'Inside Loop - get/add to person hours and cost modular variable
'** Use this new array to loop over persons while calling the other 2 functions 1) PersonHrs(PersonID, StartDate, EndDate) and 2) PersonCost(PersonID, PersonHrs, StartDate, EndDate) to get my data
Loop
End Function