[RESOLVED] Collection Problem.

[nUflAvOrS]

Private Sub Form_Load()
Dim rs As ADODB.Recordset, rs_1 As ADODB.Recordset
Dim pstrFIELD As String, pstrTABLE As String, pstrWHERE As String, pstrGROUP As String
Dim pstrWHEREAll As String
Dim Loc As String
Dim DeptCollection As Collection
Dim iCol As String, i As Integer
Dim strDept As String
Me.lvwShowDetail.Visible = True
Loc = Me.lvwShowBlock.SelectedItem.Text
pstrFIELD = " Loc,Count(Mac_Code) as CountMac," & _
            " (SELECT Count(Mac_Code) as CountMac" & _
            " From dbo.Machine_Desc" & _
            " WHERE (Status = 'Absolute Critical') AND Loc = '" & Loc & "' AND Condition = 'BreakDown'" & _
            " GROUP BY Loc) as CountAC," & _
            " (SELECT Count(Mac_Code) as CountMac" & _
            " From dbo.Machine_Desc" & _
            " WHERE (Status = 'Between Critical') AND Loc = '" & Loc & "' AND Condition = 'BreakDown'" & _
            " GROUP BY Loc) as CountBC," & _
            " (SELECT Count(Mac_Code) as CountMac" & _
            " From dbo.Machine_Desc" & _
            " WHERE (Status = 'Common Critical') AND Loc = '" & Loc & "' AND Condition = 'BreakDown'" & _
            " GROUP BY Loc) as CountCC"
pstrTABLE = "dbo.Machine_Desc"
pstrGROUP = " GROUP BY Loc"
pstrWHEREAll = " WHERE Status IN('Absolute Critical','Between Critical','Common Critical') AND Loc = '" & Loc & "' AND Condition = 'Breakdown'"
    Set rs_1 = SQLSELECT("Dept", pstrTABLE, pstrWHEREAll, " GROUP BY Dept", "", "Maintenance")
        Set DeptCollection = New Collection
            Do While Not rs_1.EOF
                 iCol = Val(iCol) + 1
 
                DeptCollection.Add IIf(IsNull(rs_1.Fields(0)), " ", rs_1.Fields(0)), iCol
                
                rs_1.MoveNext
            Loop
            On Error GoTo err:
            For i = 1 To DeptCollection.Count
            '***************** PRoblem is here *************************
                strDept = strDept & " " & DeptCollection.Item(i) 'Problem exist here
            '***************** PRoblem  *************************
            Next
            strDept = Replace(Trim(strDept), " ", ",")
    Set rs = SQLSELECT(pstrFIELD, pstrTABLE, pstrWHEREAll, pstrGROUP, "", "Maintenance")
    
    Call LoadItmxPreSumOfLoc(rs, strDept)
    
err:
    MsgBox err.Number
End Sub

I was facing a problem at the line of code i highlighted, when i debugging until the highlighted code the err.number show me 9. Furthermore, if i do not used error handle. The Project will close directly.

I'm not similar to use collection. Thus, the problem may happened in collection.

Thanks in millions for helps.

[MarkT]

Try changing the loop from
For i = 1 To DeptCollection.count

to
For i = 0 To DeptCollection.count - 1

[nUflAvOrS]

Thanks for your fast reply
The problem is same ...

[nUflAvOrS]

Problem resolved by set

DeptCollection.Add IIf(IsNull(rs_1.Fields(0)), " ", rs_1.Fields(0)), iCol

to

DeptCollection.Add cstr(rs_1.Fields(0)), iCol