mainfrm.frm

档案管理

Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit
Dim row_num As Integer               '表格的总行数
Dim col_num As Integer               '表格的总列数
Dim gridrow As Integer
Dim mycmd As New ADODB.Command
Dim show_all As Boolean
Const high = 350                     '表格每一行的高度

Private Sub add_user_Click()
Form4.Show
End Sub

Private Sub archive_managent_Click()
select_row = ""
addinfo = True
show_arch = False
cmd_states = False
Form1.Show
End Sub

Private Sub byname_Click()
Dim sql As String
Dim name As String
On Error GoTo findnameerror
show_all = False
name = Trim(InputBox$("请输入要查找的员工姓名", "查询姓名"))
If name <> "" Then
   rs1.Close
   sql = "select employee_NO,employee_Name,employee_unit from 基本档案 where employee_Name='" & name & "'"
   rs1.CursorLocation = adUseClient
   rs1.Open sql, conn, adOpenKeyset, adLockPessimistic
   'rs1.MoveFirst
   row_num = rs1.RecordCount                            '数据总条数
   col_num = 3                                          '显示三列，即编号、姓名、部门
   setgrid                                              '设置表格
   setgridhead                                          '设置表头
If rs1.RecordCount > 0 Then
   clear_grid
   MSFlexGrid1.Clear
   setgridhead
   gridrow = 0
   Do Until rs1.EOF
      displaygriddata
   Loop
   MSFlexGrid1.TopRow = 1
Else
   MsgBox "没有任何相关纪录", vbOKOnly + vbExclamation, ""
End If
Exit Sub
Else
   MsgBox "姓名不能为空！", vbOKOnly + vbExclamation, ""
   Exit Sub
End If
findnameerror:
If Err.Number <> 0 Then
   MsgBox Err.Description
End If
End Sub

Private Sub bynum_Click()
Dim sql As String
Dim num As String
On Error GoTo findnumerror
show_all = False
num = Trim(InputBox$("请输入要查找的员工编号", "查询编号"))
If num <> "" Then
   rs1.Close
   sql = "select employee_NO,employee_Name,employee_unit from 基本档案 where employee_NO='" & num & "'"
   rs1.CursorLocation = adUseClient
   rs1.Open sql, conn, adOpenKeyset, adLockPessimistic
   'rs1.MoveFirst
   row_num = rs1.RecordCount                            '数据总条数
   col_num = 3                                          '显示三列，即编号、姓名、部门
   setgrid                                              '设置表格
   setgridhead                                          '设置表头
If rs1.RecordCount > 0 Then
   clear_grid
   MSFlexGrid1.Clear
   setgridhead
   gridrow = 0
   Do Until rs1.EOF
      displaygriddata
   Loop
   MSFlexGrid1.TopRow = 1
Else
   MsgBox "没有任何相关纪录", vbOKOnly + vbExclamation, ""
End If
Exit Sub
Else
   MsgBox "编号不能为空！", vbOKOnly + vbExclamation, ""
   Exit Sub
End If
findnumerror:
If Err.Number <> 0 Then
   MsgBox Err.Description
End If

End Sub

Private Sub byunit_Click()
Dim sql As String
Dim unit As String
On Error GoTo finduniterror
show_all = False
unit = Trim(InputBox$("请输入要查找的员工部门", "按部门查询"))
If unit <> "" Then
   rs1.Close
   sql = "select employee_NO,employee_Name,employee_unit from 基本档案 where employee_unit='" & unit & "'"
   rs1.CursorLocation = adUseClient
   rs1.Open sql, conn, adOpenKeyset, adLockPessimistic
   'rs1.MoveFirst
   row_num = rs1.RecordCount                            '数据总条数
   col_num = 3                                          '显示三列，即编号、姓名、部门
   setgrid                                              '设置表格
   setgridhead                                          '设置表头
If rs1.RecordCount > 0 Then
   clear_grid
   MSFlexGrid1.Clear
   setgridhead
   gridrow = 0
   Do Until rs1.EOF
      displaygriddata
   Loop
   MSFlexGrid1.TopRow = 1
