📄 frmhuizong.frm
字号:
VERSION 5.00
Object = "{5E9E78A0-531B-11CF-91F6-C2863C385E30}#1.0#0"; "MSFLXGRD.OCX"
Begin VB.Form frmPzhz
Caption = "凭证汇总"
ClientHeight = 11115
ClientLeft = 2160
ClientTop = 1410
ClientWidth = 15240
Icon = "frmHuizong.frx":0000
LinkTopic = "Form1"
MaxButton = 0 'False
ScaleHeight = 11115
ScaleWidth = 15240
StartUpPosition = 2 '屏幕中心
WindowState = 2 'Maximized
Begin VB.ComboBox Combo1
Height = 300
Index = 1
Left = 7560
TabIndex = 9
Text = "请选择汇总月份"
Top = 2520
Width = 1935
End
Begin VB.ComboBox Combo1
Height = 300
Index = 0
Left = 4680
TabIndex = 3
Text = "请选择科目名称"
Top = 2520
Width = 2055
End
Begin VB.CommandButton Command3
Caption = "退出"
Height = 375
Left = 6000
TabIndex = 2
Top = 9120
Width = 1575
End
Begin MSFlexGridLib.MSFlexGrid MSFlexGrid1
Height = 3135
Left = 5280
TabIndex = 1
Top = 4800
Width = 5415
_ExtentX = 9551
_ExtentY = 5530
_Version = 393216
BackColorBkg = -2147483624
End
Begin VB.CommandButton Command1
Caption = "已审核汇总"
Height = 375
Index = 0
Left = 10320
TabIndex = 0
Top = 2520
Width = 1575
End
Begin VB.PictureBox Picture1
Height = 11175
Left = 0
Picture = "frmHuizong.frx":014A
ScaleHeight = 11115
ScaleWidth = 15195
TabIndex = 6
Top = 0
Width = 15255
Begin VB.TextBox Text1
Height = 375
Left = 10320
TabIndex = 13
Top = 1920
Visible = 0 'False
Width = 1575
End
Begin VB.CommandButton Command1
Caption = "未审核汇总"
Height = 375
Index = 1
Left = 10320
TabIndex = 12
Top = 3240
Width = 1575
End
Begin VB.ComboBox Combo1
Height = 300
Index = 3
Left = 7560
TabIndex = 11
Text = "请选择汇总月份"
Top = 3240
Width = 1935
End
Begin VB.ComboBox Combo1
Height = 300
Index = 2
Left = 4680
TabIndex = 10
Text = "请选择科目名称"
Top = 3240
Width = 2055
End
Begin VB.Frame Frame1
BackColor = &H80000018&
Caption = "查询结果"
ForeColor = &H00000000&
Height = 4455
Left = 3960
TabIndex = 7
Top = 4080
Width = 7815
End
Begin VB.Label Label3
BackStyle = 0 'Transparent
Caption = "凭证汇总"
BeginProperty Font
Name = "宋体"
Size = 15.75
Charset = 134
Weight = 700
Underline = 0 'False
Italic = 0 'False
Strikethrough = 0 'False
EndProperty
ForeColor = &H00FFFFFF&
Height = 495
Left = 600
TabIndex = 8
Top = 480
Width = 2415
End
End
Begin VB.Label Label2
AutoSize = -1 'True
Caption = "月份"
Height = 180
Left = 3360
TabIndex = 5
Top = 360
Width = 360
End
Begin VB.Label Label1
AutoSize = -1 'True
Caption = "科目名称"
Height = 180
Left = 960
TabIndex = 4
Top = 360
Width = 720
End
End
Attribute VB_Name = "frmPzhz"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit
Private Sub Command1_Click(Index As Integer)
'判断用户点击的是已审核汇总还是未审核汇总
Select Case Index
Case 0
Text1.Text = -1
Case 1
Text1.Text = 0
End Select
Dim strSQL, strRecord As String
'声明一记录集对象，并与ADOcn关联
Dim ADOrs As New Recordset
ADOrs.ActiveConnection = ADOcn
'拼写Select查询语句
'判断用户是否都选择了要查询的科目名称和汇总月份
If Combo1(0).Text = "请选择科目名称" Or Combo1(1).Text = "请选择汇总月份" Then
MsgBox "请选择科目名称和汇总月份"
Else
strSQL = "Select *"
strSQL = strSQL + " From pzls"
strSQL = strSQL + " where 审核标志=" + Text1.Text + " and 科目名称='" + Combo1(0) + "' and 日期 like'" + Left(Combo1(1).List(Combo1(1).ListIndex), 7) + "%'"
'执行Select语句
ADOrs.Open strSQL
'初始化MSFlexGrid表格
MSFlexGrid1.Clear '清空
MSFlexGrid1.Rows = 0 '行数
MSFlexGrid1.Cols = 4 '列数
MSFlexGrid1.colWidth(0) = MSFlexGrid1.Width / 4.3 '设置宽度
MSFlexGrid1.colWidth(1) = MSFlexGrid1.Width / 4
MSFlexGrid1.colWidth(2) = MSFlexGrid1.Width / 4
MSFlexGrid1.colWidth(3) = MSFlexGrid1.Width / 4
'设置表格标题栏
strRecord = " 科目代码" + vbTab + " 科目名称" + vbTab + " 借方金额" + vbTab + " 贷方金额"
MSFlexGrid1.AddItem strRecord
Dim m, n As String, c, d
'从第一条记录开始循环，直到表尾
Do While Not ADOrs.EOF
'汇总借方和贷方金额
m = ADOrs.Fields("科目代码")
n = ADOrs.Fields("科目名称")
c = c + Val(ADOrs("借方金额"))
d = d + Val(ADOrs("贷方金额"))
'取下一条记录
ADOrs.MoveNext
Loop
'判断有没有可汇总的数据
If m = "" Then
MsgBox "没有可汇总的数据"
Else
'向表格内添加行
MSFlexGrid1.AddItem m + vbTab + n + vbTab + Str(c) + vbTab + "0"
MSFlexGrid1.AddItem m + vbTab + n + vbTab + "0" + vbTab + Str(d)
End If
End If
End Sub
Private Sub Command3_Click()
Unload Me
End Sub
Private Sub Form_Load()
'初始化MSFlexGrid表格
MSFlexGrid1.Clear '清空
MSFlexGrid1.Rows = 0 '行数
MSFlexGrid1.Cols = 4 '列数
MSFlexGrid1.colWidth(0) = MSFlexGrid1.Width / 4.3 '设置宽度
MSFlexGrid1.colWidth(1) = MSFlexGrid1.Width / 4
MSFlexGrid1.colWidth(2) = MSFlexGrid1.Width / 4
MSFlexGrid1.colWidth(3) = MSFlexGrid1.Width / 4
'设置表格标题栏
MSFlexGrid1.AddItem " 科目代码" + vbTab + " 科目名称" + vbTab + " 借方金额" + vbTab + " 贷方金额"
'声明sql查询语句的字符串变量
Dim strSQL, strDate As String
Dim i, j, k As Integer
'声明一记录集对象，并与ADOcn关联
Dim ADOrs As New Recordset
ADOrs.ActiveConnection = ADOcn
'把sql查询语句附值给变量strSQL
strSQL = "Select distinct 科目名称"
strSQL = strSQL + " From pzls"
'按查询语句打开数据库
ADOrs.Open strSQL
'把数据表里的所有科目名称添加到combo1(0)和combo(2)里,年月份添加到combo1(1)和combo(3)里
Do While Not ADOrs.EOF
Combo1(0).AddItem ADOrs.Fields("科目名称")
Combo1(2).AddItem ADOrs.Fields("科目名称")
ADOrs.MoveNext '读取下一条记录
Loop
ADOrs.Close '关闭记录集对象
strSQL = "Select distinct 日期"
strSQL = strSQL + " From pzls order by 日期"
'按查询语句打开数据库
ADOrs.Open strSQL
'把数据表里的所有科目名称添加到combo1(0)和combo(2)里,年月份添加到combo1(1)和combo(3)里
Do While Not ADOrs.EOF
strDate = Year(ADOrs.Fields("日期")) & "-" & Month(ADOrs.Fields("日期"))
Combo1(1).AddItem strDate
Combo1(3).AddItem strDate
ADOrs.MoveNext '读取下一条记录
Loop
'把combo1(1)和combo1(3)里的重复项删除
For k = 0 To 3
For j = 0 To Combo1(k).ListCount - 1
For i = j + 1 To Combo1(k).ListCount - 1
If Combo1(k).List(j) = Combo1(k).List(i) Then
Combo1(k).RemoveItem (i)
i = i - 1
End If
Next i
Next j
Next k
'
' '使combo1(1)和combo1(3)按年份顺序排列
' Dim a, b As Integer
' Dim c As String
' For a = 0 To Combo1.Count - 1
' For b = a To Combo1.Count - 1
' If Left(Combo1(1).List(a), 4) > Left(Combo1(1).List(b), 4) Then
' c = Combo1(1).List(a)
' Combo1(1).List(a) = Combo1(1).List(b)
' Combo1(1).List(b) = c
' End If
' Next b
' Next a
'
' '使combo1(1)和combo1(3)按月份顺序排列
' For a = 0 To Combo1.Count - 1
' For b = a To Combo1.Count - 1
' If Mid(Combo1(1).List(a), 6, 7) > Mid(Combo1(1).List(b), 6, 7) Then
' c = Combo1(1).List(a)
' Combo1(1).List(a) = Combo1(1).List(b)
' Combo1(1).List(b) = c
' End If
' Next b
' Next a
End Sub
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -