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
