Option Explicit
Dim sql, tj As String, rnum As Double, r As Integer
Private Sub CommandButton1_Click()
'刷新库存
On Error Resume Next
Dim rowcount As Double
rowcount = ActiveSheet.UsedRange.Rows.Count
If rowcount > 4 Then
ActiveSheet.Range("5:" + CStr(rowcount)).EntireRow.Delete
End If
Select Case Sheet1.Range("G1")
Case "全部"
tj = ""
Case "大于0"
tj = "and T0.[CQTY]>0"
Case "等于0"
tj = "and T0.[CQTY]=0"
Case "小于0"
tj = "and T0.[CQTY]<0"
End Select
Call OpenCn
Application.StatusBar = "正在访问数据库,请稍等...."
sql = "select t2.name,t1.name_template,t3.material,t3.cust_spec,sum(qty) from stock_quant t0 left join product_product t1 on t1.id=t0.product_id left join product_template t3 on t3.id=t1.product_tmpl_id" _
& " left join stock_location t2 on t2.id=t0.location_id where t2.name='" + Trim(Sheet1.Range("E1")) + "' group by t1.name_template,t3.material,t3.cust_spec,t2.name;"
Sheet3.Range("A1") = sql
rs.CursorLocation = adUseClient
rs.Open sql, cn, 1, 1
rnum = rs.RecordCount
If rnum < 1 Then
MsgBox "没有记录,请您确认条件后查询! 谢谢!"
Else
Sheet1.Range("A5").CopyFromRecordset rs
Call Style(rnum)
End If
Call CloseCn
r = 5
'MsgBox Sheet1.Cells(r, 7)
While Len(Sheet1.Cells(r, 7)) > 3
If Len(Sheet1.Cells(r, 7)) = 6 Then
Sheet1.Cells(r, 7).Interior.ColorIndex = 3
ElseIf (Len(Sheet1.Cells(r, 7)) = 10) Then
Sheet1.Cells(r, 7).Interior.ColorIndex = 6
End If
r = r + 1
Wend
Application.StatusBar = "查询完成,共计:" + CStr(rnum) + "条记录"
End Sub