form1.frm

档案管理

setcmd
End Sub

Private Sub cmdsave_Click()
Dim sql As String
Dim rs_save As New ADODB.Recordset
Dim i As Integer
Dim answer As String
On Error GoTo commanderror
If Trim(Text3(0).Text) = "" Then
      MsgBox "职工姓名不能为空", vbOKOnly + vbExclamation, ""
      Text3(0).SetFocus
      Exit Sub
End If
If Trim(Text1.Text) = "" Then
      MsgBox "职工姓名不能为空", vbOKOnly + vbExclamation, ""
      Text1.SetFocus
      Exit Sub
End If
If Trim(Text2.Text) = "" Then
      MsgBox "职工编号不能为空", vbOKOnly + vbExclamation, ""
      Text2.SetFocus
      Exit Sub
End If
If Trim(Combo2.Text) = "" Then
      MsgBox "请选择性别", vbOKOnly + vbExclamation, ""
      Combo2.SetFocus
      Exit Sub
End If
If Trim(Text3(4).Text) = "" Then
      MsgBox "请输入出生日期", vbOKOnly + vbExclamation, ""
      Text3(4).SetFocus
      Exit Sub
End If
If Trim(Combo1.Text) = "" Then
      MsgBox "请选择隶属部门", vbOKOnly + vbExclamation, ""
      Combo1.SetFocus
      Exit Sub
End If
If Trim(Combo3.Text) = "" Then
      MsgBox "请选择婚否", vbOKOnly + vbExclamation, ""
      Combo3.SetFocus
      Exit Sub
End If
If Trim(Combo4.Text) = "" Then
      MsgBox "请选择职工类别", vbOKOnly + vbExclamation, ""
      Combo4.SetFocus
      Exit Sub
End If
If Trim(Combo5.Text) = "" Then
      MsgBox "请选择工资种类", vbOKOnly + vbExclamation, ""
      Combo5.SetFocus
      Exit Sub
End If
If Trim(Text3(15).Text) = "" Then
      MsgBox "请输入参加工作时间", vbOKOnly + vbExclamation, ""
      Text3(15).SetFocus
      Exit Sub
End If
If Trim(Text3(16).Text) = "" Then
      MsgBox "请输入进入本单位时间", vbOKOnly + vbExclamation, ""
      Text3(16).SetFocus
      Exit Sub
End If
If Trim(Text1.Text) <> Trim(Text3(0).Text) Then
      MsgBox "姓名不一致", vbOKOnly + vbExclamation, ""
      Exit Sub
End If
If Not IsDate(Text3(4).Text) Then
      MsgBox "日期请按照yyyy-mm-dd格式输入", vbOKOnly + vbExclamation, ""
      Text3(4).SetFocus
      Exit Sub
End If
If Not IsDate(Text3(15).Text) Then
      MsgBox "日期请按照yyyy-mm-dd格式输入", vbOKOnly + vbExclamation, ""
      Text3(15).SetFocus
      Exit Sub
End If
If Not IsDate(Text3(16).Text) Then
      MsgBox "日期请按照yyyy-mm-dd格式输入", vbOKOnly + vbExclamation, ""
      Text3(16).SetFocus
      Exit Sub
End If
      Text3(4) = Format(Text3(4), "yyyy-mm-dd")      '格式化日期
      Text3(15) = Format(Text3(15), "yyyy-mm-dd")
      Text3(16) = Format(Text3(16), "yyyy-mm-dd")                                              '判断控件是否为空，及日期格式是否正确
If show_arch = False And addinfo = True Then         '如果处于添加状态
      sql = "select * from 基本档案 where employee_NO ='" & Trim(Text2.Text) & "'"
      rs_save.Open sql, conn, adOpenKeyset, adLockPessimistic
      If rs_save.EOF = False Then
         MsgBox "编号重复，请重新输入", vbOKOnly + vbExclamation, "警告"
         Text2.SetFocus
      End If
      'sql = "select * from 基本档案 "
      'rs_save.Open sql, conn, adOpenKeyset, adLockPessimistic
      rs_save.AddNew
      rs_save.Fields(0) = Trim(Text2.Text)           '输入编号
      For i = 0 To rs_save.Fields.Count - 2
          rs_save.Fields(i + 1) = Trim(Text3(i).Text)
      Next i
      rs_save.Fields(4) = Trim(Combo2.Text)          '输入性别
      rs_save.Fields(2) = Trim(Combo1.Text)          '输入部门
      rs_save.Fields(9) = Trim(Combo3.Text)          '输入婚否
      rs_save.Fields(14) = Trim(Combo4.Text)         '输入职工类型
      rs_save.Fields(20) = Trim(Combo5.Text)         '输入工资类别
      rs_save.Update                                 '更新数据库
