IT BUSINESS - 인사관리 프로그램 -

따라해보아요. 지난번꺼랑 이어지는 겁니다.

---

[Private Sub btn등록수정_Click() 소스]

 1239580815_source.txt

Private Sub btn등록수정_Click()
Dim nterm As ListItem
If btn등록수정.Caption = "등록" Then
If txt이름.Text = "" Then
MsgBox "사원명을 입력하세요", vbExclamation, "사원관리"
txt이름.SetFocus
Exit Sub
End If
If txt부서코드.Text = "" Then
MsgBox "부서코드를 입력하세요", vbExclamation, "사원관리"
Me.Combo부서.SetFocus
Exit Sub
End If
On Error GoTo sqlerr
adoCmd.ActiveConnection.BeginTrans
SQL = "insert into 사원대장(이름,부서코드, 직위, 휴대전화, 집전화, 자택주소, 이메일)"
SQL = SQL & "values("
SQL = SQL & "'" & txt이름.Text & "',"
SQL = SQL & "'" & txt부서코드.Text & "',"
SQL = SQL & "'" & txt직위.Text & "',"
SQL = SQL & "'" & txt휴대전화.Text & "',"
SQL = SQL & "'" & txt집전화.Text & "',"
SQL = SQL & "'" & txt자택주소.Text & "',"
SQL = SQL & "'" & txt이메일.Text & "')"
adoCmd.CommandText = SQL
adoCmd.Execute
If Err.Number = 0 Then
adoCmd.ActiveConnection.CommitTrans
MsgBox "입력한 사원이 등록되었습니다.", vbInformation, "사원관리"
SQL = "select * from 사원대장 where 이름 = '" & Me.txt이름.Text & "'"
sqlQ (SQL)
If Rs.RecordCount > 0 Then
Rs.MoveFirst
Set nterm = Me.ListView사원대장.ListItems.Add(, , Rs.Fields(0))
nterm.SubItems(1) = Rs!이름
nterm.SubItems(2) = Rs!직위
End If
Else
adoCmd.ActiveConnection.RollbackTrans
MsgBox "에러가 발생하였습니다" & vbLf & _
Err.Number & vbLf & Err.Description, vbExclamation, "사원관리"

End If
Else
If txt이름.Text = "" Then
MsgBox "사원명을 입력하세요", vbExclamation, "사원관리"
txt이름.SetFocus 'txt부서명.setfocus
Exit Sub
End If
On Error GoTo sqlerr
adoCmd.ActiveConnection.BeginTrans
SQL = "Update 사원대장 set 이름 = '" & txt이름.Text & "',"
SQL = SQL & "직위 = '" & txt직위.Text & "',"
SQL = SQL & "휴대전화 = '" & txt휴대전화.Text & "',"
SQL = SQL & "집전화 = '" & txt집전화.Text & "',"
SQL = SQL & "이메일 = '" & txt이메일.Text & "',"
SQL = SQL & "where id = " & Me.ListView사원대장.SelectedItem.Text
adoCmd.CommandText = SQL
adoCmd.Execute
If Err.Number = 0 Then
adoCmd.ActiveConnection.CommitTrans
MsgBox "사원대장이 수정되었습니다", vbInformation, "사원관리"
Me.ListView사원대장.SelectedItem.SubItems(1) = txt이름.Text
Me.ListView사원대장.SelectedItem.SubItems(2) = txt직위.Text
Else
adoCmd.ActiveConnection.RollbackTrans
MsgBox "에러가 발생하였습니다" & vbLf & _
Err.Number & vbLf & Err.Description, vbExclamation, "사원관리"
End If
End If
'Command_ 를 btn 으로 수정하였습니다.
Me.btn삭제.Enabled = True
Me.btn등록수정.Enabled = False
Me.btn취소.Enabled = False
Me.btn신규.Enabled = True
Exit Sub
sqlerr:
MsgBox Err.Description, vbInformation, "사원관리"
End Sub

▣ 전체 소스 다운로드

 1239580815_사원관리소스.txt

CODE ()는 여기에 있습니다. >>> 만약 안된다면, 디버깅 하세요 ㅋㅋㅋㅋㅋㅋ

