Collection.Count returns 0!
Ack.
I've used a nifty template I found on dev-archive.com to create a new collection class for (you guessed it) a group of records (data stored in object of type that i defined, another class) pulled from a database.
Here's the problem: I'm not getting any errors adding objects to the collection using the .Add objName, key, but checking the Count returns ZERO.
Here's the code for the collection class (aka, CapitalBudgetData.cls)
Option Explicit
Private m_PrivateCollection As Collection
Private Sub Class_Initialize()
' explicit assignment is slightly faster than auto-instancing
Set m_PrivateCollection = New Collection
End Sub
' Add a new CapitalBudget item to the collection
Public Sub Add(newItem As CapitalBudget, Optional Key As Variant)
' add to the private collection
MsgBox "adding a new item with key = " & Key
m_PrivateCollection.Add newItem, Key
Set Add = newItem
End Sub
' Remove an item from the collection
Public Sub Remove(index As Variant)
m_PrivateCollection.Remove index
End Sub
' Return a CapitalBudget item from the collection
Function Item(index As Variant) As CapitalBudget
Set Item = m_PrivateCollection.Item(index)
End Function
' Return the number of items in the collection
Property Get Count() As Long
Count = m_PrivateCollection.Count
End Property
' Remove all items from the collection
Public Sub Clear()
Set m_PrivateCollection = New Collection
End Sub
' Implement support for enumeration (For Each)
Function NewEnum() As IUnknown
' delegate to the private collection
Set NewEnum = m_PrivateCollection.[_NewEnum]
End Function
And this is how I'm adding objects to the collection:
Dim BudgetDataNextYear As CapitalBudgetData
Dim BudgetRecord As CapitalBudget
.
.
.
Set BudgetRecord = New CapitalBudget
BudgetDataNextYear.Add BudgetRecord, CVar(intNumRecords)
BudgetDataNextYear.Item(intNumRecords).NewRecord ( some data here =^D )
BudgetDataNextYear.Item(intNumRecords).CalcBudgetByMonth ( some more data here =^D )
.
.
.
' And later on ... this always returns zero
MsgBox "Count of collection = " & BudgetDataNextYear.Count
What's really bugging me (hah) is that I'm not getting any errors apart from an "'5', Invalid procedure call", which after googling for awhile leads me to believe relates to trying to access an item in the collection when there are no items IN the colleciton.
Anybody got any ideas on this one?
- ScaredFreakyGuy
# 3 Re: Collection.Count returns 0!
Thanks, Marco, for the tip on initializing the Collection class object. Unfortunately, no objects are being added to the collection. Totally weird.
Here's the code for the first part of the function that's handling the calculations and "adding" =^) the BudgetRecord objects into the collection:
Private Sub PrintCapitalBudget()
Dim fsoReport As New Scripting.FileSystemObject
Dim tsReport As TextStream
Dim tsHdrFtr As TextStream
Dim strRptHdrName As String
Dim strRptFtrName As String
Dim strReportName As String
Dim strCategory() As String
Dim strBgColor As String
Dim intBgCounter As Integer
Dim intRow, intCol, intRecNum As Integer
Dim intMonthIndex As Integer
Dim intStartMonth, intEndMonth, intTotalMonths As Integer
Dim intStartYear, intEndYear, intThisYear As Integer
Dim intNumDaysStToEnd, intNumDaysStToYearEnd As Integer
Dim intNumRecords(6, 2) As Integer
Dim intTotalAFEs As Integer
Dim lngBudgetByMonth(12) As Long
Dim lngSubTotal(6, 2, 12) As Long
Dim lngGrandTotal As Long
Dim dateStart, dateComplete As Date
Dim BudgetRecord As New CapitalBudget
Dim BudgetDataNextYear As New CapitalBudgetData
Dim BudgetDataCarryOver As New CapitalBudgetData
strRptHdrName = "include\report_CapitalBudget_header.html"
strRptFtrName = "include\report_CapitalBudget_footer.html"
strReportName = "Reports\2006CapitalBudget_" & Format(Now, "dd-MMM-yy") & ".html"
' Prepare the Capital Budget report file for writing
Set tsReport = fsoReport.CreateTextFile(strReportName, ForWriting)
' Write the header of the report by creating a new file
Set tsHdrFtr = fsoReport.OpenTextFile(strRptHdrName, ForReading)
Do Until tsHdrFtr.AtEndOfStream
tsReport.WriteLine tsHdrFtr.ReadLine
Loop
tsHdrFtr.Close
Set tsHdrFtr = Nothing
Set BudgetDataNextYear = New CapitalBudgetData
Set BudgetDataCarryOver = New CapitalBudgetData
' Calculate how much of each AFE is budgeted for each month of the next year, grouping by category
For intRow = 0 To 5
' Initialize the counters to zero
intNumRecords(intRow, 0) = 0
intNumRecords(intRow, 1) = 0
' Get the records for this category (intRow)
CurrentAFEsData.GetAFEsData rstAFEs, 1, (intRow + 1)
' Calculate the NEXT YEAR AFE records for this category, copying the pertinent info for
' the report into CapitalBudget objects and performing the necessary calculations
Do Until rstAFEs.EOF
For intMonthIndex = 0 To 12
lngBudgetByMonth(intMonthIndex) = 0
Next intMonthIndex
dateStart = rstAFEs("dateStart")
dateComplete = rstAFEs("dateComplete")
intStartMonth = Format(dateStart, "m")
intEndMonth = Format(dateComplete, "m")
intStartYear = Format(dateStart, "y")
intEndYear = Format(dateComplete, "y")
intThisYear = Year(Now)
intNumDaysStToEnd = DateDiff("d", dateStart, dateComplete)
intNumDaysStToYearEnd = DateDiff("d", dateStart, "31-Dec-" & Year(Now))
intTotalMonths = Format((intNumDaysStToEnd / 30), "#.#")
If intTotalMonths = 0 Then
intTotalMonths = 1
End If
Set BudgetRecord = New CapitalBudget
If intStartYear = intEndYear Then
If intStartYear = (Year(Now) + 1) Then
' Project starts next year and ends next year
BudgetDataNextYear.Add BudgetRecord, CVar(intNumRecords(intRow, 0))
BudgetDataNextYear.Item(intNumRecords(intRow, 0)).NewRecord Format(rstAFEs("afeID"), "000000") & "-" & Format(rstAFEs("pirID"), "000"), _
rstAFEs("description"), _
rstAFEs("totThisApprop"), _
lngBudgetByMonth()
BudgetDataNextYear.Item(intNumRecords(intRow, 0)).CalcBudgetByMonth intStartMonth, intEndMonth, intTotalMonths
intNumRecords(intRow, 0) = intNumRecords(intRow, 0) + 1
Else
' Project start/end dates are outside the scope of this report, so DON'T include it!
End If
End If
If intStartYear < intEndYear Then
If intStartYear = Year(Now) Then
If intEndYear = (Year(Now) + 1) Then
' Project starts this year and ends next year (exactly twelve months)
BudgetDataCarryOver.Add BudgetRecord
BudgetDataCarryOver.Item(intNumRecords(intRow, 1)).NewRecord Format(rstAFEs("afeID"), "000000") & "-" & Format(rstAFEs("pirID"), "000"), _
rstAFEs("description"), _
rstAFEs("totThisApprop"), _
lngBudgetByMonth()
BudgetDataCarryOver.Item(intNumRecords(intRow, 1)).CalcBudgetByMonth 1, intEndMonth, intTotalMonths
intNumRecords(intRow, 1) = intNumRecords(intRow, 1) + 1
MsgBox CStr(intNumRecords(intRow, 1))
ElseIf intEndYear < (Year(Now) + 1) Then
' Project starts this year and ends a year after the next
BudgetDataCarryOver.Add BudgetRecord
BudgetDataCarryOver.Item(intNumRecords(intRow, 1)).NewRecord Format(rstAFEs("afeID"), "000000") & "-" & Format(rstAFEs("pirID"), "000"), _
rstAFEs("description"), _
rstAFEs("totThisApprop"), _
lngBudgetByMonth()
BudgetDataCarryOver.Item(intNumRecords(intRow, 1)).CalcBudgetByMonth 1, 12, intTotalMonths
intNumRecords(intRow, 1) = intNumRecords(intRow, 1) + 1
End If
ElseIf intStartYear = (Year(Now) + 1) Then
' Project starts next year
BudgetDataNextYear.Add BudgetRecord
BudgetDataNextYear.Item(intNumRecords(intRow, 0)).NewRecord Format(rstAFEs("afeID"), "000000") & "-" & Format(rstAFEs("pirID"), "000"), _
rstAFEs("description"), _
rstAFEs("totThisApprop"), _
lngBudgetByMonth()
BudgetDataNextYear.Item(intNumRecords(intRow, 0)).CalcBudgetByMonth intStartMonth, 12, intTotalMonths
intNumRecords(intRow, 0) = intNumRecords(intRow, 0) + 1
Else
' Project start/end dates are outside the scope of this report, so DON'T include it!
End If
End If
rstAFEs.MoveNext
Loop
Next intRow
I'm googling like a madman (muahahah) but no luck so far, though I've learned alot of things about VB that i never knew.
- ScaredFreakyGuy
