Dim Index As Integer
Dim flag As String                              '判斷是新增記錄還是修改記錄
Private Sub Form_Load()
    CboQuery.Clear                              '清空ComboBox
    '查詢職務信息
    SQL = " select * from 職務信息表"
    Set rs = SelectSQL(SQL, msg)
    If rs.RecordCount = 0 Then                  '如果沒有職務信息
        MsgBox ("請先建立職務信息！")
    Else
        Do While Not rs.BOF And Not rs.EOF
            '添加到ComboBox列表
            Me.CboQuery.AddItem (rs.Fields("職務代碼") & rs.Fields("職務名稱"))
            rs.MoveNext                         '指向下一條記錄
        Loop
        Me.CboQuery.ListIndex = 0               '默認ComboBox
        rs.Close
    End If
    CboGzID.Clear                               '清空ComboBox
    '查詢工資標準信息
    SQL = " select * from 工資標準信息表"
    Set rs = SelectSQL(SQL, msg)
    If rs.RecordCount = 0 Then                  '如果沒有工資等級信息
        MsgBox ("請先建立工資等級信息！")
    Else
        Do While Not rs.BOF And Not rs.EOF
            '添加到ComboBox列表
            Me.CboGzID.AddItem (rs.Fields("工資等級ID") & rs.Fields("等級名稱"))
            rs.MoveNext                         '指向下一條記錄
        Loop
        Me.CboGzID.ListIndex = 0                '默認ComboBox
        rs.Close
    End If
    Call LoadData                               '裝載數據
    Call ShowData                               '顯示數據
End Sub
Private Sub LoadData()
'裝載數據
    Dim colName As String
    Dim strWhere As String
    '得到查詢條件
    colName = Left(Trim(CboQuery.Text), 4)
    '構建條件
    strWhere = " where 職務代碼='" & colName & "'"
    Set rs = Nothing
    '查詢職務信息
    SQL = "SELECT * FROM 職務信息表 " & strWhere & " ORDER BY 職務代碼 "
    Set rs = SelectSQL(SQL, msg)
    If rs.RecordCount = 0 Then                  '如果不存在記錄
        '編輯控件可用性
        CmdAdd.Enabled = True: CmdModify.Enabled = False: CmdDelete.Enabled = False
        CmdCancel.Enabled = False: CmdSave.Enabled = False
    Else
         '編輯控件可用性
        CmdAdd.Enabled = True: CmdModify.Enabled = True: CmdDelete.Enabled = True
        CmdCancel.Enabled = False: CmdSave.Enabled = False
    End If
    '設置控件Enable值
    Call ControlEnable(False)
End Sub
Private Sub ControlEnable(flag As Boolean)
'設置控件的Enable值
    Me.txtZwNo.Enabled = flag
    Me.txtZwName.Enabled = flag
    Me.CboGzID.Enabled = flag
    Me.txtGZSH.Enabled = flag
    Me.txtGZXia.Enabled = flag
    Me.txtJian.Enabled = flag
End Sub
Private Sub ShowData()
'在控件中顯示數據
    If rs.RecordCount <> 0 Then                 '如果存在記錄
        '為控件賦值
        Me.txtZwNo.Text = rs.Fields("職務代碼")
        Me.txtZwName.Text = rs.Fields("職務名稱")
        Me.CboGzID.Text = rs.Fields("工資等級ID")
        Me.txtGZSH.Text = rs.Fields("工資上限")
        Me.txtGZXia.Text = rs.Fields("工資下限")
        Me.txtJian.Text = rs.Fields("簡介")
    End If
End Sub
Private Sub CmdAdd_Click()
'添加操作
    '所有控件重置
    Call ControlClear
    '設置控件Enable值為可用
    Call ControlEnable(True)
    '設置標志flag，表示所進行的操作為添加
    flag = "Add"
    '添加、修改、刪除按鈕不可用，取消、保存按鈕可用
    CmdAdd.Enabled = False: CmdModify.Enabled = False: CmdDelete.Enabled = False
    CmdCancel.Enabled = True: CmdSave.Enabled = True
End Sub
Private Sub ControlClear()
'重置控件
    Me.txtZwNo.Text = ""
    Me.txtZwName.Text = ""
    Me.CboGzID.ListIndex = 0
    Me.txtGZSH.Text = ""
    Me.txtGZXia.Text = ""
    Me.txtJian.Text = ""
End Sub
Private Sub CmdModify_Click()
'修改操作
    If rs.RecordCount > 0 Then                  '如果存在記錄
        '部門代碼不可以修改,其他控件可用
        Me.txtZwNo.Enabled = False
        '設置控件Enable值
        Call ControlEnable(True)
        '設置標志flag，表示所進行的操作為修改
        flag = "Modify"
        '添加、修改、刪除按鈕不可用，取消、保存按鈕可用
        CmdAdd.Enabled = False: CmdModify.Enabled = False: CmdDelete.Enabled = False
        CmdCancel.Enabled = True: CmdSave.Enabled = True
    Else
         MsgBox ("沒有可以修改的數據!")
    End If
