楼主
xmyjk |
Q:
1.如何把图示的内容经筛选后显示到窗体中去,可按具体的”采购订单“或”物料编号“或”下单日期“或”厂商代号“来点“Query”按钮就会在窗体中出现以下第2点的内容
2. 要在窗体中显示13个字段的内容（“订单号码”、“订单项次”、“物料编号”、“订单数量”、“单位”、“订单单价”、“币别”、“参考单号”、“要求交期”、“下单日期”、“在外量”、“采购金额”、“厂商代号”）
3.
点“Delete"按钮就可以删除所选中的内容（当然也要包括也删除对应工作表中的那一行）
A:查询运用了SQL，窗体使用了TREEVIEW，使用方法如下：
- Private Sub cmdClose_Click()
- Unload Me
- End Sub
- Private Sub cmdDelete_Click()
- Dim i&
- Application.ScreenUpdating = False
- line1:
- For i = 1 To ListView1.ListItems.Count
- If ListView1.ListItems(i).Checked = True Then
- Sheets("采购订单状况").Range([Y2], [Y65536].End(3)).Find(ListView1.ListItems(i).ListSubItems(13).Text, , xlValues, xlWhole).EntireRow.Delete
- ListView1.ListItems.Remove i
- GoTo line1
- End If
- Next
- Application.ScreenUpdating = True
- End Sub
- Private Sub cmdQuery_Click()
- Dim cnn, rcd, arr, sql As String, i&, j&, tmp, cxh
- ListView1.ListItems.Clear
- With Sheets("采购订单状况")
- Set cnn = CreateObject("ADODB.Connection")
- Set rcd = CreateObject("ADODB.recordset")
- cnn.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;imex=1';data source=" & ThisWorkbook.FullName
- Select Case cboxCondition.Text
- Case "订单号码": cxh = """" & UCase(txtCondition.Text) & """"
- Case "物料编号": cxh = """" & UCase(txtCondition.Text) & """"
- Case "下单日期": cxh = "#" & Format(txtCondition.Text, "yyyy-mm-dd") & "#"
- Case "厂商代号": cxh = """" & UCase(txtCondition.Text) & """"
- End Select
- If txtCondition.Text = "" Then
- sql = "Select 订单号码,订单项次,物料编号,订单数量,单位,订单单价,币别,参考单号,要求交期,下单日期,在外量,采购金额,厂商代号,序号 From [采购订单状况$]"
- Else
- sql = "Select 订单号码,订单项次,物料编号,订单数量,单位,订单单价,币别,参考单号,要求交期,下单日期,在外量,采购金额,厂商代号,序号 From [采购订单状况$] WHERE " & cboxCondition.Text & "=" & cxh
- End If
- rcd.Open sql, cnn
- On Error Resume Next
- arr = rcd.getrows()
- rcd.Close: Set rcd = Nothing
- cnn.Close: Set cnn = Nothing
-
- If IsArray(arr) Then
- For i = 0 To UBound(arr, 2)
- Set Itm = ListView1.ListItems.Add()
- Itm.Text = arr(0, i)
- For j = 1 To UBound(arr, 1)
- If IsNull(arr(j, i)) Then
- Itm.SubItems(j) = ""
- Else
- Itm.SubItems(j) = arr(j, i)
- End If
- Next
- Next
- Else
- MsgBox "无相关记录！"
- End If
- Erase arr
- End With
- End Sub
- Private Sub UserForm_Initialize()
- Dim Itm As ListItem
- ListView1.ColumnHeaders.Clear
- ListView1.ListItems.Clear
- With ListView1
- .ColumnHeaders.Add 1, , "订单号码", .Width / 14
- .ColumnHeaders.Add 2, , "订单项次", .Width / 14, lvwColumnCenter
- .ColumnHeaders.Add 3, , "物料编号", .Width / 14, lvwColumnCenter
- .ColumnHeaders.Add 4, , "订单数量", .Width / 14, lvwColumnCenter
- .ColumnHeaders.Add 5, , "单位", .Width / 14, lvwColumnCenter
- .ColumnHeaders.Add 6, , "订单单价", .Width / 14, lvwColumnCenter
- .ColumnHeaders.Add 7, , "币别", .Width / 14, lvwColumnCenter
- .ColumnHeaders.Add 8, , "参考单号", .Width / 14, lvwColumnCenter
- .ColumnHeaders.Add 9, , "要求交期", .Width / 14, lvwColumnCenter
- .ColumnHeaders.Add 10, , "下单日期", .Width / 14, lvwColumnCenter
- .ColumnHeaders.Add 11, , "在外量", .Width / 14, lvwColumnCenter
- .ColumnHeaders.Add 12, , "采购金额", .Width / 14, lvwColumnCenter
- .ColumnHeaders.Add 13, , "厂商代号", .Width / 14, lvwColumnCenter
- .ColumnHeaders.Add 14, , "序号", .Width / 14, lvwColumnCenter
-
- .View = lvwReport
- .Gridlines = True
- .FullRowSelect = True
- .CheckBoxes = True
- End With
-
- cboxCondition.AddItem "订单号码"
- cboxCondition.AddItem "物料编号"
- cboxCondition.AddItem "下单日期"
- cboxCondition.AddItem "厂商代号"
-
- txtCondition.SetFocus
- End Sub
- Private Sub CommandButton1_Click()
- [y1] = "序号": [Y2] = 1
- [Y2].AutoFill Destination:=Range([Y2], [X65536].End(3).Offset(0, 1)), Type:=xlFillSeries
- OrderQueryDelete.Show
- End Sub
 VBA中查询与删除代码.rar
|