'添加家庭信息
If Not IsNull(DataGrid1.Bookmark) Then
   rs_grid1.Fields(rs_grid1.Fields.Count - 1) = Trim(Text2.Text)
   rs_grid1.Update
End If
'添加工作记录信息
If Not IsNull(DataGrid2.Bookmark) Then
   rs_grid2.Fields(rs_grid2.Fields.Count - 1) = Trim(Text2.Text)
   rs_grid2.Update
End If
'添加奖惩记录
If Not IsNull(DataGrid3.Bookmark) Then
   rs_grid3.Fields(rs_grid3.Fields.Count - 1) = Trim(Text2.Text)
   rs_grid3.Update
End If
'添加职称评定
If Not IsNull(DataGrid4.Bookmark) Then
   rs_grid4.Fields(rs_grid4.Fields.Count - 1) = Trim(Text2.Text)
   rs_grid4.Update
End If
'添加培训记录
If Not IsNull(DataGrid5.Bookmark) Then
   rs_grid5.Fields(rs_grid5.Fields.Count - 1) = Trim(Text2.Text)
   rs_grid5.Update
End If
'添加变动记录
If Not IsNull(DataGrid6.Bookmark) Then
   rs_grid6.Fields(rs_grid6.Fields.Count - 1) = Trim(Text2.Text)
   rs_grid6.Update
End If
      MsgBox "增加档案成功", vbOKOnly + vbExclamation, ""
      rs_save.Close
      Unload Me
Else                                                 '处于修改状态
      answer = MsgBox("确定修改？", vbYesNo)
      If answer = vbYes Then
      sql = "select * from 基本档案 where employee_NO ='" & Trim(Text2.Text) & "'"
      rs_save.Open sql, conn, adOpenKeyset, adLockPessimistic
      For i = 1 To rs_save.Fields.Count - 1
          rs_save.Fields(i) = Trim(Text3(i - 1).Text)
      Next i
'修改家庭成员信息
If Not IsNull(DataGrid1.Bookmark) Then
   rs_grid1.Fields(rs_grid1.Fields.Count - 1) = Trim(Text2.Text)
   rs_grid1.Update
End If
'修改工作记录信息
If Not IsNull(DataGrid2.Bookmark) Then
   rs_grid2.Fields(rs_grid2.Fields.Count - 1) = Trim(Text2.Text)
   rs_grid2.Update
End If
'修改奖惩记录
If Not IsNull(DataGrid3.Bookmark) Then
   rs_grid3.Fields(rs_grid3.Fields.Count - 1) = Trim(Text2.Text)
   rs_grid3.Update
End If
'修改职称评定
If Not IsNull(DataGrid4.Bookmark) Then
   rs_grid4.Fields(rs_grid4.Fields.Count - 1) = Trim(Text2.Text)
   rs_grid4.Update
End If
'修改培训记录
If Not IsNull(DataGrid5.Bookmark) Then
   rs_grid5.Fields(rs_grid5.Fields.Count - 1) = Trim(Text2.Text)
   rs_grid5.Update
End If
'修改变动记录
If Not IsNull(DataGrid6.Bookmark) Then
   rs_grid6.Fields(rs_grid6.Fields.Count - 1) = Trim(Text2.Text)
   rs_grid6.Update
End If
      rs_save.Fields(4) = Trim(Combo2.Text)          '输入性别
      rs_save.Fields(2) = Trim(Combo1.Text)          '输入部门
      rs_save.Fields(9) = Trim(Combo3.Text)          '输入婚否
      rs_save.Fields(14) = Trim(Combo4.Text)         '输入职工类型
      rs_save.Fields(20) = Trim(Combo5.Text)         '输入工资类别
      rs_save.Update                                 '更新数据库
      MsgBox "修改档案成功", vbOKOnly + vbExclamation, ""
      rs_save.Close
      cmd_states = True
      setcmd
      Else
           rs_save.CancelUpdate
           cmd_states = True
           setcmd
      End If
End If
commanderror:
If Err.Number <> 0 Then
      MsgBox Err.Description
End If
End Sub

Private Sub Form_Load()
If username = "guest" Then
   cmdmodify.Enabled = False
   cmddel.Enabled = False
End If
setcmd                                     '标记按钮状态
display_rs3
viewdata
End Sub

Public Sub viewdata()
Dim i As Integer
Dim sql As String
Dim rsmy As New ADODB.Recordset
If show_arch = True And addinfo = False Then    '通过按钮进入form1时
'显示基本资料部分
   Combo1.Enabled = False                       '禁止使用combo控件
   Combo2.Enabled = False
   Combo3.Enabled = False
   Combo4.Enabled = False
   Combo5.Enabled = False
   If Not IsNull(rs3.Fields(2)) Then Combo1.Text = rs3.Fields(2) Else Combo1.Text = ""           '隶属部门
   If Not IsNull(rs3.Fields(4)) Then Combo2.Text = rs3.Fields(4) Else Combo2.Text = ""           '性别
   If Not IsNull(rs3.Fields(9)) Then Combo3.Text = rs3.Fields(9) Else Combo3.Text = ""           '婚姻状况
   If Not IsNull(rs3.Fields(14)) Then Combo4.Text = rs3.Fields(14) Else Combo4.Text = ""         '职工类型
   If Not IsNull(rs3.Fields(20)) Then Combo5.Text = rs3.Fields(20) Else Combo5.Text = ""         '工资类别
   For i = 0 To 22
      If i = 4 Or i = 15 Or i = 16 Then
         Text3(i) = Format(rs3.Fields(i + 1), "yyyy-mm-dd")
      ElseIf Not IsNull(rs3.Fields(i + 1)) Then  '注意程序中text控件与数据库字段的对应
         Text3(i) = rs3.Fields(i + 1)
      Else
         Text3(i) = ""
      End If
   Next i
showgrid
Else                                          '通过菜单进入form1,进入添加信息状态，combo控件可用
   Combo1.Text = ""
   Combo2.Text = ""
   Combo3.Text = ""
   Combo4.Text = ""
   Combo5.Text = ""
showgrid
With DataGrid1
   .AllowAddNew = True
   .AllowArrows = True
   .AllowUpdate = True
End With
With DataGrid2
   .AllowAddNew = True
   .AllowArrows = True
   .AllowUpdate = True
End With
With DataGrid3
   .AllowAddNew = True
   .AllowArrows = True
   .AllowUpdate = True
End With
With DataGrid4
   .AllowAddNew = True
   .AllowArrows = True
   .AllowUpdate = True
End With
With DataGrid5
   .AllowAddNew = True
   .AllowArrows = True
   .AllowUpdate = True
End With
With DataGrid6
   .AllowAddNew = True
   .AllowArrows = True
   .AllowUpdate = True
End With
End If
End Sub


Public Sub setcmd()
'以下标记按钮状态
If cmd_states = True Then             '第一种按钮状态
   cmdmodify.Enabled = True           '修改职工信息按钮可用
   cmddel.Enabled = True              '删除职工档案按钮可用
   cmdsave.Enabled = False            '保存信息按钮不可用
   cmdcancel.Enabled = False          '放弃保存按钮不可用
   cmdexit.Enabled = True             '退出按钮可用
Else                                  '第二种按钮状态
   cmdmodify.Enabled = False          '修改职工信息按钮不可用
   cmddel.Enabled = False             '删除职工档案按钮不可用
   cmdsave.Enabled = True             '保存信息按钮可用
   cmdcancel.Enabled = True           '放弃保存按钮可用
   cmdexit.Enabled = False            '退出按钮不可用
End If
End Sub


'此部分程序用来初始化combo控件
Public Sub display_rs3()
Dim sql As String
Dim i As Integer
sql = "select * from 基本档案 where 基本档案.employee_NO=" & "'" & select_row & "'"
cmd.ActiveConnection = conn
cmd.CommandText = sql
Set rs3 = cmd.Execute
If show_arch = True And addinfo = False Then
   Text1.Text = rs3.Fields(1)
   Text2.Text = rs3.Fields(0)
Else
   Text1.Text = ""
   Text2.Text = ""