End Sub
Private Sub CmdDelete_Click()
'刪除操作
    On Error GoTo ErrMsg                        '錯誤處理
    If rs.RecordCount > 0 Then                  '如果存在記錄
        msg = MsgBox("刪除該條記錄嗎?", vbYesNo)
        If msg = vbYes Then                     '如果選擇“是”
            rs.Delete                           '刪除記錄
            Call Form_Load                      '重新裝載數據、初始化Combobox
            '清空控件
            Call ControlClear
            '設置控件Enable值為不可用
            Call ControlEnable(False)
            '添加、刪除按鈕可用，修改、取消、保存按鈕不可用
            CmdAdd.Enabled = True: CmdModify.Enabled = False: CmdDelete.Enabled = True
            CmdSave.Enabled = False: CmdCancel.Enabled = False
            MsgBox ("成功刪除的數據!")
        End If
    Else
        MsgBox ("沒有可刪除的數據!")
    End If
    Exit Sub
ErrMsg:                                         '報告出錯信息
    MsgBox Err.Description, vbExclamation, "出錯"
End Sub
Private Sub CmdSave_Click()
'保存操作
    On Error GoTo ErrMsg                        '錯誤處理
    If Not CheckData Then Exit Sub              '如果數據不合法就退出
    If flag = "Modify" Then                     '如果是修改數據
        msg = MsgBox("您確實要修改這條數據嗎?", vbYesNo)
        If msg = vbYes Then
            Call setData                        '設置數據
        Else
            Exit Sub
        End If
    ElseIf flag = "Add" Then                    '如果是添加新數據
        rs.AddNew
        Call setData                            '設置數據
    End If
    rs.Update                                   '更新數據
    CmdModify.Enabled = True: CmdDelete.Enabled = True: CmdAdd.Enabled = True
    CmdSave.Enabled = False: CmdCancel.Enabled = False
    If flag = "Add" Then                        '如果標志是“Add”
        MsgBox ("成功添加數據!")
    Else
        MsgBox ("成功更新數據!")
    End If
    Call Form_Load                              '重新裝載數據
    Exit Sub
ErrMsg:                                         '報告出錯信息
    MsgBox Err.Description, vbExclamation, "出錯"
End Sub
Private Function CheckData() As Boolean
'檢查數據的合法性
    Dim rst As ADODB.Recordset
    Dim msgt As String
    msgt = ""
    '檢查數據
    If Trim(txtZwNo.Text) = "" Then             '檢查職務代碼是否為空
        msgt = "職務代碼為空； "
    ElseIf Not Len(Trim(txtZwNo.Text)) = 4 Then '檢查職務代碼是否為4位
        msgt = "職務代碼不是4位； "
    ElseIf Trim(txtZwName.Text) = "" Then       '職務名稱是否為空
        msgt = msgt & " 職務名稱為空； "
    ElseIf Trim(CboGzID.Text) = "" Then         '工資等級ID是否為空
        msgt = msgt & " 工資等級ID為空； "
    ElseIf Trim(txtGZSH.Text) = "" Then         '工資上限是否為空
        msgt = msgt & " 工資上限為空； "
    ElseIf Trim(txtGZXia.Text) = "" Then        '工資下限是否為空
        msgt = msgt & " 工資下限為空； "
    End If
    If Not msgt = "" Then                        '如果不為空，給出錯誤提示
        MsgBox (msgt)
        CheckData = False                        '返回False
        Exit Function
    End If
    '檢查唯一性
    SQL = " select * from 職務信息表 where 職務代碼='" & Trim(txtZwNo.Text) & "'"
    Set rst = SelectSQL(SQL, msg)
    '如果是進行添加操作且已經存在該記錄，提示重復添加
    If flag = "Add" And rst.RecordCount > 0 Then
        MsgBox ("該信息已經存在，重復添加!")
        rst.Close
        CheckData = False                       '返回False
        Exit Function
    End If
    CheckData = True                            '合法返回True
End Function
Private Sub setData()
'為字段設置數據
    rs.Fields("職務代碼") = Me.txtZwNo.Text
    rs.Fields("職務名稱") = Me.txtZwName.Text
    rs.Fields("工資等級ID") = Trim(Left(Me.CboGzID.Text, 4))
    rs.Fields("工資上限") = Me.txtGZSH.Text
    rs.Fields("工資下限") = Me.txtGZXia.Text
    rs.Fields("簡介") = Me.txtJian.Text
End Sub
Private Sub cmdCancel_Click()
'取消操作
    Call ShowData                               '重新在控件中顯示信息
    '設置控件Enable值為不可用
    Call ControlEnable(False)
    '修改、刪除、添加按鈕可用,保存和取消按鈕不可用
    CmdAdd.Enabled = True: CmdModify.Enabled = True: CmdDelete.Enabled = True
    CmdSave.Enabled = False: CmdCancel.Enabled = False
End Sub
Private Sub CboQuery_Click()
'在控件中顯示具體的職務信息
    Call LoadData                               '重新裝載數據
    Call ShowData                               '重新顯示數據
End Sub
Private Sub CmdExit_Click()
'退出操作
    人事管理系統.Enabled = True
    Unload Me
End Sub
Private Sub Form_Unload(Cancel As Integer)
'退出操作
    人事管理系統.Enabled = True
    rs.Close
    Unload Me
End Sub