Private Sub btn등록수정_Click()
Dim nterm As ListItem
If btn등록수정.Caption = "등록" Then
If txt이름.Text = "" Then
MsgBox "사원명을 입력하세요", vbExclamation, "사원관리"
txt이름.SetFocus
Exit Sub
End If
If txt부서코드.Text = "" Then
MsgBox "부서코드를 입력하세요", vbExclamation, "사원관리"
Me.Combo부서.SetFocus
Exit Sub
End If
On Error GoTo sqlERR
adoCmd.ActiveConnection.BeginTrans
SQL = "insert into 사원대장(이름,부서코드, 직위, 휴대전화, 집전화, 자택주소, 이메일)"
SQL = SQL & "values("
SQL = SQL & "'" & txt이름.Text & "',"
SQL = SQL & "'" & txt부서코드.Text & "',"
SQL = SQL & "'" & txt직위.Text & "',"
SQL = SQL & "'" & txt휴대전화.Text & "',"
SQL = SQL & "'" & txt집전화.Text & "',"
SQL = SQL & "'" & txt자택주소.Text & "',"
SQL = SQL & "'" & txt이메일.Text & "')"
adoCmd.CommandText = SQL
adoCmd.Execute
If Err.Number = 0 Then
adoCmd.ActiveConnection.CommitTrans
MsgBox "입력한 사원이 등록되었습니다.", vbInformation, "사원관리"
SQL = "select * from 사원대장 where 이름 = '" & Me.txt이름.Text & "'"
sqlQ (SQL)
If Rs.RecordCount > 0 Then
Rs.MoveFirst
Set nterm = Me.ListView사원대장.ListItems.Add(, , Rs.Fields(0))
nterm.SubItems(1) = Rs!이름
nterm.SubItems(2) = Rs!직위
End If
Else
adoCmd.ActiveConnection.RollbackTrans
MsgBox "에러가 발생하였습니다" & vbLf & _
Err.Number & vbLf & Err.Description, vbExclamation, "사원관리"

End If
Else
If txt이름.Text = "" Then
MsgBox "사원명을 입력하세요", vbExclamation, "사원관리"
txt이름.SetFocus 'txt부서명.setfocus
Exit Sub
End If
On Error GoTo sqlERR
adoCmd.ActiveConnection.BeginTrans
SQL = "Update 사원대장 set 이름 = '" & txt이름.Text & "',"
SQL = SQL & "직위 = '" & txt직위.Text & "',"
SQL = SQL & "휴대전화 = '" & txt휴대전화.Text & "',"
SQL = SQL & "집전화 = '" & txt집전화.Text & "',"
SQL = SQL & "이메일 = '" & txt이메일.Text & "',"
SQL = SQL & "where id = " & Me.ListView사원대장.SelectedItem.Text
adoCmd.CommandText = SQL
adoCmd.Execute
If Err.Number = 0 Then
adoCmd.ActiveConnection.CommitTrans
MsgBox "사원대장이 수정되었습니다", vbInformation, "사원관리"
Me.ListView사원대장.SelectedItem.SubItems(1) = txt이름.Text
Me.ListView사원대장.SelectedItem.SubItems(2) = txt직위.Text
Else
adoCmd.ActiveConnection.RollbackTrans
MsgBox "에러가 발생하였습니다" & vbLf & _
Err.Number & vbLf & Err.Description, vbExclamation, "사원관리"
End If
End If
'Command_ 를 btn 으로 수정하였습니다.
Me.btn삭제.Enabled = True
Me.btn등록수정.Enabled = False
Me.btn취소.Enabled = False
Me.btn신규.Enabled = True
Exit Sub
sqlERR:
MsgBox Err.Description, vbInformation, "사원관리"
End Sub

Private Sub btn삭제_Click()
If MsgBox("해당 사원을 삭제하시겠습니까", vbYesNo, "사원삭제") = vbYes Then
On Error GoTo sqlERR
adoCmd.ActiveConnection.BeginTrans
SQL = "DELETE FROM 사원대장 where ID = " & Me.ListView사원대장.SelectedItem.Text
adoCmd.CommandText = SQL
adoCmd.Execute
If Err.Number = 0 Then
adoCmd.ActiveConnection.CommitTrans
MsgBox "사원이 삭제되었습니다", vbInformation, "사원삭제"
ListView사원대장.ListItems.Remove Me.ListView사원대장.SelectedItem.Index
Else
adoCmd.ActiveConnection.RollbackTrans
MsgBox "에러가 발생하였습니다" & vbLf & _
Err.Number & vbLf & Err.Description, vbExclamation, "사원삭제"
End If
End If
Exit Sub
sqlERR:
MsgBox Err.Description, vbInformation, "사원삭제"
End Sub