End If
        Combo2.AddItem "男"
        Combo2.AddItem "女"
        sql = "select * from 部门信息"       'combo控件添加部门
        rsview.CursorLocation = adUseClient
        rsview.Open sql, conn, adOpenKeyset, adLockPessimistic
        For i = 1 To rsview.RecordCount
           Combo1.AddItem rsview.Fields(0)
           rsview.MoveNext
        Next i
        rsview.Close
        sql = "select * from 职工类型"       'combo控件添加职工类型
        rsview.CursorLocation = adUseClient
        rsview.Open sql, conn, adOpenKeyset, adLockPessimistic
        For i = 1 To rsview.RecordCount
            Combo4.AddItem rsview.Fields(0)
            rsview.MoveNext
        Next i
        rsview.MoveFirst
        For i = 1 To rsview.RecordCount      'combo控件添加工资类型
            Combo5.AddItem rsview.Fields(1)
            rsview.MoveNext
        Next i
        Set rsview = Nothing
        Combo3.AddItem "已婚"
        Combo3.AddItem "未婚"
End Sub

Public Sub showgrid()
Dim sql As String
'If show_arch = True And addinfo = False Then
'以下为显示家庭成员部分
   sql = "select * from 家庭成员 where 家庭成员.employee_NO =" & "'" & select_row & "'"
   rs_grid1.CursorLocation = adUseClient
   rs_grid1.Open sql, conn, adOpenKeyset, adLockPessimistic
   Set DataGrid1.DataSource = rs_grid1
   DataGrid1.AllowAddNew = False               '此时不可以修改记录
   DataGrid1.AllowDelete = False
   DataGrid1.AllowUpdate = False
   DataGrid1.Columns(7).Visible = False
'以下为显示工作记录部分
   sql = "select * from 工作记录 where 工作记录.employee_NO =" & "'" & select_row & "'"
   rs_grid2.CursorLocation = adUseClient
   rs_grid2.Open sql, conn, adOpenKeyset, adLockPessimistic
   Set DataGrid2.DataSource = rs_grid2
   DataGrid2.AllowAddNew = False               '此时不可以修改记录
   DataGrid2.AllowDelete = False
   DataGrid2.AllowUpdate = False
   DataGrid2.Columns(5).Visible = False
'以下为显示奖惩记录部分
   sql = "select * from 奖惩记录 where 奖惩记录.employee_NO =" & "'" & select_row & "'"
   rs_grid3.CursorLocation = adUseClient
   rs_grid3.Open sql, conn, adOpenKeyset, adLockPessimistic
   Set DataGrid3.DataSource = rs_grid3
   DataGrid3.AllowAddNew = False               '此时不可以修改记录
   DataGrid3.AllowDelete = False
   DataGrid3.AllowUpdate = False
   DataGrid3.Columns(6).Visible = False
'以下为显示职称评定部分
   sql = "select * from 职称评定 where 职称评定.employee_NO =" & "'" & select_row & "'"
   rs_grid4.CursorLocation = adUseClient
   rs_grid4.Open sql, conn, adOpenKeyset, adLockPessimistic
   Set DataGrid4.DataSource = rs_grid4
   DataGrid4.AllowAddNew = False               '此时不可以修改记录
   DataGrid4.AllowDelete = False
   DataGrid4.AllowUpdate = False
   DataGrid4.Columns(7).Visible = False
'以下为显示培训记录部分
   sql = "select * from 培训记录 where 培训记录.employee_NO =" & "'" & select_row & "'"
   rs_grid5.CursorLocation = adUseClient
   rs_grid5.Open sql, conn, adOpenKeyset, adLockPessimistic
   Set DataGrid5.DataSource = rs_grid5
   DataGrid5.AllowAddNew = False               '此时不可以修改记录
   DataGrid5.AllowDelete = False
   DataGrid5.AllowUpdate = False
   DataGrid5.Columns(9).Visible = False
'以下为显示变动记录部分
   sql = "select * from 调动记录 where 调动记录.employee_NO =" & "'" & select_row & "'"
   rs_grid6.CursorLocation = adUseClient
   rs_grid6.Open sql, conn, adOpenKeyset, adLockPessimistic
   Set DataGrid6.DataSource = rs_grid6
   DataGrid6.AllowAddNew = False               '此时不可以修改记录
   DataGrid6.AllowDelete = False
   DataGrid6.AllowUpdate = False
   DataGrid6.Columns(6).Visible = False
'End If
End Sub

Private Sub Form_Unload(Cancel As Integer)
rs3.Close
rs_grid1.Close
rs_grid2.Close
rs_grid3.Close
rs_grid4.Close
rs_grid5.Close
rs_grid6.Close
End Sub