|
楼主 |
发表于 2011-7-1 13:16
|
显示全部楼层
回复 7楼 wqfzqgk 的帖子
Private Sub CommandButton1_Click()
'If Len(ComboBox1.Text) = 0 Or Len(ComboBox2.Text) = 0 Then
'MsgBox "起始或结束日期不能为空!"
' Exit Sub
'End If
Me.ListView1.ListItems.Clear
Set CNN = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
CNN.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName
'Sql = "select 日期,凭证号,摘要,科目代码,总账科目,一级明细科目,二级明细科目,借方,贷方 from [凭证列表$] where 日期>=#" & ComboBox1.Text & "# and 日期<=#" & ComboBox2.Text & "#"
Sql = "select 日期,凭证号,摘要,科目代码,总账科目,一级明细科目,二级明细科目,借方,贷方 from [凭证列表$] where 日期>=#" & DTPicker1.Value & "# and 日期<=#" & DTPicker2.Value & "#"
If ComboBox3.Text <> "" Then Sql = Sql & "and 凭证号 like '%" & ComboBox3.Text & "%'"
If ComboBox4.Text <> "" Then Sql = Sql & "and 摘要 like '%" & ComboBox4.Text & "%'"
If ComboBox5.Text <> "" Then Sql = Sql & "and 总账科目 like '%" & ComboBox5.Text & "%'"
If ComboBox6.Text <> "" Then Sql = Sql & "and 总账科目 like '%" & ComboBox6.Text & "%'"
If ComboBox7.Text <> "" Then Sql = Sql & "and 一级明细科目 like '%" & ComboBox7.Text & "%'"
If ComboBox8.Text <> "" Then Sql = Sql & "and 二级明细科目 like '%" & ComboBox8.Text & "%'"
rst.Open Sql, CNN, adOpenKeyset, adLockPessimistic
Do Until rst.EOF
icount = rst.RecordCount
For i = 1 To icount
ListView1.ListItems.Add , , rst.Fields("日期")
ListView1.ListItems(i).SubItems(1) = IIf(IsNull(rst.Fields("凭证号")), "", rst.Fields("凭证号"))
ListView1.ListItems(i).SubItems(2) = IIf(IsNull(rst.Fields("摘要")), "", rst.Fields("摘要"))
ListView1.ListItems(i).SubItems(3) = IIf(IsNull(rst.Fields("科目代码")), "", rst.Fields("科目代码"))
ListView1.ListItems(i).SubItems(4) = IIf(IsNull(rst.Fields("总账科目")), "", rst.Fields("总账科目"))
ListView1.ListItems(i).SubItems(5) = IIf(IsNull(rst.Fields("一级明细科目")), "", rst.Fields("一级明细科目"))
ListView1.ListItems(i).SubItems(6) = IIf(IsNull(rst.Fields("二级明细科目")), "", rst.Fields("二级明细科目"))
ListView1.ListItems(i).SubItems(7) = IIf(IsNull(rst.Fields("借方")), "", rst.Fields("借方"))
ListView1.ListItems(i).SubItems(8) = IIf(IsNull(rst.Fields("贷方")), "", rst.Fields("贷方"))
rst.MoveNext
Next i
Loop
Set rst = Nothing
Set CNN = Nothing
End Sub
Private Sub UserForm_Initialize()
DTPicker1.Value = Date
DTPicker2.Value = Date
ListView1.ColumnHeaders.Clear
ListView1.ListItems.Clear
ListView1.View = lvwReport
ListView1.FullRowSelect = True
ListView1.Gridlines = True
Dim h As New Collection
Dim k As New Collection
Dim i%, j%
On Error Resume Next
For j = 3 To 8
For i = 2 To Sheet7.[a65536].End(xlUp).Row
h.Add Sheet7.Cells(i, j - 1), CStr(Sheet7.Cells(i, j - 1))
If Err.Number = 0 Then
Me.Controls("ComboBox" & j).AddItem Sheet7.Cells(i, j - 1)
End If
Err.Clear
Next
Next
'For i = 2 To Sheet7.[a65536].End(xlUp).Row
' k.Add Sheet7.Cells(i, "a"), CStr(Sheet7.Cells(i, "a"))
' If Err.Number = 0 Then
'ComboBox1.AddItem Sheet7.Cells(i, "a")
'ComboBox2.AddItem Sheet7.Cells(i, "a")
'End If
'Err.Clear
'Next
ListView1.ColumnHeaders.Add , , "日期", 60
ListView1.ColumnHeaders.Add , , "凭证号", 40
ListView1.ColumnHeaders.Add , , "摘要", 150
ListView1.ColumnHeaders.Add , , "科目代码", 70
ListView1.ColumnHeaders.Add , , "总账科目", 120
ListView1.ColumnHeaders.Add , , "一级明细科目", 120
ListView1.ColumnHeaders.Add , , "二级明细科目", 30
ListView1.ColumnHeaders.Add , , "借方", 100
ListView1.ColumnHeaders.Add , , "贷方", 100
End Sub
请问:怎么加,我太笨了,不会,加了一下,rst.Open Sql, CNN, adOpenKeyset, adLockPessimistic不执行,谢谢 |
|