|
Private Sub ComboBox1_Change()
Dim mSQL$
Dim Conn, RST
Dim y&, x&
On Error Resume Next
x = Sheet2.[a65536].End(xlUp).Row
If ComboBox1.Text <> "" Then mSQL = "select 日期,客户,货号,名称及规格,颜色,单位,单价 from[数据明细$a2:l" & x & "] where 客户 = '" & ComboBox1.Text & "' "
If mSQL = "" Then Exit Sub
Set RST = CreateObject("Adodb.Recordset")
Set Conn = CreateObject("adodb.connection")
Conn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName
RST.Open mSQL, Conn, 1, 1
ListView1.ListItems.Clear
For y = 1 To RST.RecordCount
Me.ListView1.ListItems.Add , , RST("日期")
Me.ListView1.ListItems(y).SubItems(1) = RST("客户")
Me.ListView1.ListItems(y).SubItems(2) = RST("货号")
Me.ListView1.ListItems(y).SubItems(3) = RST("名称及规格")
Me.ListView1.ListItems(y).SubItems(4) = RST("颜色")
Me.ListView1.ListItems(y).SubItems(5) = RST("单位")
Me.ListView1.ListItems(y).SubItems(6) = RST("单价")
RST.MoveNext
Next
RST.Close: Conn.Close
Set RST = Nothing: Set Conn = Nothing
End Sub
Private Sub UserForm_Initialize()
Dim i&
With ListView1
.ColumnHeaders.Add , , " 日期", Width / 8
.ColumnHeaders.Add , , "客户", Width / 9, lvwColumnCenter
.ColumnHeaders.Add , , "货号", Width / 10, lvwColumnCenter
.ColumnHeaders.Add , , "名称及规格", Width / 5, lvwColumnCenter
.ColumnHeaders.Add , , "颜色", Width / 6, lvwColumnCenter
.ColumnHeaders.Add , , "单位", Width / 12, lvwColumnCenter
.ColumnHeaders.Add , , "单价", Width / 7, lvwColumnCenter
.View = lvwReport
.Sorted = True
.FullRowSelect = True
.Gridlines = True
End With
With ComboBox1
For i = 3 To Sheets("参数设置").Range("f65536").End(3).Row
.AddItem Sheets("参数设置").Cells(i, 6).Value
Next
End With
End Sub
|
|