Option Explicit
Dim rs As ADODB.Recordset
Dim SQL As String
Dim msg As String
Dim Index As Integer
Dim flag As String
Private Sub CmdExit_Click()
If flag = "Modify" Then
If MsgBox("保存当前记录的变化吗? ", vbOKCancel + vbExclamation, "警告") = vbOK Then
Call CmdSave_Click
End If
End If
Unload Me
End Sub
Private Sub Form_Load()
CboQuery.Clear
SQL = " select * from 部门信息表"
Set rs = SelectSQL(SQL, msg)
If rs.RecordCount = 0 Then
MsgBox ("请先建立部门信息!")
Else
Do While Not rs.BOF And Not rs.EOF
Me.CboQuery.AddItem (rs.Fields("部门代码") & rs.Fields("部门名称"))
rs.MoveNext
Loop
Me.CboQuery.ListIndex = 0
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
Call ControlEnable(False)
End Sub
Private Sub ControlEnable(flag As Boolean)
Me.textDeNo.Enabled = flag
Me.textDeName.Enabled = flag
Me.textLeName.Enabled = flag
Me.textAddress.Enabled = flag
Me.textPhone.Enabled = flag
Me.textJian.Enabled = flag
End Sub
Private Sub ShowData()
If rs.RecordCount <> 0 Then
Me.textDeNo.Text = rs.Fields("部门代码")
Me.textDeName.Text = rs.Fields("部门名称")
Me.textLeName.Text = rs.Fields("领导名称")
Me.textAddress.Text = rs.Fields("联系地址")
Me.textPhone.Text = rs.Fields("联系电话")
Me.textJian.Text = rs.Fields("简介")
End If
End Sub
Private Sub CmdAdd_Click()
Call ControlClear
Call ControlEnable(True)
flag = "Add"
CmdAdd.Enabled = False: CmdModify.Enabled = False: CmdDelete.Enabled = False
CmdCancel.Enabled = True: CmdSave.Enabled = True
End Sub
Private Sub ControlClear()
Me.textDeNo.Text = ""
Me.textDeName.Text = ""
Me.textLeName.Text = ""
Me.textAddress.Text = ""
Me.textPhone.Text = ""
Me.textJian.Text = ""
End Sub
Private Sub CmdCancel_Click()
Call ShowData
Call ControlEnable(False)
CmdAdd.Enabled = True: CmdModify.Enabled = True: CmdDelete.Enabled = True
CmdSave.Enabled = False: CmdCancel.Enabled = False
End Sub
Private Sub CmdModify_Click()
If rs.RecordCount > 0 Then
Me.textDeNo.Enabled = False
Call ControlEnable(True)
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
Call ControlClear
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
For Index = 0 To 2
Call setData
Next Index
Else
Exit Sub
End If
ElseIf flag = "Add" Then
rs.AddNew
Call setData
End If
rs.Update
CmdAdd.Enabled = True: CmdModify.Enabled = True: CmdDelete.Enabled = True
CmdCancel.Enabled = False: CmdSave.Enabled = False
If flag = "Add" Then
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(textDeNo.Text) = "" Then
msgt = "部门代码为空; "
ElseIf Not Len(Trim(textDeNo.Text)) = 4 Then
msgt = "部门代码不是4位; "
ElseIf Trim(textDeName.Text) = "" Then
msgt = msgt & "部门名称为空; "
ElseIf Trim(textLeName.Text) = "" Then
msgt = msgt & "领导姓名为空; "
End If
If Not msgt = "" Then
MsgBox (msgt)
CheckData = False
Exit Function
End If
SQL = " select * from 部门信息表 where 部门代码='" & Trim(textDeNo.Text) & "'"
Set rst = SelectSQL(SQL, msg)
If flag = "Add" And rst.RecordCount > 0 Then
MsgBox ("该信息已经存在,重复添加!")
rst.Close
CheckData = False
Exit Function
End If
CheckData = True
End Function
Private Sub setData()
rs.Fields("部门代码") = Me.textDeNo.Text
rs.Fields("部门名称") = Me.textDeName.Text
rs.Fields("领导姓名") = Me.textLeName.Text
rs.Fields("联系地址") = Me.textAddress.Text
rs.Fields("联系电话") = Me.textPhone.Text
rs.Fields("简介") = Me.textJian.Text
End Sub
Dim rs As ADODB.Recordset
Dim SQL As String
Dim msg As String
Dim Index As Integer
Dim flag As String
Private Sub CmdExit_Click()
If flag = "Modify" Then
If MsgBox("保存当前记录的变化吗? ", vbOKCancel + vbExclamation, "警告") = vbOK Then
Call CmdSave_Click
End If
End If
Unload Me
End Sub
Private Sub Form_Load()
CboQuery.Clear
SQL = " select * from 部门信息表"
Set rs = SelectSQL(SQL, msg)
If rs.RecordCount = 0 Then
MsgBox ("请先建立部门信息!")
Else
Do While Not rs.BOF And Not rs.EOF
Me.CboQuery.AddItem (rs.Fields("部门代码") & rs.Fields("部门名称"))
rs.MoveNext
Loop
Me.CboQuery.ListIndex = 0
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
Call ControlEnable(False)
End Sub
Private Sub ControlEnable(flag As Boolean)
Me.textDeNo.Enabled = flag
Me.textDeName.Enabled = flag
Me.textLeName.Enabled = flag
Me.textAddress.Enabled = flag
Me.textPhone.Enabled = flag
Me.textJian.Enabled = flag
End Sub
Private Sub ShowData()
If rs.RecordCount <> 0 Then
Me.textDeNo.Text = rs.Fields("部门代码")
Me.textDeName.Text = rs.Fields("部门名称")
Me.textLeName.Text = rs.Fields("领导名称")
Me.textAddress.Text = rs.Fields("联系地址")
Me.textPhone.Text = rs.Fields("联系电话")
Me.textJian.Text = rs.Fields("简介")
End If
End Sub
Private Sub CmdAdd_Click()
Call ControlClear
Call ControlEnable(True)
flag = "Add"
CmdAdd.Enabled = False: CmdModify.Enabled = False: CmdDelete.Enabled = False
CmdCancel.Enabled = True: CmdSave.Enabled = True
End Sub
Private Sub ControlClear()
Me.textDeNo.Text = ""
Me.textDeName.Text = ""
Me.textLeName.Text = ""
Me.textAddress.Text = ""
Me.textPhone.Text = ""
Me.textJian.Text = ""
End Sub
Private Sub CmdCancel_Click()
Call ShowData
Call ControlEnable(False)
CmdAdd.Enabled = True: CmdModify.Enabled = True: CmdDelete.Enabled = True
CmdSave.Enabled = False: CmdCancel.Enabled = False
End Sub
Private Sub CmdModify_Click()
If rs.RecordCount > 0 Then
Me.textDeNo.Enabled = False
Call ControlEnable(True)
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
Call ControlClear
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
For Index = 0 To 2
Call setData
Next Index
Else
Exit Sub
End If
ElseIf flag = "Add" Then
rs.AddNew
Call setData
End If
rs.Update
CmdAdd.Enabled = True: CmdModify.Enabled = True: CmdDelete.Enabled = True
CmdCancel.Enabled = False: CmdSave.Enabled = False
If flag = "Add" Then
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(textDeNo.Text) = "" Then
msgt = "部门代码为空; "
ElseIf Not Len(Trim(textDeNo.Text)) = 4 Then
msgt = "部门代码不是4位; "
ElseIf Trim(textDeName.Text) = "" Then
msgt = msgt & "部门名称为空; "
ElseIf Trim(textLeName.Text) = "" Then
msgt = msgt & "领导姓名为空; "
End If
If Not msgt = "" Then
MsgBox (msgt)
CheckData = False
Exit Function
End If
SQL = " select * from 部门信息表 where 部门代码='" & Trim(textDeNo.Text) & "'"
Set rst = SelectSQL(SQL, msg)
If flag = "Add" And rst.RecordCount > 0 Then
MsgBox ("该信息已经存在,重复添加!")
rst.Close
CheckData = False
Exit Function
End If
CheckData = True
End Function
Private Sub setData()
rs.Fields("部门代码") = Me.textDeNo.Text
rs.Fields("部门名称") = Me.textDeName.Text
rs.Fields("领导姓名") = Me.textLeName.Text
rs.Fields("联系地址") = Me.textAddress.Text
rs.Fields("联系电话") = Me.textPhone.Text
rs.Fields("简介") = Me.textJian.Text
End Sub