Option Compare Database
Private Sub Command工资1月_Click()
If Me.Combo年份 <> "" Then
(资料图片)
year_num = Me.Combo年份
month_num = 1
DoCmd.OpenForm "工资窗体", acNormal
Else
MsgBox "请输入年份"
Exit Sub
End If
End Sub
Private Sub Command工资2月_Click()
If Me.Combo年份 <> "" Then
year_num = Me.Combo年份
month_num = 2
DoCmd.OpenForm "工资窗体", acNormal
Else
MsgBox "请输入年份"
Exit Sub
End If
End Sub
Private Sub Command工资3月_Click()
If Me.Combo年份 <> "" Then
year_num = Me.Combo年份
month_num = 3
DoCmd.OpenForm "工资窗体", acNormal
Else
MsgBox "请输入年份"
Exit Sub
End If
End Sub
Private Sub Command工资4月_Click()
If Me.Combo年份 <> "" Then
year_num = Me.Combo年份
month_num = 4
DoCmd.OpenForm "工资窗体", acNormal
Else
MsgBox "请输入年份"
Exit Sub
End If
End Sub
Private Sub Command工资5月_Click()
If Me.Combo年份 <> "" Then
year_num = Me.Combo年份
month_num = 5
DoCmd.OpenForm "工资窗体", acNormal
Else
MsgBox "请输入年份"
Exit Sub
End If
End Sub
Private Sub Command工资6月_Click()
If Me.Combo年份 <> "" Then
year_num = Me.Combo年份
month_num = 6
DoCmd.OpenForm "工资窗体", acNormal
Else
MsgBox "请输入年份"
Exit Sub
End If
End Sub
Private Sub Command工资7月_Click()
If Me.Combo年份 <> "" Then
year_num = Me.Combo年份
month_num = 7
DoCmd.OpenForm "工资窗体", acNormal
Else
MsgBox "请输入年份"
Exit Sub
End If
End Sub
Private Sub Command工资8月_Click()
If Me.Combo年份 <> "" Then
year_num = Me.Combo年份
month_num = 8
DoCmd.OpenForm "工资窗体", acNormal
Else
MsgBox "请输入年份"
Exit Sub
End If
End Sub
Private Sub Command工资9月_Click()
If Me.Combo年份 <> "" Then
year_num = Me.Combo年份
month_num = 9
DoCmd.OpenForm "工资窗体", acNormal
Else
MsgBox "请输入年份"
Exit Sub
End If
End Sub
Private Sub Command工资10月_Click()
If Me.Combo年份 <> "" Then
year_num = Me.Combo年份
month_num = 10
DoCmd.OpenForm "工资窗体", acNormal
Else
MsgBox "请输入年份"
Exit Sub
End If
End Sub
Private Sub Command工资11月_Click()
If Me.Combo年份 <> "" Then
year_num = Me.Combo年份
month_num = 11
DoCmd.OpenForm "工资窗体", acNormal
Else
MsgBox "请输入年份"
Exit Sub
End If
End Sub
Private Sub Command工资12月_Click()
If Me.Combo年份 <> "" Then
year_num = Me.Combo年份
month_num = 12
DoCmd.OpenForm "工资窗体", acNormal
Else
MsgBox "请输入年份"
Exit Sub
End If
End Sub
Private Sub Command加班1月_Click()
If Me.Combo年份 <> "" Then
year_num = Me.Combo年份
month_num = 1
DoCmd.OpenForm "加班窗体", acNormal
Else
MsgBox "请输入年份"
Exit Sub
End If
End Sub
Private Sub Command加班2月_Click()
If Me.Combo年份 <> "" Then
year_num = Me.Combo年份
month_num = 2
DoCmd.OpenForm "加班窗体", acNormal
Else
MsgBox "请输入年份"
Exit Sub
End If
End Sub
Private Sub Command加班3月_Click()
If Me.Combo年份 <> "" Then
year_num = Me.Combo年份
month_num = 3
DoCmd.OpenForm "加班窗体", acNormal
Else
MsgBox "请输入年份"
Exit Sub
End If
End Sub
Private Sub Command加班4月_Click()
If Me.Combo年份 <> "" Then
year_num = Me.Combo年份
month_num = 4
DoCmd.OpenForm "加班窗体", acNormal
Else
MsgBox "请输入年份"
Exit Sub
End If
End Sub
Private Sub Command加班5月_Click()
If Me.Combo年份 <> "" Then
year_num = Me.Combo年份
month_num = 5
DoCmd.OpenForm "加班窗体", acNormal
Else
MsgBox "请输入年份"
Exit Sub
End If
End Sub
Private Sub Command加班6月_Click()
If Me.Combo年份 <> "" Then
year_num = Me.Combo年份
month_num = 6
DoCmd.OpenForm "加班窗体", acNormal
Else
MsgBox "请输入年份"
Exit Sub
End If
End Sub
Private Sub Command加班7月_Click()
If Me.Combo年份 <> "" Then
year_num = Me.Combo年份
month_num = 7
DoCmd.OpenForm "加班窗体", acNormal
Else
MsgBox "请输入年份"
Exit Sub
End If
End Sub
Private Sub Command加班8月_Click()
If Me.Combo年份 <> "" Then
year_num = Me.Combo年份
month_num = 8
DoCmd.OpenForm "加班窗体", acNormal
Else
MsgBox "请输入年份"
Exit Sub
End If
End Sub
Private Sub Command加班9月_Click()
If Me.Combo年份 <> "" Then
year_num = Me.Combo年份
month_num = 9
DoCmd.OpenForm "加班窗体", acNormal
Else
MsgBox "请输入年份"
Exit Sub
End If
End Sub
Private Sub Command加班10月_Click()
If Me.Combo年份 <> "" Then
year_num = Me.Combo年份
month_num = 10
DoCmd.OpenForm "加班窗体", acNormal
Else
MsgBox "请输入年份"
Exit Sub
End If
End Sub
Private Sub Command加班11月_Click()
If Me.Combo年份 <> "" Then
year_num = Me.Combo年份
month_num = 11
DoCmd.OpenForm "加班窗体", acNormal
Else
MsgBox "请输入年份"
Exit Sub
End If
End Sub
Private Sub Command加班12月_Click()
If Me.Combo年份 <> "" Then
year_num = Me.Combo年份
month_num = 12
DoCmd.OpenForm "加班窗体", acNormal
Else
MsgBox "请输入年份"
Exit Sub
End If
End Sub
Private Sub Command考勤1月_Click()
If Me.Combo年份 <> "" Then
year_num = Me.Combo年份
month_num = 1
DoCmd.OpenForm "考勤窗体", acNormal
Else
MsgBox "请输入年份"
Exit Sub
End If
End Sub
Private Sub Command考勤2月_Click()
If Me.Combo年份 <> "" Then
year_num = Me.Combo年份
month_num = 2
DoCmd.OpenForm "考勤窗体", acNormal
Else
MsgBox "请输入年份"
Exit Sub
End If
End Sub
Private Sub Command考勤3月_Click()
If Me.Combo年份 <> "" Then
year_num = Me.Combo年份
month_num = 3
DoCmd.OpenForm "考勤窗体", acNormal
Else
MsgBox "请输入年份"
Exit Sub
End If
End Sub
Private Sub Command考勤4月_Click()
If Me.Combo年份 <> "" Then
year_num = Me.Combo年份
month_num = 4
DoCmd.OpenForm "考勤窗体", acNormal
Else
MsgBox "请输入年份"
Exit Sub
End If
End Sub
Private Sub Command考勤5月_Click()
If Me.Combo年份 <> "" Then
year_num = Me.Combo年份
month_num = 5
DoCmd.OpenForm "考勤窗体", acNormal
Else
MsgBox "请输入年份"
Exit Sub
End If
End Sub
Private Sub Command考勤6月_Click()
If Me.Combo年份 <> "" Then
year_num = Me.Combo年份
month_num = 6
DoCmd.OpenForm "考勤窗体", acNormal
Else
MsgBox "请输入年份"
Exit Sub
End If
End Sub
Private Sub Command考勤7月_Click()
If Me.Combo年份 <> "" Then
year_num = Me.Combo年份
month_num = 7
DoCmd.OpenForm "考勤窗体", acNormal
Else
MsgBox "请输入年份"
Exit Sub
End If
End Sub
Private Sub Command考勤8月_Click()
If Me.Combo年份 <> "" Then
year_num = Me.Combo年份
month_num = 8
DoCmd.OpenForm "考勤窗体", acNormal
Else
MsgBox "请输入年份"
Exit Sub
End If
End Sub
Private Sub Command考勤9月_Click()
If Me.Combo年份 <> "" Then
year_num = Me.Combo年份
month_num = 9
DoCmd.OpenForm "考勤窗体", acNormal
Else
MsgBox "请输入年份"
Exit Sub
End If
End Sub
Private Sub Command考勤10月_Click()
If Me.Combo年份 <> "" Then
year_num = Me.Combo年份
month_num = 10
DoCmd.OpenForm "考勤窗体", acNormal
Else
MsgBox "请输入年份"
Exit Sub
End If
End Sub
Private Sub Command考勤11月_Click()
If Me.Combo年份 <> "" Then
year_num = Me.Combo年份
month_num = 11
DoCmd.OpenForm "考勤窗体", acNormal
Else
MsgBox "请输入年份"
Exit Sub
End If
End Sub
Private Sub Command考勤12月_Click()
If Me.Combo年份 <> "" Then
year_num = Me.Combo年份
month_num = 12
DoCmd.OpenForm "考勤窗体", acNormal
Else
MsgBox "请输入年份"
Exit Sub
End If
End Sub
Private Sub Command年月工资统计_Click()
DoCmd.OpenForm "年月工资统计", acNormal
End Sub
Private Sub Command年月加班统计_Click()
DoCmd.OpenForm "年月加班统计", acNormal
End Sub
Private Sub Command年月考勤统计_Click()
DoCmd.OpenForm "年月考勤统计", acNormal
End Sub
Private Sub Command全部工资记录_Click()
DoCmd.OpenForm "全部工资记录", acNormal
End Sub
Private Sub Command全部加班记录_Click()
DoCmd.OpenForm "全部加班记录", acNormal
End Sub
Private Sub Command全部考勤记录_Click()
DoCmd.OpenForm "全部考勤记录", acNormal
End Sub
Private Sub Command退出系统_Click()
If MsgBox("是否退出系统", vbYesNo) <> vbYes Then
Exit Sub
End If
Application.Quit acQuitSaveAll
End Sub
Private Sub Command系统后台_Click()
DoCmd.Close acForm, Me.Name
DoCmd.SelectObject acForm, , True
End Sub
Private Sub Command员工工资统计_Click()
DoCmd.OpenForm "员工工资统计", acNormal
End Sub
Private Sub Command员工管理_Click()
DoCmd.OpenForm "员工管理", acNormal
End Sub
Private Sub Command员工加班统计_Click()
DoCmd.OpenForm "员工加班统计", acNormal
End Sub
Private Sub Command员工考勤统计_Click()
DoCmd.OpenForm "员工考勤统计", acNormal
End Sub
Private Sub Form_Load()
Me.Combo年份.Value = Year(Date)
End Sub
Private Sub 年份_DblClick(Cancel As Integer)
If MsgBox("删除【" & Me.姓名 & "】" & Me.年份 & "年" & Me.月份 & "月 工资记录？", vbYesNo) = vbYes Then
DoCmd.SetWarnings (False)
Dim del_sql As String
del_sql = "Delete From 工资表 Where 工资ID=" & Me.工资ID
DoCmd.RunSQL del_sql
On Error Resume Next
Forms("工资窗体").Form.数据表子窗体.Requery
Forms("全部工资记录").Form.数据表子窗体.Requery
End If
End Sub
Option Compare Database
Public ym_filter As String
Private Sub Command查询_Click()
If Me.Combo部门.Value <> "" Then
Me.数据表子窗体.Form.Filter = "部门 like '*" & Me.Combo部门 & "*' and " & ym_filter
Me.数据表子窗体.Form.FilterOn = True
Else
Me.数据表子窗体.Form.Filter = ym_filter
Me.数据表子窗体.Form.FilterOn = True
End If
End Sub
Private Sub Command降序_Click()
If Me.Combo排序.Value <> "" Then
Me.数据表子窗体.Form.OrderBy = Me.Combo排序.Value & " DESC"
Me.数据表子窗体.Form.OrderByOn = True
Else
Me.数据表子窗体.Form.OrderBy = "工资ID DESC"
Me.数据表子窗体.Form.OrderByOn = True
End If
End Sub
Private Sub Command批量更新_Click()
If Me.Combo类型 <> "" And Me.Text金额 <> "" Then
Call updaterecordkq(Me.Text金额, year_num, month_num, Me.Combo类型)
Else
MsgBox "请选择类型，并且金额不能为空"
Exit Sub
End If
MsgBox "更新完成"
Me.数据表子窗体.Requery
End Sub
Private Sub Command全部_Click()
Me.数据表子窗体.Form.Filter = ym_filter
Me.数据表子窗体.Form.FilterOn = True
End Sub
Private Sub Command升序_Click()
If Me.Combo排序.Value <> "" Then
Me.数据表子窗体.Form.OrderBy = Me.Combo排序.Value & " ASC"
Me.数据表子窗体.Form.OrderByOn = True
Else
Me.数据表子窗体.Form.OrderBy = "工资ID ASC"
Me.数据表子窗体.Form.OrderByOn = True
End If
End Sub
Private Sub Command生成_Click()
DoCmd.OpenForm "工资选择员工", acNormal
End Sub
Private Sub Form_Load()
If year_num = 0 Or month_num = 0 Then
MsgBox "请在系统主页窗体点击指定月份打开，请设置符合规范的年份，月份数字"
DoCmd.Close acForm, Me.Name
Exit Sub
End If
Me.年份 = year_num
Me.Label标题.Caption = month_num & "月份工资"
ym_filter = "年份=" & year_num & " and 月份=" & month_num
Me.数据表子窗体.Form.Filter = ym_filter
Me.数据表子窗体.Form.FilterOn = True
Me.数据表子窗体.Form.OrderBy = "工资ID ASC"
Me.数据表子窗体.Form.OrderByOn = True
End Sub
Public Sub updaterecordkq(ByVal salarynum As Currency, ByVal ynum As Long, ByVal mnum As Long, ByVal fieldn As String)
'如果姓名不存在则不添加
Dim update_rs As DAO.Recordset
Dim update_sql As String
update_sql = "select * From 工资表 where 年份=" & ynum & " and 月份=" & mnum
Set update_rs = CurrentDb.OpenRecordset(update_sql, dbOpenDynaset)
With update_rs
Do While update_rs.EOF = False
.Edit
.Fields(fieldn).Value = salarynum
.Update
.MoveNext
Loop
.Close
End With
Set update_rs = Nothing
End Sub
Option Compare Database
Private Sub Command全部取消_Click()
'------------------------------------------------清空全部列表框原有选项
Dim i
Dim icount As Long
icount = Me.List全部.ListCount
For i = icount - 1 To 0 Step -1
Me.List全部.RemoveItem i
Next i
icount = Me.List选中.ListCount
For i = icount - 1 To 0 Step -1
Me.List选中.RemoveItem i
Next i
'------------------------------------------------查询员工表添加全部列表框选项
Dim search_rs As DAO.Recordset
Dim search_sql As String
search_sql = "Select * From 员工表"
Set search_rs = CurrentDb.OpenRecordset(search_sql, dbOpenDynaset)
Do While search_rs.EOF = False
Me.List全部.AddItem search_rs!姓名.Value & "," & search_rs!部门.Value & "," & search_rs!职务.Value
search_rs.MoveNext
Loop
search_rs.Close
Set search_rs = Nothing
End Sub
Private Sub Command全部选中_Click()
Dim i
Dim icount As Long
icount = Me.List全部.ListCount
If icount > 0 Then
For i = icount - 1 To 0 Step -1
Me.List选中.AddItem List全部.Column(0, i) & "," & List全部.Column(1, i) & "," & List全部.Column(2, i)
Me.List全部.RemoveItem i
Next i
End If
Me.List全部.Requery
Me.List选中.Requery
End Sub
Private Sub Command生成_Click()
'------------------------------------------------循环选中组合框
Dim i
Dim icount As Long
icount = Me.List选中.ListCount
Dim item_name As String
If icount > 0 Then
For i = icount - 1 To 0 Step -1
item_name = Me.List选中.Column(0, i)
'添加新记录至工资表
Call addrecordkq(item_name, year_num, month_num)
Next i
MsgBox "生成完成"
End If
End Sub
Private Sub Form_Close()
Forms("工资窗体").Form.数据表子窗体.Requery
End Sub
Private Sub Form_Load()
If year_num = 0 Or month_num = 0 Then
MsgBox "请在系统主页窗体点击指定月份打开，请设置符合规范的年份，月份数字"
DoCmd.Close acForm, Me.Name
Exit Sub
End If
'------------------------------------------------清空全部列表框原有选项
Dim i
Dim icount As Long
icount = Me.List全部.ListCount
For i = icount - 1 To 0 Step -1
Me.List全部.RemoveItem i
Next i
icount = Me.List选中.ListCount
For i = icount - 1 To 0 Step -1
Me.List选中.RemoveItem i
Next i
'------------------------------------------------查询员工表添加全部列表框选项
Dim search_rs As DAO.Recordset
Dim search_sql As String
search_sql = "Select * From 员工表"
Set search_rs = CurrentDb.OpenRecordset(search_sql, dbOpenDynaset)
Do While search_rs.EOF = False
Me.List全部.AddItem search_rs!姓名.Value & "," & search_rs!部门.Value & "," & search_rs!职务.Value
search_rs.MoveNext
Loop
search_rs.Close
Set search_rs = Nothing
End Sub
Private Sub List全部_DblClick(Cancel As Integer)
If IsNull(List全部.Value) = False And List全部.ListIndex <> -1 Then
Me.List选中.AddItem List全部.Column(0) & "," & List全部.Column(1) & "," & List全部.Column(2)
Me.List全部.RemoveItem List全部.ListIndex
End If
Me.List全部.Requery
Me.List选中.Requery
End Sub
Private Sub List选中_DblClick(Cancel As Integer)
If IsNull(List选中.Value) = False And List选中.ListIndex <> -1 Then
Me.List全部.AddItem List选中.Column(0) & "," & List选中.Column(1) & "," & List选中.Column(2)
Me.List选中.RemoveItem List选中.ListIndex
End If
Me.List选中.Requery
Me.List全部.Requery
End Sub
Public Sub addrecordkq(ByVal yg_name As String, ByVal ynum As Long, ByVal mnum As Long)
'如果姓名不存在则不添加
If Nz(DCount("姓名", "员工表", "姓名='" & yg_name & "'"), 0) = 0 Then
Exit Sub
End If
Dim add_rs As DAO.Recordset
Set add_rs = CurrentDb.OpenRecordset("工资表", dbOpenTable)
With add_rs
.AddNew
!年份.Value = ynum
!月份.Value = mnum
!姓名.Value = yg_name
!基本工资.Value = Nz(DLookup("基本工资", "员工表", "姓名='" & yg_name & "'"), 0)
!岗位工资.Value = Nz(DLookup("岗位工资", "员工表", "姓名='" & yg_name & "'"), 0)
!加班工资.Value = Nz(DLookup("加班", "员工表", "姓名='" & yg_name & "'"), 0) * Nz(DLookup("加班", "加班表", "姓名='" & yg_name & "' and 年份=" & ynum & " and 月份=" & mnum), 0)
!绩效考核.Value = 0
!奖金.Value = 0
!补贴.Value = 0
!出勤.Value = Nz(DLookup("出勤", "员工表", "姓名='" & yg_name & "'"), 0) * Nz(DLookup("出勤", "考勤表", "姓名='" & yg_name & "' and 年份=" & ynum & " and 月份=" & mnum), 0)
!病假.Value = Nz(DLookup("病假", "员工表", "姓名='" & yg_name & "'"), 0) * Nz(DLookup("病假", "考勤表", "姓名='" & yg_name & "' and 年份=" & ynum & " and 月份=" & mnum), 0)
!补休.Value = Nz(DLookup("补休", "员工表", "姓名='" & yg_name & "'"), 0) * Nz(DLookup("补休", "考勤表", "姓名='" & yg_name & "' and 年份=" & ynum & " and 月份=" & mnum), 0)
!出差.Value = Nz(DLookup("出差", "员工表", "姓名='" & yg_name & "'"), 0) * Nz(DLookup("出差", "考勤表", "姓名='" & yg_name & "' and 年份=" & ynum & " and 月份=" & mnum), 0)
!产假.Value = Nz(DLookup("产假", "员工表", "姓名='" & yg_name & "'"), 0) * Nz(DLookup("产假", "考勤表", "姓名='" & yg_name & "' and 年份=" & ynum & " and 月份=" & mnum), 0)
!迟到.Value = Nz(DLookup("迟到", "员工表", "姓名='" & yg_name & "'"), 0) * Nz(DLookup("迟到", "考勤表", "姓名='" & yg_name & "' and 年份=" & ynum & " and 月份=" & mnum), 0)
!婚假.Value = Nz(DLookup("婚假", "员工表", "姓名='" & yg_name & "'"), 0) * Nz(DLookup("婚假", "考勤表", "姓名='" & yg_name & "' and 年份=" & ynum & " and 月份=" & mnum), 0)
!旷工.Value = Nz(DLookup("旷工", "员工表", "姓名='" & yg_name & "'"), 0) * Nz(DLookup("旷工", "考勤表", "姓名='" & yg_name & "' and 年份=" & ynum & " and 月份=" & mnum), 0)
!年假.Value = Nz(DLookup("年假", "员工表", "姓名='" & yg_name & "'"), 0) * Nz(DLookup("年假", "考勤表", "姓名='" & yg_name & "' and 年份=" & ynum & " and 月份=" & mnum), 0)
!培训.Value = Nz(DLookup("培训", "员工表", "姓名='" & yg_name & "'"), 0) * Nz(DLookup("培训", "考勤表", "姓名='" & yg_name & "' and 年份=" & ynum & " and 月份=" & mnum), 0)
!事假.Value = Nz(DLookup("事假", "员工表", "姓名='" & yg_name & "'"), 0) * Nz(DLookup("事假", "考勤表", "姓名='" & yg_name & "' and 年份=" & ynum & " and 月份=" & mnum), 0)
!休息.Value = Nz(DLookup("休息", "员工表", "姓名='" & yg_name & "'"), 0) * Nz(DLookup("休息", "考勤表", "姓名='" & yg_name & "' and 年份=" & ynum & " and 月份=" & mnum), 0)
!早退.Value = Nz(DLookup("早退", "员工表", "姓名='" & yg_name & "'"), 0) * Nz(DLookup("早退", "考勤表", "姓名='" & yg_name & "' and 年份=" & ynum & " and 月份=" & mnum), 0)
!自定义.Value = Nz(DLookup("自定义", "员工表", "姓名='" & yg_name & "'"), 0) * Nz(DLookup("自定义", "考勤表", "姓名='" & yg_name & "' and 年份=" & ynum & " and 月份=" & mnum), 0)
!其他应发.Value = 0
!社保.Value = 0
!个税.Value = 0
!公积金.Value = 0
!其他代扣.Value = 0
.Update
.Close
End With
Set add_rs = Nothing
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim date_month As Long
If Me.日数29日 <> 0 Then
date_month = Month(DateSerial(year_num, month_num, 29))
If date_month <> month_num Then
MsgBox "该月份没有29号"
Me.日数29日.Value = 0
End If
End If
If Me.日数30日 <> 0 Then
date_month = Month(DateSerial(year_num, month_num, 30))
If date_month <> month_num Then
MsgBox "该月份没有30号"
Me.日数30日.Value = 0
End If
End If
If Me.日数31日 <> 0 Then
date_month = Month(DateSerial(year_num, month_num, 31))
If date_month <> month_num Then
MsgBox "该月份没有31号"
Me.日数31日.Value = 0
End If
End If
End Sub
Private Sub 年份_DblClick(Cancel As Integer)
If MsgBox("删除【" & Me.姓名 & "】" & Me.年份 & "年" & Me.月份 & "月 加班记录？", vbYesNo) = vbYes Then
DoCmd.SetWarnings (False)
Dim del_sql As String
del_sql = "Delete From 加班表 Where 加班ID=" & Me.加班ID
DoCmd.RunSQL del_sql
On Error Resume Next
Forms("加班窗体").Form.数据表子窗体.Requery
Forms("全部加班记录").Form.数据表子窗体.Requery
End If
End Sub
Option Compare Database
Public ym_filter As String
Private Sub Command查询_Click()
If Me.Combo部门.Value <> "" Then
Me.数据表子窗体.Form.Filter = "部门 like '*" & Me.Combo部门 & "*' and " & ym_filter
Me.数据表子窗体.Form.FilterOn = True
Else
Me.数据表子窗体.Form.Filter = ym_filter
Me.数据表子窗体.Form.FilterOn = True
End If
End Sub
Private Sub Command降序_Click()
If Me.Combo排序.Value <> "" Then
Me.数据表子窗体.Form.OrderBy = Me.Combo排序.Value & " DESC"
Me.数据表子窗体.Form.OrderByOn = True
Else
Me.数据表子窗体.Form.OrderBy = "加班ID DESC"
Me.数据表子窗体.Form.OrderByOn = True
End If
End Sub
Private Sub Command批量更新_Click()
'日期不能为空
If Me.Combo日期 <> "" Then
Dim date_month As Long
date_month = Month(DateSerial(year_num, month_num, Me.Combo日期))
If date_month <> month_num Then
MsgBox "该月份没有" & Me.Combo日期 & "号"
Exit Sub
End If
Else
MsgBox "请选择日期"
Exit Sub
End If
If Me.Combo加班 <> "" Then
Call updaterecordkq(Me.Combo加班, year_num, month_num, Me.Combo日期)
Else
MsgBox "请输入或选择加班时间"
Exit Sub
End If
MsgBox "更新完成"
Me.数据表子窗体.Requery
End Sub
Private Sub Command全部_Click()
Me.数据表子窗体.Form.Filter = ym_filter
Me.数据表子窗体.Form.FilterOn = True
End Sub
Private Sub Command升序_Click()
If Me.Combo排序.Value <> "" Then
Me.数据表子窗体.Form.OrderBy = Me.Combo排序.Value & " ASC"
Me.数据表子窗体.Form.OrderByOn = True
Else
Me.数据表子窗体.Form.OrderBy = "加班ID ASC"
Me.数据表子窗体.Form.OrderByOn = True
End If
End Sub
Private Sub Command生成_Click()
DoCmd.OpenForm "加班选择员工", acNormal
End Sub
Private Sub Form_Load()
If year_num = 0 Or month_num = 0 Then
MsgBox "请在系统主页窗体点击指定月份打开，请设置符合规范的年份，月份数字"
DoCmd.Close acForm, Me.Name
Exit Sub
End If
Me.年份 = year_num
Me.Label标题.Caption = month_num & "月份加班"
ym_filter = "年份=" & year_num & " and 月份=" & month_num
Me.数据表子窗体.Form.Filter = ym_filter
Me.数据表子窗体.Form.FilterOn = True
Me.数据表子窗体.Form.OrderBy = "加班ID ASC"
Me.数据表子窗体.Form.OrderByOn = True
Me.Combo日期 = Day(Date)
End Sub
Public Sub updaterecordkq(ByVal worknum As Single, ByVal ynum As Long, ByVal mnum As Long, ByVal daynum As Long)
'如果姓名不存在则不添加
Dim update_rs As DAO.Recordset
Dim update_sql As String
update_sql = "select * From 加班表 where 年份=" & ynum & " and 月份=" & mnum
Set update_rs = CurrentDb.OpenRecordset(update_sql, dbOpenDynaset)
With update_rs
Dim update_field As String
update_field = "日数" & daynum & "日"
Do While update_rs.EOF = False
.Edit
.Fields(CStr(update_field)).Value = worknum
.Update
.MoveNext
Loop
.Close
End With
Set update_rs = Nothing
End Sub
Option Compare Database
Private Sub Command全部取消_Click()
'------------------------------------------------清空全部列表框原有选项
Dim i
Dim icount As Long
icount = Me.List全部.ListCount
For i = icount - 1 To 0 Step -1
Me.List全部.RemoveItem i
Next i
icount = Me.List选中.ListCount
For i = icount - 1 To 0 Step -1
Me.List选中.RemoveItem i
Next i
'------------------------------------------------查询员工表添加全部列表框选项
Dim search_rs As DAO.Recordset
Dim search_sql As String
search_sql = "Select * From 员工表"
Set search_rs = CurrentDb.OpenRecordset(search_sql, dbOpenDynaset)
Do While search_rs.EOF = False
Me.List全部.AddItem search_rs!姓名.Value & "," & search_rs!部门.Value & "," & search_rs!职务.Value
search_rs.MoveNext
Loop
search_rs.Close
Set search_rs = Nothing
End Sub
Private Sub Command全部选中_Click()
Dim i
Dim icount As Long
icount = Me.List全部.ListCount
If icount > 0 Then
For i = icount - 1 To 0 Step -1
Me.List选中.AddItem List全部.Column(0, i) & "," & List全部.Column(1, i) & "," & List全部.Column(2, i)
Me.List全部.RemoveItem i
Next i
End If
Me.List全部.Requery
Me.List选中.Requery
End Sub
Private Sub Command生成_Click()
'------------------------------------------------循环选中组合框
Dim i
Dim icount As Long
icount = Me.List选中.ListCount
Dim item_name As String
If icount > 0 Then
For i = icount - 1 To 0 Step -1
item_name = Me.List选中.Column(0, i)
'添加新记录至加班表
Call addrecordkq(item_name, year_num, month_num)
Next i
MsgBox "生成完成"
End If
End Sub
Private Sub Form_Close()
Forms("加班窗体").Form.数据表子窗体.Requery
End Sub
Private Sub Form_Load()
If year_num = 0 Or month_num = 0 Then
MsgBox "请在系统主页窗体点击指定月份打开，请设置符合规范的年份，月份数字"
DoCmd.Close acForm, Me.Name
Exit Sub
End If
'------------------------------------------------清空全部列表框原有选项
Dim i
Dim icount As Long
icount = Me.List全部.ListCount
For i = icount - 1 To 0 Step -1
Me.List全部.RemoveItem i
Next i
icount = Me.List选中.ListCount
For i = icount - 1 To 0 Step -1
Me.List选中.RemoveItem i
Next i
'------------------------------------------------查询员工表添加全部列表框选项
Dim search_rs As DAO.Recordset
Dim search_sql As String
search_sql = "Select * From 员工表"
Set search_rs = CurrentDb.OpenRecordset(search_sql, dbOpenDynaset)
Do While search_rs.EOF = False
Me.List全部.AddItem search_rs!姓名.Value & "," & search_rs!部门.Value & "," & search_rs!职务.Value
search_rs.MoveNext
Loop
search_rs.Close
Set search_rs = Nothing
End Sub
Private Sub List全部_DblClick(Cancel As Integer)
If IsNull(List全部.Value) = False And List全部.ListIndex <> -1 Then
Me.List选中.AddItem List全部.Column(0) & "," & List全部.Column(1) & "," & List全部.Column(2)
Me.List全部.RemoveItem List全部.ListIndex
End If
Me.List全部.Requery
Me.List选中.Requery
End Sub
Public Sub addrecordkq(ByVal yg_name As String, ByVal ynum As Long, ByVal mnum As Long)
'如果姓名不存在则不添加
If Nz(DCount("姓名", "员工表", "姓名='" & yg_name & "'"), 0) = 0 Then
Exit Sub
End If
Dim add_rs As DAO.Recordset
Set add_rs = CurrentDb.OpenRecordset("加班表", dbOpenTable)
With add_rs
.AddNew
!年份.Value = ynum
!月份.Value = mnum
!姓名.Value = yg_name
.Update
.Close
End With
Set add_rs = Nothing
End Sub
Private Sub List选中_DblClick(Cancel As Integer)
If IsNull(List选中.Value) = False And List选中.ListIndex <> -1 Then
Me.List全部.AddItem List选中.Column(0) & "," & List选中.Column(1) & "," & List选中.Column(2)
Me.List选中.RemoveItem List选中.ListIndex
End If
Me.List选中.Requery
Me.List全部.Requery
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim date_month As Long
If Me.日数29日 <> "" Then
date_month = Month(DateSerial(year_num, month_num, 29))
If date_month <> month_num Then
MsgBox "该月份没有29号"
Me.日数29日.Value = ""
End If
End If
If Me.日数30日 <> "" Then
date_month = Month(DateSerial(year_num, month_num, 30))
If date_month <> month_num Then
MsgBox "该月份没有30号"
Me.日数30日.Value = ""
End If
End If
If Me.日数31日 <> "" Then
date_month = Month(DateSerial(year_num, month_num, 31))
If date_month <> month_num Then
MsgBox "该月份没有31号"
Me.日数31日.Value = ""
End If
End If
End Sub
Private Sub 年份_DblClick(Cancel As Integer)
If MsgBox("删除【" & Me.姓名 & "】" & Me.年份 & "年" & Me.月份 & "月 考勤记录？", vbYesNo) = vbYes Then
DoCmd.SetWarnings (False)
Dim del_sql As String
del_sql = "Delete From 考勤表 Where 考勤ID=" & Me.考勤ID
DoCmd.RunSQL del_sql
On Error Resume Next
Forms("考勤窗体").Form.数据表子窗体.Requery
Forms("全部考勤记录").Form.数据表子窗体.Requery
End If
End Sub
Option Compare Database
Public ym_filter As String
Private Sub Command查询_Click()
If Me.Combo部门.Value <> "" Then
Me.数据表子窗体.Form.Filter = "部门 like '*" & Me.Combo部门 & "*' and " & ym_filter
Me.数据表子窗体.Form.FilterOn = True
Else
Me.数据表子窗体.Form.Filter = ym_filter
Me.数据表子窗体.Form.FilterOn = True
End If
End Sub
Private Sub Command降序_Click()
If Me.Combo排序.Value <> "" Then
Me.数据表子窗体.Form.OrderBy = Me.Combo排序.Value & " DESC"
Me.数据表子窗体.Form.OrderByOn = True
Else
Me.数据表子窗体.Form.OrderBy = "考勤ID DESC"
Me.数据表子窗体.Form.OrderByOn = True
End If
End Sub
Private Sub Command批量更新_Click()
'日期不能为空
If Me.Combo日期 <> "" Then
Dim date_month As Long
date_month = Month(DateSerial(year_num, month_num, Me.Combo日期))
If date_month <> month_num Then
MsgBox "该月份没有" & Me.Combo日期 & "号"
Exit Sub
End If
Else
MsgBox "请选择日期"
Exit Sub
End If
If Me.Combo类型 <> "" Then
Call updaterecordkq(Me.Combo类型, year_num, month_num, Me.Combo日期)
Else
Call updaterecordkq("", year_num, month_num, Me.Combo日期)
End If
MsgBox "更新完成"
Me.数据表子窗体.Requery
End Sub
Private Sub Command全部_Click()
Me.数据表子窗体.Form.Filter = ym_filter
Me.数据表子窗体.Form.FilterOn = True
End Sub
Private Sub Command升序_Click()
If Me.Combo排序.Value <> "" Then
Me.数据表子窗体.Form.OrderBy = Me.Combo排序.Value & " ASC"
Me.数据表子窗体.Form.OrderByOn = True
Else
Me.数据表子窗体.Form.OrderBy = "考勤ID ASC"
Me.数据表子窗体.Form.OrderByOn = True
End If
End Sub
Private Sub Command生成_Click()
DoCmd.OpenForm "考勤选择员工", acNormal
End Sub
Private Sub Form_Load()
If year_num = 0 Or month_num = 0 Then
MsgBox "请在系统主页窗体点击指定月份打开，请设置符合规范的年份，月份数字"
DoCmd.Close acForm, Me.Name
Exit Sub
End If
Me.年份 = year_num
Me.Label标题.Caption = month_num & "月份考勤"
ym_filter = "年份=" & year_num & " and 月份=" & month_num
Me.数据表子窗体.Form.Filter = ym_filter
Me.数据表子窗体.Form.FilterOn = True
Me.数据表子窗体.Form.OrderBy = "考勤ID ASC"
Me.数据表子窗体.Form.OrderByOn = True
Me.Combo日期 = Day(Date)
End Sub
Public Sub updaterecordkq(ByVal markchar As String, ByVal ynum As Long, ByVal mnum As Long, ByVal daynum As Long)
'如果姓名不存在则不添加
Dim update_rs As DAO.Recordset
Dim update_sql As String
update_sql = "select * From 考勤表 where 年份=" & ynum & " and 月份=" & mnum
Set update_rs = CurrentDb.OpenRecordset(update_sql, dbOpenDynaset)
With update_rs
Dim update_field As String
update_field = "日数" & daynum & "日"
Do While update_rs.EOF = False
.Edit
.Fields(CStr(update_field)).Value = markchar
.Update
.MoveNext
Loop
.Close
End With
Set update_rs = Nothing
End Sub
Option Compare Database
Private Sub Command全部取消_Click()
'------------------------------------------------清空全部列表框原有选项
Dim i
Dim icount As Long
icount = Me.List全部.ListCount
For i = icount - 1 To 0 Step -1
Me.List全部.RemoveItem i
Next i
icount = Me.List选中.ListCount
For i = icount - 1 To 0 Step -1
Me.List选中.RemoveItem i
Next i
'------------------------------------------------查询员工表添加全部列表框选项
Dim search_rs As DAO.Recordset
Dim search_sql As String
search_sql = "Select * From 员工表"
Set search_rs = CurrentDb.OpenRecordset(search_sql, dbOpenDynaset)
Do While search_rs.EOF = False
Me.List全部.AddItem search_rs!姓名.Value & "," & search_rs!部门.Value & "," & search_rs!职务.Value
search_rs.MoveNext
Loop
search_rs.Close
Set search_rs = Nothing
End Sub
Private Sub Command全部选中_Click()
Dim i
Dim icount As Long
icount = Me.List全部.ListCount
If icount > 0 Then
For i = icount - 1 To 0 Step -1
Me.List选中.AddItem List全部.Column(0, i) & "," & List全部.Column(1, i) & "," & List全部.Column(2, i)
Me.List全部.RemoveItem i
Next i
End If
Me.List全部.Requery
Me.List选中.Requery
End Sub
Private Sub Command生成_Click()
'------------------------------------------------循环选中组合框
Dim i
Dim icount As Long
icount = Me.List选中.ListCount
Dim item_name As String
If icount > 0 Then
For i = icount - 1 To 0 Step -1
item_name = Me.List选中.Column(0, i)
'添加新记录至考勤表
Call addrecordkq(item_name, year_num, month_num)
Next i
MsgBox "生成完成"
End If
End Sub
Private Sub Form_Close()
Forms("考勤窗体").Form.数据表子窗体.Requery
End Sub
Private Sub Form_Load()
If year_num = 0 Or month_num = 0 Then
MsgBox "请在系统主页窗体点击指定月份打开，请设置符合规范的年份，月份数字"
DoCmd.Close acForm, Me.Name
Exit Sub
End If
'------------------------------------------------清空全部列表框原有选项
Dim i
Dim icount As Long
icount = Me.List全部.ListCount
For i = icount - 1 To 0 Step -1
Me.List全部.RemoveItem i
Next i
icount = Me.List选中.ListCount
For i = icount - 1 To 0 Step -1
Me.List选中.RemoveItem i
Next i
'------------------------------------------------查询员工表添加全部列表框选项
Dim search_rs As DAO.Recordset
Dim search_sql As String
search_sql = "Select * From 员工表"
Set search_rs = CurrentDb.OpenRecordset(search_sql, dbOpenDynaset)
Do While search_rs.EOF = False
Me.List全部.AddItem search_rs!姓名.Value & "," & search_rs!部门.Value & "," & search_rs!职务.Value
search_rs.MoveNext
Loop
search_rs.Close
Set search_rs = Nothing
End Sub
Private Sub List全部_DblClick(Cancel As Integer)
If IsNull(List全部.Value) = False And List全部.ListIndex <> -1 Then
Me.List选中.AddItem List全部.Column(0) & "," & List全部.Column(1) & "," & List全部.Column(2)
Me.List全部.RemoveItem List全部.ListIndex
End If
Me.List全部.Requery
Me.List选中.Requery
End Sub
Public Sub addrecordkq(ByVal yg_name As String, ByVal ynum As Long, ByVal mnum As Long)
'如果姓名不存在则不添加
If Nz(DCount("姓名", "员工表", "姓名='" & yg_name & "'"), 0) = 0 Then
Exit Sub
End If
Dim add_rs As DAO.Recordset
Set add_rs = CurrentDb.OpenRecordset("考勤表", dbOpenTable)
With add_rs
.AddNew
!年份.Value = ynum
!月份.Value = mnum
!姓名.Value = yg_name
.Update
.Close
End With
Set add_rs = Nothing
End Sub
Private Sub List选中_DblClick(Cancel As Integer)
If IsNull(List选中.Value) = False And List选中.ListIndex <> -1 Then
Me.List全部.AddItem List选中.Column(0) & "," & List选中.Column(1) & "," & List选中.Column(2)
Me.List选中.RemoveItem List选中.ListIndex
End If
Me.List选中.Requery
Me.List全部.Requery
End Sub
Option Compare Database
Private Sub Command数据导出_Click()
On Error GoTo 导出失败
'------------------------------------------------清空表数据
DoCmd.SetWarnings (False)
Dim del_sql As String
del_sql = "Delete From 年月工资统计表"
DoCmd.RunSQL del_sql
Dim dflink_sql As String
ck_filter = Me.数据表子窗体.Form.Filter
If ck_filter <> "" And Me.数据表子窗体.Form.FilterOn = True Then
dflink_sql = "SELECT * FROM 年月工资统计查询 " & " Where " & ck_filter
Else
dflink_sql = "SELECT * FROM 年月工资统计查询"
End If
ck_order = Me.数据表子窗体.Form.OrderBy
If ck_order <> "" Then
dflink_sql = dflink_sql & " order by " & ck_order
End If
Dim dflink_rs As DAO.Recordset
Set dflink_rs = CurrentDb.OpenRecordset(dflink_sql, dbOpenDynaset)
With dflink_rs
Dim add_rs As DAO.Recordset
Set add_rs = CurrentDb.OpenRecordset("年月工资统计表", dbOpenTable)
Do While .EOF = False
add_rs.AddNew
add_rs!年份.Value = !年份.Value
add_rs!月份.Value = !月份.Value
add_rs!基本工资合计.Value = !基本工资合计.Value
add_rs!岗位工资合计.Value = !岗位工资合计.Value
add_rs!加班工资合计.Value = !加班工资合计.Value
add_rs!绩效考核合计.Value = !绩效考核合计.Value
add_rs!奖金合计.Value = !奖金合计.Value
add_rs!补贴合计.Value = !补贴合计.Value
add_rs!出勤合计.Value = !出勤合计.Value
add_rs!病假合计.Value = !病假合计.Value
add_rs!补休合计.Value = !补休合计.Value
add_rs!出差合计.Value = !出差合计.Value
add_rs!产假合计.Value = !产假合计.Value
add_rs!迟到合计.Value = !迟到合计.Value
add_rs!婚假合计.Value = !婚假合计.Value
add_rs!旷工合计.Value = !旷工合计.Value
add_rs!年假合计.Value = !年假合计.Value
add_rs!培训合计.Value = !培训合计.Value
add_rs!事假合计.Value = !事假合计.Value
add_rs!休息合计.Value = !休息合计.Value
add_rs!早退合计.Value = !早退合计.Value
add_rs!自定义合计.Value = !自定义合计.Value
add_rs!其他应发合计.Value = !其他应发合计.Value
add_rs!社保合计.Value = !社保合计.Value
add_rs!个税合计.Value = !个税合计.Value
add_rs!公积金合计.Value = !公积金合计.Value
add_rs!其他代扣合计.Value = !其他代扣合计.Value
add_rs!应发工资合计.Value = !应发工资合计.Value
add_rs!代扣工资合计.Value = !代扣工资合计.Value
add_rs!实发工资合计.Value = !实发工资合计.Value
add_rs.Update
.MoveNext
Loop
End With
add_rs.Close
Set add_rs = Nothing
dflink_rs.Close
Set dflink_rs = Nothing
Call 导出查询表结果("年月工资统计表")
Exit Sub
导出失败:
MsgBox Err.Description
End Sub
Private Sub Form_Load()
Me.数据表子窗体.Form.OrderBy = "年份 DESC,月份 DESC"
Me.数据表子窗体.Form.OrderByOn = True
End Sub
Option Compare Database
Private Sub Command数据导出_Click()
On Error GoTo 导出失败
'------------------------------------------------清空表数据
DoCmd.SetWarnings (False)
Dim del_sql As String
del_sql = "Delete From 年月加班统计表"
DoCmd.RunSQL del_sql
Dim dflink_sql As String
ck_filter = Me.数据表子窗体.Form.Filter
If ck_filter <> "" And Me.数据表子窗体.Form.FilterOn = True Then
dflink_sql = "SELECT * FROM 年月加班统计查询 " & " Where " & ck_filter
Else
dflink_sql = "SELECT * FROM 年月加班统计查询"
End If
ck_order = Me.数据表子窗体.Form.OrderBy
If ck_order <> "" Then
dflink_sql = dflink_sql & " order by " & ck_order
End If
Dim dflink_rs As DAO.Recordset
Set dflink_rs = CurrentDb.OpenRecordset(dflink_sql, dbOpenDynaset)
With dflink_rs
Dim add_rs As DAO.Recordset
Set add_rs = CurrentDb.OpenRecordset("年月加班统计表", dbOpenTable)
Do While .EOF = False
add_rs.AddNew
add_rs!年份.Value = !年份.Value
add_rs!月份.Value = !月份.Value
add_rs!加班合计.Value = !加班合计.Value
add_rs.Update
.MoveNext
Loop
End With
add_rs.Close
Set add_rs = Nothing
dflink_rs.Close
Set dflink_rs = Nothing
Call 导出查询表结果("年月加班统计表")
Exit Sub
导出失败:
MsgBox Err.Description
End Sub
Private Sub Form_Load()
Me.数据表子窗体.Form.OrderBy = "年份 DESC,月份 DESC"
Me.数据表子窗体.Form.OrderByOn = True
End Sub
Option Compare Database
Private Sub Command数据导出_Click()
On Error GoTo 导出失败
'------------------------------------------------清空表数据
DoCmd.SetWarnings (False)
Dim del_sql As String
del_sql = "Delete From 年月考勤统计表"
DoCmd.RunSQL del_sql
Dim dflink_sql As String
ck_filter = Me.数据表子窗体.Form.Filter
If ck_filter <> "" And Me.数据表子窗体.Form.FilterOn = True Then
dflink_sql = "SELECT * FROM 年月考勤统计查询 " & " Where " & ck_filter
Else
dflink_sql = "SELECT * FROM 年月考勤统计查询"
End If
ck_order = Me.数据表子窗体.Form.OrderBy
If ck_order <> "" Then
dflink_sql = dflink_sql & " order by " & ck_order
End If
Dim dflink_rs As DAO.Recordset
Set dflink_rs = CurrentDb.OpenRecordset(dflink_sql, dbOpenDynaset)
With dflink_rs
Dim add_rs As DAO.Recordset
Set add_rs = CurrentDb.OpenRecordset("年月考勤统计表", dbOpenTable)
Do While .EOF = False
add_rs.AddNew
add_rs!年份.Value = !年份.Value
add_rs!月份.Value = !月份.Value
add_rs!出勤合计.Value = !出勤合计.Value
add_rs!病假合计.Value = !病假合计.Value
add_rs!补休合计.Value = !补休合计.Value
add_rs!出差合计.Value = !出差合计.Value
add_rs!产假合计.Value = !产假合计.Value
add_rs!迟到合计.Value = !迟到合计.Value
add_rs!婚假合计.Value = !婚假合计.Value
add_rs!旷工合计.Value = !旷工合计.Value
add_rs!年假合计.Value = !年假合计.Value
add_rs!培训合计.Value = !培训合计.Value
add_rs!事假合计.Value = !事假合计.Value
add_rs!休息合计.Value = !休息合计.Value
add_rs!早退合计.Value = !早退合计.Value
add_rs!自定义合计.Value = !自定义合计.Value
add_rs.Update
.MoveNext
Loop
End With
add_rs.Close
Set add_rs = Nothing
dflink_rs.Close
Set dflink_rs = Nothing
Call 导出查询表结果("年月考勤统计表")
Exit Sub
导出失败:
MsgBox Err.Description
End Sub
Private Sub Form_Load()
Me.数据表子窗体.Form.OrderBy = "年份 DESC,月份 DESC"
Me.数据表子窗体.Form.OrderByOn = True
End Sub
Option Compare Database
Dim filter_all As String
Private Sub Command查询_Click()
filter_all = ""
If Me.Combo年份 <> "" Then
If filter_all <> "" Then
filter_all = filter_all & "and 年份=" & Me.Combo年份
Else
filter_all = "年份=" & Me.Combo年份
End If
End If
If Me.Combo月份 <> "" Then
If filter_all <> "" Then
filter_all = filter_all & "and 月份=" & Me.Combo月份
Else
filter_all = "月份=" & Me.Combo月份
End If
End If
If Me.Combo部门 <> "" Then
If filter_all <> "" Then
filter_all = filter_all & "and 部门 like '*" & Me.Combo部门 & "*'"
Else
filter_all = "部门 like '*" & Me.Combo部门 & "*'"
End If
End If
If Me.Combo姓名 <> "" Then
If filter_all <> "" Then
filter_all = filter_all & "and 姓名 like '*" & Me.Combo姓名 & "*'"
Else
filter_all = "姓名 like '*" & Me.Combo姓名 & "*'"
End If
End If
If filter_all <> "" Then
Me.数据表子窗体.Form.Filter = filter_all
Me.数据表子窗体.Form.FilterOn = True
Else
Me.数据表子窗体.Form.FilterOn = False
End If
End Sub
Private Sub Command降序_Click()
If Me.Combo排序.Value <> "" Then
Me.数据表子窗体.Form.OrderBy = Me.Combo排序.Value & " DESC"
Me.数据表子窗体.Form.OrderByOn = True
Else
Me.数据表子窗体.Form.OrderBy = "加班ID DESC"
Me.数据表子窗体.Form.OrderByOn = True
End If
End Sub
Private Sub Command清除_Click()
Me.Combo年份 = ""
Me.Combo月份 = ""
Me.Combo部门 = ""
Me.Combo姓名 = ""
End Sub
Private Sub Command全部_Click()
filter_all = ""
Me.数据表子窗体.Form.FilterOn = False
End Sub
Private Sub Command升序_Click()
If Me.Combo排序.Value <> "" Then
Me.数据表子窗体.Form.OrderBy = Me.Combo排序.Value & " ASC"
Me.数据表子窗体.Form.OrderByOn = True
Else
Me.数据表子窗体.Form.OrderBy = "加班ID DESC"
Me.数据表子窗体.Form.OrderByOn = True
End If
End Sub
Private Sub Command数据导出_Click()
'On Error GoTo 导出失败
'------------------------------------------------清空表数据
DoCmd.SetWarnings (False)
Dim del_sql As String
del_sql = "Delete From 加班记录"
DoCmd.RunSQL del_sql
Dim dflink_sql As String
ck_filter = Me.数据表子窗体.Form.Filter
If ck_filter <> "" And Me.数据表子窗体.Form.FilterOn = True Then
dflink_sql = "SELECT * FROM 加班查询 " & " Where " & ck_filter
Else
dflink_sql = "SELECT * FROM 加班查询"
End If
ck_order = Me.数据表子窗体.Form.OrderBy
If ck_order <> "" Then
dflink_sql = dflink_sql & " order by " & ck_order
End If
Dim dflink_rs As DAO.Recordset
Set dflink_rs = CurrentDb.OpenRecordset(dflink_sql, dbOpenDynaset)
With dflink_rs
Dim add_rs As DAO.Recordset
Set add_rs = CurrentDb.OpenRecordset("加班记录", dbOpenTable)
Do While .EOF = False
add_rs.AddNew
add_rs!年份.Value = !年份.Value
add_rs!月份.Value = !月份.Value
add_rs!姓名.Value = !姓名.Value
add_rs!部门.Value = !部门.Value
add_rs!职务.Value = !职务.Value
add_rs!日数1日.Value = !日数1日.Value
add_rs!日数2日.Value = !日数2日.Value
add_rs!日数3日.Value = !日数3日.Value
add_rs!日数4日.Value = !日数4日.Value
add_rs!日数5日.Value = !日数5日.Value
add_rs!日数6日.Value = !日数6日.Value
add_rs!日数7日.Value = !日数7日.Value
add_rs!日数8日.Value = !日数8日.Value
add_rs!日数9日.Value = !日数9日.Value
add_rs!日数10日.Value = !日数10日.Value
add_rs!日数11日.Value = !日数11日.Value
add_rs!日数12日.Value = !日数12日.Value
add_rs!日数13日.Value = !日数13日.Value
add_rs!日数14日.Value = !日数14日.Value
add_rs!日数15日.Value = !日数15日.Value
add_rs!日数16日.Value = !日数16日.Value
add_rs!日数17日.Value = !日数17日.Value
add_rs!日数18日.Value = !日数18日.Value
add_rs!日数19日.Value = !日数19日.Value
add_rs!日数20日.Value = !日数20日.Value
add_rs!日数21日.Value = !日数21日.Value
add_rs!日数22日.Value = !日数22日.Value
add_rs!日数23日.Value = !日数23日.Value
add_rs!日数24日.Value = !日数24日.Value
add_rs!日数25日.Value = !日数25日.Value
add_rs!日数26日.Value = !日数26日.Value
add_rs!日数27日.Value = !日数27日.Value
add_rs!日数28日.Value = !日数28日.Value
add_rs!日数29日.Value = !日数29日.Value
add_rs!日数30日.Value = !日数30日.Value
add_rs!日数31日.Value = !日数31日.Value
add_rs!加班.Value = !加班.Value
add_rs.Update
.MoveNext
Loop
End With
add_rs.Close
Set add_rs = Nothing
dflink_rs.Close
Set dflink_rs = Nothing
Call 导出查询表结果("加班记录")
Exit Sub
导出失败:
MsgBox Err.Description
End Sub
Private Sub Form_Load()
Me.数据表子窗体.Form.OrderBy = "年份 DESC,月份 DESC"
Me.数据表子窗体.Form.OrderByOn = True
End Sub
Option Compare Database
Dim filter_all As String
Private Sub Command查询_Click()
filter_all = ""
If Me.Combo年份 <> "" Then
If filter_all <> "" Then
filter_all = filter_all & "and 年份=" & Me.Combo年份
Else
filter_all = "年份=" & Me.Combo年份
End If
End If
If Me.Combo月份 <> "" Then
If filter_all <> "" Then
filter_all = filter_all & "and 月份=" & Me.Combo月份
Else
filter_all = "月份=" & Me.Combo月份
End If
End If
If Me.Combo部门 <> "" Then
If filter_all <> "" Then
filter_all = filter_all & "and 部门 like '*" & Me.Combo部门 & "*'"
Else
filter_all = "部门 like '*" & Me.Combo部门 & "*'"
End If
End If
If Me.Combo姓名 <> "" Then
If filter_all <> "" Then
filter_all = filter_all & "and 姓名 like '*" & Me.Combo姓名 & "*'"
Else
filter_all = "姓名 like '*" & Me.Combo姓名 & "*'"
End If
End If
If filter_all <> "" Then
Me.数据表子窗体.Form.Filter = filter_all
Me.数据表子窗体.Form.FilterOn = True
Else
Me.数据表子窗体.Form.FilterOn = False
End If
End Sub
Private Sub Command降序_Click()
If Me.Combo排序.Value <> "" Then
Me.数据表子窗体.Form.OrderBy = Me.Combo排序.Value & " DESC"
Me.数据表子窗体.Form.OrderByOn = True
Else
Me.数据表子窗体.Form.OrderBy = "加班ID DESC"
Me.数据表子窗体.Form.OrderByOn = True
End If
End Sub
Private Sub Command清除_Click()
Me.Combo年份 = ""
Me.Combo月份 = ""
Me.Combo部门 = ""
Me.Combo姓名 = ""
End Sub
Private Sub Command全部_Click()
filter_all = ""
Me.数据表子窗体.Form.FilterOn = False
End Sub
Private Sub Command升序_Click()
If Me.Combo排序.Value <> "" Then
Me.数据表子窗体.Form.OrderBy = Me.Combo排序.Value & " ASC"
Me.数据表子窗体.Form.OrderByOn = True
Else
Me.数据表子窗体.Form.OrderBy = "加班ID DESC"
Me.数据表子窗体.Form.OrderByOn = True
End If
End Sub
Private Sub Command数据导出_Click()
'On Error GoTo 导出失败
'------------------------------------------------清空表数据
DoCmd.SetWarnings (False)
Dim del_sql As String
del_sql = "Delete From 加班记录"
DoCmd.RunSQL del_sql
Dim dflink_sql As String
ck_filter = Me.数据表子窗体.Form.Filter
If ck_filter <> "" And Me.数据表子窗体.Form.FilterOn = True Then
dflink_sql = "SELECT * FROM 加班查询 " & " Where " & ck_filter
Else
dflink_sql = "SELECT * FROM 加班查询"
End If
ck_order = Me.数据表子窗体.Form.OrderBy
If ck_order <> "" Then
dflink_sql = dflink_sql & " order by " & ck_order
End If
Dim dflink_rs As DAO.Recordset
Set dflink_rs = CurrentDb.OpenRecordset(dflink_sql, dbOpenDynaset)
With dflink_rs
Dim add_rs As DAO.Recordset
Set add_rs = CurrentDb.OpenRecordset("加班记录", dbOpenTable)
Do While .EOF = False
add_rs.AddNew
add_rs!年份.Value = !年份.Value
add_rs!月份.Value = !月份.Value
add_rs!姓名.Value = !姓名.Value
add_rs!部门.Value = !部门.Value
add_rs!职务.Value = !职务.Value
add_rs!日数1日.Value = !日数1日.Value
add_rs!日数2日.Value = !日数2日.Value
add_rs!日数3日.Value = !日数3日.Value
add_rs!日数4日.Value = !日数4日.Value
add_rs!日数5日.Value = !日数5日.Value
add_rs!日数6日.Value = !日数6日.Value
add_rs!日数7日.Value = !日数7日.Value
add_rs!日数8日.Value = !日数8日.Value
add_rs!日数9日.Value = !日数9日.Value
add_rs!日数10日.Value = !日数10日.Value
add_rs!日数11日.Value = !日数11日.Value
add_rs!日数12日.Value = !日数12日.Value
add_rs!日数13日.Value = !日数13日.Value
add_rs!日数14日.Value = !日数14日.Value
add_rs!日数15日.Value = !日数15日.Value
add_rs!日数16日.Value = !日数16日.Value
add_rs!日数17日.Value = !日数17日.Value
add_rs!日数18日.Value = !日数18日.Value
add_rs!日数19日.Value = !日数19日.Value
add_rs!日数20日.Value = !日数20日.Value
add_rs!日数21日.Value = !日数21日.Value
add_rs!日数22日.Value = !日数22日.Value
add_rs!日数23日.Value = !日数23日.Value
add_rs!日数24日.Value = !日数24日.Value
add_rs!日数25日.Value = !日数25日.Value
add_rs!日数26日.Value = !日数26日.Value
add_rs!日数27日.Value = !日数27日.Value
add_rs!日数28日.Value = !日数28日.Value
add_rs!日数29日.Value = !日数29日.Value
add_rs!日数30日.Value = !日数30日.Value
add_rs!日数31日.Value = !日数31日.Value
add_rs!加班.Value = !加班.Value
add_rs.Update
.MoveNext
Loop
End With
add_rs.Close
Set add_rs = Nothing
dflink_rs.Close
Set dflink_rs = Nothing
Call 导出查询表结果("加班记录")
Exit Sub
导出失败:
MsgBox Err.Description
End Sub
Private Sub Form_Load()
Me.数据表子窗体.Form.OrderBy = "年份 DESC,月份 DESC"
Me.数据表子窗体.Form.OrderByOn = True
End Sub
Option Compare Database
Dim filter_all As String
Private Sub Command查询_Click()
filter_all = ""
If Me.Combo年份 <> "" Then
If filter_all <> "" Then
filter_all = filter_all & "and 年份=" & Me.Combo年份
Else
filter_all = "年份=" & Me.Combo年份
End If
End If
If Me.Combo月份 <> "" Then
If filter_all <> "" Then
filter_all = filter_all & "and 月份=" & Me.Combo月份
Else
filter_all = "月份=" & Me.Combo月份
End If
End If
If Me.Combo部门 <> "" Then
If filter_all <> "" Then
filter_all = filter_all & "and 部门 like '*" & Me.Combo部门 & "*'"
Else
filter_all = "部门 like '*" & Me.Combo部门 & "*'"
End If
End If
If Me.Combo姓名 <> "" Then
If filter_all <> "" Then
filter_all = filter_all & "and 姓名 like '*" & Me.Combo姓名 & "*'"
Else
filter_all = "姓名 like '*" & Me.Combo姓名 & "*'"
End If
End If
If filter_all <> "" Then
Me.数据表子窗体.Form.Filter = filter_all
Me.数据表子窗体.Form.FilterOn = True
Else
Me.数据表子窗体.Form.FilterOn = False
End If
End Sub
Private Sub Command降序_Click()
If Me.Combo排序.Value <> "" Then
Me.数据表子窗体.Form.OrderBy = Me.Combo排序.Value & " DESC"
Me.数据表子窗体.Form.OrderByOn = True
Else
Me.数据表子窗体.Form.OrderBy = "考勤ID DESC"
Me.数据表子窗体.Form.OrderByOn = True
End If
End Sub
Private Sub Command清除_Click()
Me.Combo年份 = ""
Me.Combo月份 = ""
Me.Combo部门 = ""
Me.Combo姓名 = ""
End Sub
Private Sub Command全部_Click()
filter_all = ""
Me.数据表子窗体.Form.FilterOn = False
End Sub
Private Sub Command升序_Click()
If Me.Combo排序.Value <> "" Then
Me.数据表子窗体.Form.OrderBy = Me.Combo排序.Value & " ASC"
Me.数据表子窗体.Form.OrderByOn = True
Else
Me.数据表子窗体.Form.OrderBy = "考勤ID DESC"
Me.数据表子窗体.Form.OrderByOn = True
End If
End Sub
Private Sub Command数据导出_Click()
On Error GoTo 导出失败
'------------------------------------------------清空表数据
DoCmd.SetWarnings (False)
Dim del_sql As String
del_sql = "Delete From 考勤记录"
DoCmd.RunSQL del_sql
Dim dflink_sql As String
ck_filter = Me.数据表子窗体.Form.Filter
If ck_filter <> "" And Me.数据表子窗体.Form.FilterOn = True Then
dflink_sql = "SELECT * FROM 考勤查询 " & " Where " & ck_filter
Else
dflink_sql = "SELECT * FROM 考勤查询"
End If
ck_order = Me.数据表子窗体.Form.OrderBy
If ck_order <> "" Then
dflink_sql = dflink_sql & " order by " & ck_order
End If
Dim dflink_rs As DAO.Recordset
Set dflink_rs = CurrentDb.OpenRecordset(dflink_sql, dbOpenDynaset)
With dflink_rs
Dim add_rs As DAO.Recordset
Set add_rs = CurrentDb.OpenRecordset("考勤记录", dbOpenTable)
Do While .EOF = False
add_rs.AddNew
add_rs!年份.Value = !年份.Value
add_rs!月份.Value = !月份.Value
add_rs!姓名.Value = !姓名.Value
add_rs!部门.Value = !部门.Value
add_rs!职务.Value = !职务.Value
add_rs!日数1日.Value = !日数1日.Value
add_rs!日数2日.Value = !日数2日.Value
add_rs!日数3日.Value = !日数3日.Value
add_rs!日数4日.Value = !日数4日.Value
add_rs!日数5日.Value = !日数5日.Value
add_rs!日数6日.Value = !日数6日.Value
add_rs!日数7日.Value = !日数7日.Value
add_rs!日数8日.Value = !日数8日.Value
add_rs!日数9日.Value = !日数9日.Value
add_rs!日数10日.Value = !日数10日.Value
add_rs!日数11日.Value = !日数11日.Value
add_rs!日数12日.Value = !日数12日.Value
add_rs!日数13日.Value = !日数13日.Value
add_rs!日数14日.Value = !日数14日.Value
add_rs!日数15日.Value = !日数15日.Value
add_rs!日数16日.Value = !日数16日.Value
add_rs!日数17日.Value = !日数17日.Value
add_rs!日数18日.Value = !日数18日.Value
add_rs!日数19日.Value = !日数19日.Value
add_rs!日数20日.Value = !日数20日.Value
add_rs!日数21日.Value = !日数21日.Value
add_rs!日数22日.Value = !日数22日.Value
add_rs!日数23日.Value = !日数23日.Value
add_rs!日数24日.Value = !日数24日.Value
add_rs!日数25日.Value = !日数25日.Value
add_rs!日数26日.Value = !日数26日.Value
add_rs!日数27日.Value = !日数27日.Value
add_rs!日数28日.Value = !日数28日.Value
add_rs!日数29日.Value = !日数29日.Value
add_rs!日数30日.Value = !日数30日.Value
add_rs!日数31日.Value = !日数31日.Value
add_rs!出勤.Value = !出勤.Value
add_rs!病假.Value = !病假.Value
add_rs!补休.Value = !补休.Value
add_rs!出差.Value = !出差.Value
add_rs!产假.Value = !产假.Value
add_rs!迟到.Value = !迟到.Value
add_rs!婚假.Value = !婚假.Value
add_rs!旷工.Value = !旷工.Value
add_rs!年假.Value = !年假.Value
add_rs!培训.Value = !培训.Value
add_rs!事假.Value = !事假.Value
add_rs!休息.Value = !休息.Value
add_rs!早退.Value = !早退.Value
add_rs!自定义.Value = !自定义.Value
add_rs.Update
.MoveNext
Loop
End With
add_rs.Close
Set add_rs = Nothing
dflink_rs.Close
Set dflink_rs = Nothing
Call 导出查询表结果("考勤记录")
Exit Sub
导出失败:
MsgBox Err.Description
End Sub
Private Sub Form_Load()
Me.数据表子窗体.Form.OrderBy = "年份 DESC,月份 DESC"
Me.数据表子窗体.Form.OrderByOn = True
End Sub
Public year_num As Long
Public month_num As Long
Public Sub 导出查询表结果(ByVal tablename As String)
On Error GoTo 导出查询_Err
DoCmd.OutputTo acOutputTable, tablename, "", "", False, "", , acExportQualityPrint
导出查询_Exit:
Exit Sub
导出查询_Err:
Resume 导出查询_Exit
End Sub
标签：
上一篇 : “英雄回家，英雄回家！”
3月16日，盛和资源(600392)副总经理毛韶春、黄厚兵，财务总监夏兰田，董秘郭晓雷，通过上交所集中竞价交...
2022年3月15日，这是继1983年以来的第40个国际消费者权益日。中消协组织围绕共促消费公平消费维权年主题...
首批金控牌照的归属出炉，两家公司拿到许可证。3月17日，央行发布公告称，已批准中国中信金融控股有限公...
时隔半月之久，西宁市城北区逐步推动复工复产，往日的生机活力被渐渐寻回，牛肉面红油飘香、包子铺炊烟...
音乐是我生活的一部分，是我的梦想，也是我的事业。英国音乐人亚当(Adam)告诉记者，在中国的十几年里，...
Copyright © 2015-2022 大河舞蹈网版权所有 备案号：京ICP备2022022245号-75 联系邮箱：435 226 40@qq.com