Else
   MsgBox "没有任何相关纪录", vbOKOnly + vbExclamation, ""
End If
Exit Sub
Else
   MsgBox "部门名称不能为空！", vbOKOnly + vbExclamation, ""
   Exit Sub
End If
finduniterror:
If Err.Number <> 0 Then
   MsgBox Err.Description
End If

End Sub

Private Sub change_pwd_Click()
Form5.Show
End Sub

Private Sub Command1_Click()
show_arch = True
addinfo = False
cmd_states = True
Form1.Show
End Sub

Private Sub Command2_Click()
Dim sql As String
On Error GoTo updateerror
If show_all = False Then
rs1.MoveFirst
row_num = rs1.RecordCount                            '数据总条数
col_num = 3
setgrid                                              '设置表格
setgridhead                                          '设置表头
If rs1.RecordCount > 0 Then
   clear_grid
   MSFlexGrid1.Clear
   setgridhead
   gridrow = 0
   Do Until rs1.EOF
      displaygriddata
   Loop
   MSFlexGrid1.TopRow = 1
Else
   MsgBox "没有任何相关纪录", vbOKOnly + vbExclamation, ""
End If
Else
 showall_Click
End If
updateerror:
If Err.Number <> 0 Then
   MsgBox Err.Description
End If
End Sub

Private Sub exitsystem_Click()
conn.Close
Set conn = Nothing
End
End Sub

Private Sub Form_Load()
Dim sql As String
On Error GoTo openerror
addinfo = False
show_arch = False
show_all = True
If username = "guest" Then
   archive_managent.Enabled = False
   add_user.Enabled = False
End If
rs1.CursorLocation = adUseClient
sql = "select 基本档案.employee_NO,基本档案.employee_Name,基本档案.employee_unit " & _
      "from 基本档案 order by 基本档案.employee_NO"   '选择基本档案中的职工编号、姓名、部门用于在表格中的显示
rs1.Open sql, conn, adOpenKeyset, adLockPessimistic
row_num = rs1.RecordCount                            '数据总条数
col_num = 3                                          '显示三列，即编号、姓名、部门
setgrid                                              '设置表格
setgridhead                                          '设置表头
If rs1.RecordCount > 0 Then
   clear_grid
   MSFlexGrid1.Clear
   setgridhead
   gridrow = 0
   Do Until rs1.EOF
      displaygriddata
   Loop
   MSFlexGrid1.TopRow = 1
Else
   MsgBox "没有任何相关纪录", vbOKOnly + vbExclamation, ""
End If
'rs1.Close
openerror:
If Err.Number <> 0 Then
   MsgBox Err.Description
End If
End Sub

Public Sub setgrid()               '设置表格子程序
Dim i As Integer
On Error GoTo setgriderror
MSFlexGrid1.ScrollBars = 2
MSFlexGrid1.FixedCols = 0
MSFlexGrid1.Rows = row_num + 1     '设置总行数
MSFlexGrid1.Cols = col_num         '设置总列数
MSFlexGrid1.SelectionMode = flexSelectionByRow
For i = 0 To row_num
   MSFlexGrid1.RowHeight(i) = high '设置每行高度
Next
MSFlexGrid1.ColWidth(0) = 1500     '第一列宽度
MSFlexGrid1.ColWidth(1) = 2000     '第二列宽度
MSFlexGrid1.ColWidth(2) = 2000     '第三列宽度
Exit Sub
setgriderror:                      '错误处理
If Err.Number <> 0 Then
    MsgBox Err.Description         '显示错误信息
End If
End Sub

Public Sub setgridhead()
On Error GoTo setgridheaderror
MSFlexGrid1.Row = 0
MSFlexGrid1.Col = 0
MSFlexGrid1.Text = "职工编号"       '第0行第0列为“职工编号”
MSFlexGrid1.Col = 1
MSFlexGrid1.Text = "职工姓名"       '第0行第1列为“职工姓名”
MSFlexGrid1.Col = 2
MSFlexGrid1.Text = "所在部门"       '第0行第2列为“所在部门”
Exit Sub
setgridheaderror:
If Err.Number <> 0 Then
    MsgBox Err.Description
