VB Module 101 ?...

[rdjohnso]

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

 

[Pat Hartman]

You are over thinking this. No code is required. Do everything with a report based on a totals query. Build a query that sums the data by person then try using the report wizard to build a report from that query and do further summarization by organization. You can pretty it up later.

Access is a RAD (rapid application development) tool. Long live Access!!! Down with code

 

[rdjohnso]

Pat,

I wish thaT were true, but its alot more complex that just a simple query.... have to bounce against a DeltaDays() function to determine total working days within a month.. then determine total hours and cost based upon a person's Allocation and Rate given the time span they are alloted at that rate and/or allocation.

Thanks anyways,

Ron

You are over thinking this. No code is required. Do everything with a report based on a totals query. Build a query that sums the data by person then try using the report wizard to build a report from that query and do further summarization by organization. You can pretty it up later.

Access is a RAD (rapid application development) tool. Long live Access!!! Down with code

 

[Pat Hartman]

So far you haven't mentioned anything that can't be done in a query. It might make sense to do some things in the report to minimize the complexity of the query. You can do calculations in the report's Format event.