Private Sub btn신규_Click()
Me.txt이름.Text = ""
Me.txt직위.Text = ""
Me.txt휴대전화.Text = ""
Me.txt집전화.Text = ""
Me.txt이메일.Text = ""
Me.txt자택주소.Text = ""
Me.txt이름.SetFocus

'Command_를 btn 으로 수정하였습니다
Me.btn삭제.Enabled = False
Me.btn등록수정.Caption = "등록"
Me.btn취소.Enabled = True
Me.btn신규.Enabled = False
End Sub

Private Sub btn취소_Click()
Me.btn등록수정.Enabled = False
Me.btn취소.Enabled = False
Me.btn신규.Enabled = True
Me.btn삭제.Enabled = True
End Sub

Private Sub Combo부서_Click()
DataLoading사원
End Sub

Private Sub Form_Load()
Dim clmX1 As ColumnHeader
Set clmX1 = ListView사원대장.ColumnHeaders.Add(, , "NO", 0)
Set clmX1 = ListView사원대장.ColumnHeaders.Add(, , "이름", 120)
Set clmX1 = ListView사원대장.ColumnHeaders.Add(, , "직위", 240)
ListView사원대장.View = lvwReport
DataLoading부서 '프로시저를 호출 합니다.
End Sub

Sub DataLoading부서()
Dim nterm As ListItem
Dim SQL As String
SQL = "select * from 부서"
sqlQ (SQL)
If Rs.RecordCount > 0 Then
Rs.MoveFirst
Do While Not Rs.EOF
Me.Combo부서.AddItem Rs!부서명
Rs.MoveNext
Loop
End If
End Sub

Sub DataLoading사원()
Dim nterm As ListItem
Dim SQL As String
If txt부서코드.Text = "" Then
SQL = "select * from 부서 where 부서명 = '" & Me.Combo부서.Text & "'"
sqlQ (SQL)
If Rs.RecordCount > 0 Then
Rs.MoveFirst
txt부서코드.Text = Rs!ID
End If
Me.ListView사원대장.ListItems.Clear
SQL = "select A.ID, A.이름, A.직위,B.ID FROM 사원대장 AS A, 부서 AS B" & _
"Where B.ID = A.부서코드 AND B.부서명 = '" & Me.Combo부서.Text & "'"
sqlQ (SQL)
If Rs.RecordCount > 0 Then
Rs.MoveFirst
Do While Not Rs.EOF
Set nterm = Me.ListView사원대장.ListItems.Add(, , Rs.Fields(0))
nterm.SubItems(1) = Rs.Fields(1)
nterm.SubItems(2) = Rs.Fields(2)
Rs.MoveNext
Loop
End If
End Sub

Private Sub ListView사원대장_DblClick()
DataLoading사원정보
End Sub

Sub DataLoading사원정보()
Dim SQL As String
SQL = "SELECT * FROM 사원대장 where id = " & Me.ListView사원대장.SelectedItem.Text
sqlQ (SQL)
If Rs.RecordCount > 0 Then
Rs.MoveFirst
Me.txt이름.Text = Rs!이름
Me.txt직위.Text = Rs!직위
Me.txt휴대전화.Text = Rs!휴대전화
Me.txt집전화.Text = Rs!집전화
Me.txt자택주소.Text = Rs!자택주소
Me.txt이메일.Text = Rs!이메일
Me.btn등록수정.Caption = "수정"
Me.btn삭제.Enabled = True
Me.btn신규.Enabled = False
Me.btn등록수정.Enabled = True
Me.btn취소.Enabled = True
'Command_ 를 btn 으로 수정했음
End If
End Sub

---

중간고사는 ERD 그리기 > 아마도 PC로 할 예정

ERD 그리는 방법으로 합니다.

시간은 1시간이면 되겠지요.

시험은 다음 주 월요일(2009년 04월 20일)