End If
End Sub

Public Sub clear_grid()
On Error GoTo clear_grid_error
Dim i As Integer, j As Integer
For i = 1 To row_num
    For j = 0 To col_num - 1
        MSFlexGrid1.Row = i
        MSFlexGrid1.Col = j
        MSFlexGrid1.Text = ""
    Next
Next
clear_grid_error:
If Err.Number <> 0 Then
   MsgBox Err.Description
End If
End Sub

Public Sub displaygriddata()      '显示msflexgrid内容的子程序
On Error GoTo displayerror
gridrow = gridrow + 1
MSFlexGrid1.Row = gridrow
MSFlexGrid1.Col = 0
If Not IsNull(rs1![employee_NO]) Then
   MSFlexGrid1.Text = rs1![employee_NO]
Else
   MSFlexGrid1.Text = ""
End If
                                 '以上为显示当前行第0列的内容
MSFlexGrid1.Col = 1
If Not IsNull(rs1![employee_Name]) Then
   MSFlexGrid1.Text = rs1![employee_Name]
Else
   MSFlexGrid1.Text = ""
End If
                                 '以上为显示当前行第1列的内容
MSFlexGrid1.Col = 2
If Not IsNull(rs1![employee_unit]) Then
   MSFlexGrid1.Text = rs1![employee_unit]
Else
   MSFlexGrid1.Text = ""
End If
                                 '以上为显示当前行第2列的内容
rs1.MoveNext                      '移动到下一条记录
displayerror:
If Err.Number <> 0 Then
   MsgBox Err.Description
End If
End Sub
'当点击Msflexgrid1控件的某一行（即选定某一个员工）时，触发Click事件
Private Sub MSFlexGrid1_Click()
On Error GoTo griderror
Dim getrow As Long
getrow = MSFlexGrid1.Row
If MSFlexGrid1.Rows = 1 Then
   MsgBox "无相关纪录", vbOKOnly + vbExclamation, ""
Else
select_row = MSFlexGrid1.TextMatrix(getrow, 0)
displaybasicdata                '右侧基本资料栏内显示选定职工的详细信息
End If
griderror:
If Err.Number <> 0 Then
   MsgBox Err.Description
End If
End Sub

'显示基本资料子程序，利用command对象，不同于调用Recordset对象的Open方法
Public Sub displaybasicdata()
Dim sql As String
Dim i As Integer
Dim cmd As New ADODB.Command
On Error GoTo displaybasicerror
Set cmd.ActiveConnection = conn
sql = "select * from 基本档案 where 基本档案.employee_NO=" & "'" & select_row & "'"
cmd.CommandText = sql
Set rs2 = cmd.Execute
For i = 0 To 15
  If Not IsNull(rs2.Fields(i)) Then
    Text1(i) = rs2.Fields(i)
  Else
    Text1(i) = ""
  End If
Next
displaybasicerror:
If Err.Number <> 0 Then
   MsgBox Err.Description
End If
End Sub

Private Sub showall_Click()
rs1.Close
show_all = True
Dim sql As String
sql = "select 基本档案.employee_NO,基本档案.employee_Name,基本档案.employee_unit " & _
      "from 基本档案 order by 基本档案.employee_NO"   '选择基本档案中的职工编号、姓名、部门用于在表格中的显示
rs1.Open sql, conn, adOpenKeyset, adLockPessimistic
row_num = rs1.RecordCount                            '数据总条数
col_num = 3                                          '显示三列，即编号、姓名、部门
setgrid                                              '设置表格
setgridhead                                          '设置表头
If rs1.RecordCount > 0 Then
   clear_grid
   MSFlexGrid1.Clear
   setgridhead
   gridrow = 0
   Do Until rs1.EOF
      displaygriddata
   Loop
   MSFlexGrid1.TopRow = 1
Else
   MsgBox "没有任何相关纪录", vbOKOnly + vbExclamation, ""
End If
Exit Sub
End Sub

Private Sub unit_management_Click()
Form2.Show
End Sub