'在线获得显示报表时SN的相关信息
Public Sub Set_SNInfoOnline()
'On Error Resume Next Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim SeqTotalNum As Integer
Dim TmpColl As New Collection
Dim tmpresult As New TestResultClass
'SN Info
SelSN.Num = 1
ReDim SelSN.SNInfo(0 To SelSN.Num - 1, 0 To 2)
For i = 0 To SelSN.Num - 1
SelSN.SNInfo(i, 0) = FrmTPExecute.LabUUT(0).Caption
SelSN.SNInfo(i, 1) = TPInformation.TPId
SelSN.SNInfo(i, 2) = 0 '不需要testid
Next i 'Seq Info
ReDim SelSN.SNSeqInfo(0 To SelSN.Num - 1)
For i = 0 To SelSN.Num - 1
SeqTotalNum = UBound(TIListArr)
If SeqTotalNum >= 1 Then
SelSN.SNSeqInfo(i).SeqNum = SeqTotalNum
ReDim SelSN.SNSeqInfo(i).SeqName(0 To SeqTotalNum - 1)
ReDim SelSN.SNSeqInfo(i).SeqState(0 To SeqTotalNum - 1)
ReDim SelSN.SNSeqInfo(i).SeqKind(0 To SeqTotalNum - 1)
ReDim SelSN.SNSeqInfo(i).SeqResult(0 To SeqTotalNum - 1) Dim Rs As New ADODB.Recordset, SqlStr$
Rs.CursorLocation = adUseClient
SqlStr = "select a.type(case a.type when 'PRE' then 0 when 'UUT' then 1 else 2 end) as typeid,a.seq,a.TIName,a.extName,a.PassGo,a.FailGo,a.Failretry,a.LoopTo,a.loopcycle " _
& " ,b.RelatedDevices,b.closehd,a.Report " _
& " from TP_TestItem a,TI_TestItemList b " _
& " where a.tpid=" & SelSN.SNInfo(i, 1) & " and a.active<>0 " _
& " and a.TIName=b.TIName " _
& " order by a.typeid,a.seq "
Rs.Open SqlStr, ConSys, adOpenDynamic '(实时错误‘-2147467259(80004005)’对象‘open’的方法‘_Recordset’失败)
'add SeqName SeqState
For j = 0 To Rs.RecordCount - 1
SelSN.SNSeqInfo(i).SeqName(j) = Rs.Fields(3).Value
Rs.MoveNext
Next j
Rs.Close
'add SeqState
For j = 0 To SeqTotalNum - 1
SelSN.SNSeqInfo(i).SeqState(j) = TIListArr(j + 1).isReport
Next j
'add SeqKind
For j = 0 To SelSN.SNSeqInfo(i).SeqNum - 1
SqlStr = "Select Type from TI_TestItemList where TIName='" _
& SelSN.SNSeqInfo(i).SeqName(j) & "'"
Rs.Open SqlStr, ConSys, adOpenDynamic (这里不会出错)
SelSN.SNSeqInfo(i).SeqKind(j) = Rs.Fields(0).Value
If SelSN.SNSeqInfo(i).SeqKind(j) = "00" Or SelSN.SNSeqInfo(i).SeqKind(j) = "10" Then
SelSN.SNSeqInfo(i).PreSeqCount = SelSN.SNSeqInfo(i).PreSeqCount + 1
ElseIf SelSN.SNSeqInfo(i).SeqKind(j) = "02" Or SelSN.SNSeqInfo(i).SeqKind(j) = "12" Then
SelSN.SNSeqInfo(i).PosSeqCount = SelSN.SNSeqInfo(i).PosSeqCount + 1
End If
Rs.Close
Next j
'add SeqKind
For j = 0 To SelSN.SNSeqInfo(i).SeqNum - 1
If TIListArr(j + 1).TiType = 0 Then
SelSN.SNSeqInfo(i).SeqKind(j) = "00"
ElseIf TIListArr(j + 1).TiType = 1 Then
SelSN.SNSeqInfo(i).SeqKind(j) = "01"
Else
SelSN.SNSeqInfo(i).SeqKind(j) = "02"
End If
If SelSN.SNSeqInfo(i).SeqKind(j) = "00" Or SelSN.SNSeqInfo(i).SeqKind(j) = "10" Then
SelSN.SNSeqInfo(i).PreSeqCount = SelSN.SNSeqInfo(i).PreSeqCount + 1
ElseIf SelSN.SNSeqInfo(i).SeqKind(j) = "02" Or SelSN.SNSeqInfo(i).SeqKind(j) = "12" Then
SelSN.SNSeqInfo(i).PosSeqCount = SelSN.SNSeqInfo(i).PosSeqCount + 1
End If
Next j 'add seq result
For j = 0 To SelSN.SNSeqInfo(i).SeqNum - 1
' Set TmpColl = New Collection
' Set TmpResult = New TestResultClass
Set TmpColl = VarCollect("TestResult" & CStr(j + 1))
Set tmpresult = TmpColl("TestResult-0")
If tmpresult.Value = 0 Then
SelSN.SNSeqInfo(i).SeqResult(j) = False
Else
SelSN.SNSeqInfo(i).SeqResult(j) = True
End If
Next j
Else
SelSN.SNSeqInfo(i).SeqNum = 0
End If
Next iEnd Sub
Public Sub Set_SNInfoOnline()
'On Error Resume Next Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim SeqTotalNum As Integer
Dim TmpColl As New Collection
Dim tmpresult As New TestResultClass
'SN Info
SelSN.Num = 1
ReDim SelSN.SNInfo(0 To SelSN.Num - 1, 0 To 2)
For i = 0 To SelSN.Num - 1
SelSN.SNInfo(i, 0) = FrmTPExecute.LabUUT(0).Caption
SelSN.SNInfo(i, 1) = TPInformation.TPId
SelSN.SNInfo(i, 2) = 0 '不需要testid
Next i 'Seq Info
ReDim SelSN.SNSeqInfo(0 To SelSN.Num - 1)
For i = 0 To SelSN.Num - 1
SeqTotalNum = UBound(TIListArr)
If SeqTotalNum >= 1 Then
SelSN.SNSeqInfo(i).SeqNum = SeqTotalNum
ReDim SelSN.SNSeqInfo(i).SeqName(0 To SeqTotalNum - 1)
ReDim SelSN.SNSeqInfo(i).SeqState(0 To SeqTotalNum - 1)
ReDim SelSN.SNSeqInfo(i).SeqKind(0 To SeqTotalNum - 1)
ReDim SelSN.SNSeqInfo(i).SeqResult(0 To SeqTotalNum - 1) Dim Rs As New ADODB.Recordset, SqlStr$
Rs.CursorLocation = adUseClient
SqlStr = "select a.type(case a.type when 'PRE' then 0 when 'UUT' then 1 else 2 end) as typeid,a.seq,a.TIName,a.extName,a.PassGo,a.FailGo,a.Failretry,a.LoopTo,a.loopcycle " _
& " ,b.RelatedDevices,b.closehd,a.Report " _
& " from TP_TestItem a,TI_TestItemList b " _
& " where a.tpid=" & SelSN.SNInfo(i, 1) & " and a.active<>0 " _
& " and a.TIName=b.TIName " _
& " order by a.typeid,a.seq "
Rs.Open SqlStr, ConSys, adOpenDynamic '(实时错误‘-2147467259(80004005)’对象‘open’的方法‘_Recordset’失败)
'add SeqName SeqState
For j = 0 To Rs.RecordCount - 1
SelSN.SNSeqInfo(i).SeqName(j) = Rs.Fields(3).Value
Rs.MoveNext
Next j
Rs.Close
'add SeqState
For j = 0 To SeqTotalNum - 1
SelSN.SNSeqInfo(i).SeqState(j) = TIListArr(j + 1).isReport
Next j
'add SeqKind
For j = 0 To SelSN.SNSeqInfo(i).SeqNum - 1
SqlStr = "Select Type from TI_TestItemList where TIName='" _
& SelSN.SNSeqInfo(i).SeqName(j) & "'"
Rs.Open SqlStr, ConSys, adOpenDynamic (这里不会出错)
SelSN.SNSeqInfo(i).SeqKind(j) = Rs.Fields(0).Value
If SelSN.SNSeqInfo(i).SeqKind(j) = "00" Or SelSN.SNSeqInfo(i).SeqKind(j) = "10" Then
SelSN.SNSeqInfo(i).PreSeqCount = SelSN.SNSeqInfo(i).PreSeqCount + 1
ElseIf SelSN.SNSeqInfo(i).SeqKind(j) = "02" Or SelSN.SNSeqInfo(i).SeqKind(j) = "12" Then
SelSN.SNSeqInfo(i).PosSeqCount = SelSN.SNSeqInfo(i).PosSeqCount + 1
End If
Rs.Close
Next j
'add SeqKind
For j = 0 To SelSN.SNSeqInfo(i).SeqNum - 1
If TIListArr(j + 1).TiType = 0 Then
SelSN.SNSeqInfo(i).SeqKind(j) = "00"
ElseIf TIListArr(j + 1).TiType = 1 Then
SelSN.SNSeqInfo(i).SeqKind(j) = "01"
Else
SelSN.SNSeqInfo(i).SeqKind(j) = "02"
End If
If SelSN.SNSeqInfo(i).SeqKind(j) = "00" Or SelSN.SNSeqInfo(i).SeqKind(j) = "10" Then
SelSN.SNSeqInfo(i).PreSeqCount = SelSN.SNSeqInfo(i).PreSeqCount + 1
ElseIf SelSN.SNSeqInfo(i).SeqKind(j) = "02" Or SelSN.SNSeqInfo(i).SeqKind(j) = "12" Then
SelSN.SNSeqInfo(i).PosSeqCount = SelSN.SNSeqInfo(i).PosSeqCount + 1
End If
Next j 'add seq result
For j = 0 To SelSN.SNSeqInfo(i).SeqNum - 1
' Set TmpColl = New Collection
' Set TmpResult = New TestResultClass
Set TmpColl = VarCollect("TestResult" & CStr(j + 1))
Set tmpresult = TmpColl("TestResult-0")
If tmpresult.Value = 0 Then
SelSN.SNSeqInfo(i).SeqResult(j) = False
Else
SelSN.SNSeqInfo(i).SeqResult(j) = True
End If
Next j
Else
SelSN.SNSeqInfo(i).SeqNum = 0
End If
Next iEnd Sub
& " ,b.RelatedDevices,b.closehd,a.Report " _
& " from TP_TestItem a,TI_TestItemList b " _
& " where a.tpid=" & SelSN.SNInfo(i, 1) & " and a.active<>0 " _
& " and a.TIName=b.TIName " _
& " order by a.typeid,a.seq "
SqlStr = "select IIf(a.type = 'PRE', 0, IIf(a.type= 'UUT', 1, 2)) as typeid ...
调试时出现: 实时错误 '-2147217904' (80040E10)
至少一个参数没有被指定值.
SqlStr = SqlStr & "select IIf(a.type = 'PRE', 0, IIf(a.type= 'UUT', 1, 2)) as typeid " & vbNewLine
SqlStr = SqlStr & " ,a.seq " & vbNewLine
SqlStr = SqlStr & " ,a.TIName " & vbNewLine
SqlStr = SqlStr & " ,a.extName " & vbNewLine
SqlStr = SqlStr & " ,a.PassGo " & vbNewLine
SqlStr = SqlStr & " ,a.FailGo " & vbNewLine
SqlStr = SqlStr & " ,a.Failretry " & vbNewLine
SqlStr = SqlStr & " ,a.LoopTo " & vbNewLine
SqlStr = SqlStr & " ,a.loopcycle " & vbNewLine
SqlStr = SqlStr & " ,b.RelatedDevices " & vbNewLine
SqlStr = SqlStr & " ,b.closehd " & vbNewLine
SqlStr = SqlStr & " ,a.Report " & vbNewLine
SqlStr = SqlStr & " from TP_TestItem a " & vbNewLine
SqlStr = SqlStr & " ,TI_TestItemList b " & vbNewLine
SqlStr = SqlStr & " where a.tpid=" & SelSN.SNInfo(i, 1) & " " & vbNewLine
SqlStr = SqlStr & " and a.active<>0 " & vbNewLine
SqlStr = SqlStr & " and a.TIName=b.TIName " & vbNewLine
SqlStr = SqlStr & " order by a.typeid,a.seq "
我的ACCESS的两个表的字段如下:
TP_TestItem表:
tpid,type,seq,active,TIName,extName,Label,PassGo,FailGo,FailRetry,LoopTo,Loopcycle,Report
TI_TestItemList表:
Type,id,TIName,Active,Comment,Release,Datetime,RelateDevices,closehd求高手指点下面这句怎么修改,实在没法完成,很急,谢谢了!
SqlStr = "select a.type(IIf(a.type = 'PRE', 0, IIf(a.type= 'UUT', 1, 2))) as typeid,a.seq,a.TIName,a.extName,a.PassGo,a.FailGo,a.Failretry,a.LoopTo,a.loopcycle " _
& " ,b.RelatedDevices,b.closehd,a.Report " _
& " from TP_TestItem a,TI_TestItemList b " _
& " where a.tpid=" & SelSN.SNInfo(i, 1) & " and a.active<>0 " _
& " and a.TIName=b.TIName " _
& " order by a.typeid,a.seq "
比如type,处理如下SqlStr = ""
SqlStr = SqlStr & "select IIf(a.[type] = 'PRE', 0, IIf(a.[type]= 'UUT', 1, 2)) as typeid " & vbNewLine
SqlStr = SqlStr & " ,a.seq " & vbNewLine
SqlStr = SqlStr & " ,a.TIName " & vbNewLine
SqlStr = SqlStr & " ,a.extName " & vbNewLine
SqlStr = SqlStr & " ,a.PassGo " & vbNewLine
SqlStr = SqlStr & " ,a.FailGo " & vbNewLine
SqlStr = SqlStr & " ,a.Failretry " & vbNewLine
SqlStr = SqlStr & " ,a.LoopTo " & vbNewLine
SqlStr = SqlStr & " ,a.loopcycle " & vbNewLine
SqlStr = SqlStr & " ,b.RelatedDevices " & vbNewLine
SqlStr = SqlStr & " ,b.closehd " & vbNewLine
SqlStr = SqlStr & " ,a.Report " & vbNewLine
SqlStr = SqlStr & " from TP_TestItem a " & vbNewLine
SqlStr = SqlStr & " ,TI_TestItemList b " & vbNewLine
SqlStr = SqlStr & " where a.tpid=" & SelSN.SNInfo(i, 1) & " " & vbNewLine
SqlStr = SqlStr & " and a.active<>0 " & vbNewLine
SqlStr = SqlStr & " and a.TIName=b.TIName " & vbNewLine
SqlStr = SqlStr & " order by a.typeid,a.seq "
SqlStr = "select a.type,iif(a.type='PRE',0,iif(a.type='UUT',1, 2)) as typeid," _
& "a.seq,a.tiname,a.extname,a.passgo,a.failgo,a.failretry,a.loopto,a.loopcycle " _
& " ,b.relateddevices,b.closehd,a.report " _
& " from tp_testitem a,TI_TestItemList b " _
& " where a.tpid=" & SelSN.SNInfo(i, 1) & " and a.active<>0 " _
& " and a.tiname=b.tiname " _
& " order by iif(a.type='PRE',0,iif(a.type='UUT',1, 2)),a.seq "