📄 main_kqgl_addmodify.frm
字号:
Begin VB.CommandButton cmdSeladd
Caption = "选择员工"
Height = 345
Left = 7050
TabIndex = 13
Top = 1515
Width = 1245
End
Begin VB.CommandButton cmdDeptadd
Caption = "部门员工"
Height = 345
Left = 5730
TabIndex = 12
Top = 1515
Width = 1245
End
Begin VB.CommandButton cmdAlladd
Caption = "全体员工"
Height = 345
Left = 4410
TabIndex = 11
Top = 1515
Width = 1245
End
Begin MSDataListLib.DataCombo DataCombo1
Bindings = "main_kqgl_addmodify.frx":165F
Height = 330
Left = 4365
TabIndex = 9
Top = 240
Width = 3990
_ExtentX = 7038
_ExtentY = 582
_Version = 393216
ListField = "部门名称"
BoundColumn = "部门编号"
Text = ""
End
Begin VB.Frame Frame2
Height = 690
Left = 165
TabIndex = 3
Top = 1185
Width = 3180
Begin VB.OptionButton Option1
Caption = "下班"
Height = 435
Index = 1
Left = 1650
TabIndex = 5
Top = 180
Width = 1400
End
Begin VB.OptionButton Option1
Caption = "上班"
Height = 435
Index = 0
Left = 345
TabIndex = 4
Top = 180
Value = -1 'True
Width = 1515
End
End
Begin MSComCtl2.DTPicker DTPicker1
Height = 315
Left = 1170
TabIndex = 2
Top = 300
Width = 2160
_ExtentX = 3810
_ExtentY = 556
_Version = 393216
Format = 19791873
CurrentDate = 39450
End
Begin MSMask.MaskEdBox MaskEdBoxTime
Height = 315
Left = 1170
TabIndex = 18
Top = 750
Width = 2160
_ExtentX = 3810
_ExtentY = 556
_Version = 393216
MaxLength = 5
Format = "hh:mm"
Mask = "##:##"
PromptChar = "_"
End
Begin VB.Label Label5
Caption = "上下班日期"
Height = 270
Left = 135
TabIndex = 16
Top = 390
Width = 1080
End
Begin VB.Label Label4
Caption = "员工"
Height = 240
Left = 3840
TabIndex = 10
Top = 840
Width = 495
End
Begin VB.Label Label3
Caption = "部门"
Height = 240
Left = 3840
TabIndex = 8
Top = 300
Width = 495
End
Begin VB.Label Label1
Caption = "上下班时间"
Height = 270
Left = 135
TabIndex = 1
Top = 840
Width = 1080
End
End
Begin VB.Label Label2
BackStyle = 0 'Transparent
Caption = "出勤记录表："
Height = 255
Left = 90
TabIndex = 6
Top = 2325
Width = 1230
End
End
Attribute VB_Name = "main_kqgl_addmodify"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Dim rs As New ADODB.Recordset, rs1 As New ADODB.Recordset, rs2 As New ADODB.Recordset
Public s As String, mysql As String
Sub timeerr()
If Left(MaskEdBoxTime.Text, 2) > 24 Then '如果小时大于24，则提示时间输入错误
MsgBox "时间输入错误！"
Exit Sub
End If
If Right(MaskEdBoxTime.Text, 2) > 59 Then '如果分钟大于59，则提示时间输入错误
MsgBox "时间输入错误！"
Exit Sub
End If
End Sub
Private Sub Form_Load()
DataCombo1.Text = "总公司" '设置"部门"为总公司
Option1_Click (Index) '调用该过程，默认设置为上班
cmdFind_Click '调用该过程按默认条件检索数据
DTPicker1.Value = Date '设置上下班日期为当前系统日期
End Sub
Private Sub Option1_Click(Index As Integer)
s = Option1(Index).Caption '将Option1控件数组显示的标题赋值给变量s
rs.Open "时间设置表", cnn, adOpenKeyset, adLockOptimistic '连接时间设置表
If rs.RecordCount > 0 Then '如果表中有记录
If Option1(0) = True Then '如果选择了"上班"
MaskEdBoxTime.Text = Left(rs.Fields("上班时间"), 5) '设置上班时间为表中的时间
Else '否则
MaskEdBoxTime.Text = Left(rs.Fields("下班时间"), 5) '设置下班时间为表中的时间
End If
End If
rs.Close '关闭数据集对象
End Sub
Private Sub DataCombo1_Change()
List1.Clear '清空列表（List1）
If DataCombo1.Text = "总公司" Then '如果为"总公司"
rs.Open "select * from 员工信息表", cnn, adOpenKeyset, adLockOptimistic '连接员工信息表
If rs.RecordCount > 0 Then '如果表中有记录
rs.MoveFirst '将记录指针移到第一条记录
Do While rs.EOF = False '只要记录不到尾
List1.AddItem rs.Fields("编号") & " " & rs.Fields("姓名") '将当前员工编号和姓名添加到列表中
rs.MoveNext '将记录指针移到下一条记录
Loop
End If
rs.Close '关闭数据集对象
Else '否则
rs.Open "select * from 员工信息表 where 部门='" + DataCombo1.Text + "'", cnn, adOpenKeyset, adLockOptimistic '连接员工信息，显示当前部门的所有员工
If rs.RecordCount > 0 Then '如果有记录
rs.MoveFirst '将记录指针移到第一条记录
Do While rs.EOF = False '只要记录不到尾
List1.AddItem rs.Fields("编号") & " " & rs.Fields("姓名") '将当前员工编号和姓名添加到列表中
rs.MoveNext '将记录指针移到下一条记录
Loop
End If
rs.Close '关闭数据集对象
End If
cmdFind_Click '调用过程，按当前默认条件检索记录
End Sub
Private Sub cmdAlladd_Click()
rs1.Open "select * from 员工信息表 order by 编号", cnn, adOpenKeyset, adLockOptimistic '连接员工信息表
If rs1.RecordCount > 0 Then '如果表中有记录
Do While rs1.EOF = False '只要记录不到尾
rs2.Open "select * from 出勤记录表 where 日期='" + Str(DTPicker1.Value) + "'and 上下班情况='" + s + "'and 员工编号='" + rs1.Fields("编号") + "'", cnn, adOpenKeyset, adLockOptimistic '按指定条件查找员工出勤记录
If rs2.RecordCount > 0 Then '如果有记录就什么也不做
Else '否则
rs2.AddNew '添加新记录
'给员工编号和员工姓名字段赋值
rs2.Fields("员工编号") = rs1.Fields("编号")
rs2.Fields("员工姓名") = rs1.Fields("姓名")
'给上下班情况字段赋值
Set rs = cnn.Execute("select * from 时间设置表") '连接时间设置表
If Option1(0) = True Then
rs2.Fields("上下班情况") = Option1(0).Caption
'将上班时间同"时间设置表"中的时间进行比较
If MaskEdBoxTime.Text <> Left(rs.Fields("上班时间"), 5) Then
rs2.Fields("是否异常") = "异常" '如果不同则为"异常"，同时计算异常时间
rs2.Fields("异常时间") = DateDiff("n", Left(rs.Fields("上班时间"), 5), MaskEdBoxTime.Text) & "分钟"
Else '否则
rs2.Fields("是否异常") = "正常" '为"正常"
End If
Else
rs2.Fields("上下班情况") = Option1(1).Caption
'将下班时间同"时间设置表"中的时间进行比较
If MaskEdBoxTime.Text <> Left(rs.Fields("下班时间"), 5) Then
rs2.Fields("是否异常") = "异常" '如果不同则为"异常"，同时计算异常时间
rs2.Fields("异常时间") = DateDiff("n", MaskEdBoxTime.Text, Left(rs.Fields("下班时间"), 5)) & "分钟"
Else '否则
rs2.Fields("是否异常") = "正常" '为"正常"
End If
End If
'给上下班时间和日期字段赋值
rs2.Fields("上下班时间") = MaskEdBoxTime.Text
rs2.Fields("日期") = DTPicker1.Value
rs.Close '关闭数据集对象rs
rs2.Update '更新数据表
End If
rs2.Close '关闭数据集对象rs2
rs1.MoveNext '将记录指针移到下一条记录
Loop
End If
rs1.Close '关闭数据集对象rs1
Adodc2.Refresh '刷新数据库
End Sub
Private Sub cmdDeptadd_Click()
rs1.Open "select * from 员工信息表 where 部门='" + DataCombo1.Text + "'order by 编号", cnn, adOpenKeyset, adLockOptimistic
If rs1.RecordCount > 0 Then
Do While rs1.EOF = False
rs2.Open "select * from 出勤记录表 where 日期='" + Str(DTPicker1.Value) + "'and 上下班情况='" + s + "'and 员工编号='" + rs1.Fields("编号") + "'", cnn, adOpenKeyset, adLockOptimistic
If rs2.RecordCount > 0 Then
Else
rs2.AddNew
rs2.Fields("员工编号") = rs1.Fields("编号")
rs2.Fields("员工姓名") = rs1.Fields("姓名")
Set rs = cnn.Execute("select * from 时间设置表")
If Option1(0) = True Then
rs2.Fields("上下班情况") = Option1(0).Caption
If MaskEdBoxTime.Text <> Left(rs.Fields("上班时间"), 5) Then
rs2.Fields("是否异常") = "异常"
rs2.Fields("异常时间") = DateDiff("n", Left(rs.Fields("上班时间"), 5), MaskEdBoxTime.Text) & "分钟"
Else
rs2.Fields("是否异常") = "正常"
End If
Else
rs2.Fields("上下班情况") = Option1(1).Caption
If MaskEdBoxTime.Text <> Left(rs.Fields("下班时间"), 5) Then
rs2.Fields("是否异常") = "异常"
rs2.Fields("异常时间") = DateDiff("n", MaskEdBoxTime.Text, Left(rs.Fields("下班时间"), 5)) & "分钟"
Else
rs2.Fields("是否异常") = "正常"
End If
End If
rs2.Fields("上下班时间") = MaskEdBoxTime.Text
rs2.Fields("日期") = DTPicker1.Value
rs.Close
rs2.Update
End If
rs2.Close
rs1.MoveNext
Loop
End If
rs1.Close
Adodc2.Refresh
End Sub
Private Sub cmdSeladd_Click()
Load main_selman '载入"选择员工"窗体
main_selman.Show '显示"选择员工"窗体
End Sub
Private Sub cmdFind_Click()
If DataCombo1.Text = "总公司" Then '如果"部门"列表中显示的是"总公司"
mysql = "出勤记录表 where 日期='" + Str(DTPicker1.Value) + "'and 上下班情况='" + s + "'" '生成SQL语句
Else '否则
'生成SQL语句
mysql = "出勤记录表,员工信息表 where 员工信息表.部门='" + DataCombo1.Text + "'and 出勤记录表.员工编号=员工信息表.编号 and 出勤记录表.日期='" + Str(DTPicker1.Value) + "'and 出勤记录表.上下班情况='" + s + "'"
End If
Adodc2.RecordSource = mysql '将SQL语句赋值给ADO控件的RecordSource属性实现查询
Adodc2.Refresh '刷新数据库
End Sub
Private Sub cmdSeldelete_Click()
If DataCombo1.Text = "总公司" Then '如果"部门"列表中显示的是"总公司"
cnn.Execute ("delete from 出勤记录表 where 日期='" + Str(DTPicker1.Value) + "'and 出勤记录表.上下班情况='" + s + "'") '删除符合条件的出勤记录
Else '否则
'按部门删除符合条件的出勤记录
cnn.Execute ("delete from 出勤记录表 where 员工编号 in (select 员工信息表.编号 from 出勤记录表,员工信息表 where 员工信息表.部门='" + DataCombo1.Text + "'and 出勤记录表.员工编号=员工信息表.编号 and 出勤记录表.日期='" + Str(DTPicker1.Value) + "'and 出勤记录表.上下班情况='" + s + "')")
End If
Adodc2.Refresh '刷新数据表
End Sub
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -