ms access 액세스 콤보박스 서브폼

combobox_sub_form.accdb

1.81MB

'도구-참조-Microsoft ActiveX Data Objents 6.1 Library

'Form_FQ직원현황 VBA

Option Compare Database
Option Explicit

Private Sub subA_select()
    Form_F메인.직원현황ID = Me.직원현황ID
    Form_F메인.사원ID = Me.사원ID
    Form_F메인.부서ID = Me.부서ID
    Form_F메인.fname = Me.firstname
    Form_F메인.lname = Me.lastname
    Form_F메인.points = Me.points
    Form_F메인.Combo부서 = Me.부서
End Sub
Private Sub firstname_Click()
    Call subA_select
End Sub
Private Sub lastname_Click()
    Call subA_select
End Sub
Private Sub points_Click()
    Call subA_select
End Sub
Private Sub 부서_Click()
    Call subA_select
End Sub

 

Option Compare Database
Option Explicit

Private Sub null_check()
    ' Null 체크: IsNull 함수를 사용해야 합니다.
    If IsNull(Me.fname) Or Trim(Me.fname) = "" Then
        MsgBox "fname이 입력되지 않았습니다"
        Me.fname.SetFocus
        Exit Sub
    ElseIf IsNull(Me.lname) Or Trim(Me.lname) = "" Then
        MsgBox "lname이 선택되지 않았습니다"
        Me.lname.SetFocus
        Exit Sub
    ElseIf IsNull(Me.points) Or Trim(Me.points) = "" Then
        MsgBox "points가 입력되지 않았습니다"
        Me.points.SetFocus
        Exit Sub
    ElseIf IsNull(Me.Combo부서) Or Trim(Me.Combo부서) = "" Then
        MsgBox "부서가 선택되지 않았습니다"
        Me.Combo부서.SetFocus
        Exit Sub
    End If
End Sub

Private Sub select_data()
    Dim rs As Object
    
    ' 하위 폼의 Recordset 객체 참조
    Set rs = Me.subA.Form.RecordsetClone
    
    ' 직원현황ID 필드 값으로 레코드 찾기
    rs.FindFirst "직원현황ID = " & Me.직원현황ID
    
    ' 레코드를 찾았는지 확인하고, 레코드를 선택
    If Not rs.EOF Then
        Me.subA.Form.Bookmark = rs.Bookmark
    Else
        MsgBox "해당 직원현황ID를 찾을 수 없습니다.", vbExclamation
    End If
    
    ' 객체 해제
    Set rs = Nothing
    
End Sub


Private Sub form_init()
    Me.직원현황ID = Null
    Me.사원ID = Null
    Me.부서ID = Null
    Me.fname = Null
    Me.lname = Null
    Me.points = Null
    Me.Combo부서 = Null
End Sub

Private Sub Cmd_Delete_Click()
    CurrentDb.Execute "Delete From T사원 Where 사원id=" & 사원ID.Value
    Call form_init
    Me.subA.Requery

End Sub

Private Sub Cmd_Insert_Click()
    Dim newID_T사원 As Integer
    Dim newID_T직원현황 As Integer
    
    'null_check==========================
    ' Null 체크: IsNull 함수를 사용해야 합니다.
    If IsNull(Me.fname) Or Trim(Me.fname) = "" Then
        MsgBox "fname이 입력되지 않았습니다"
        Me.fname.SetFocus
        Exit Sub
    ElseIf IsNull(Me.lname) Or Trim(Me.lname) = "" Then
        MsgBox "lname이 선택되지 않았습니다"
        Me.lname.SetFocus
        Exit Sub
    ElseIf IsNull(Me.points) Or Trim(Me.points) = "" Then
        MsgBox "points가 입력되지 않았습니다"
        Me.points.SetFocus
        Exit Sub
    ElseIf IsNull(Me.Combo부서) Or Trim(Me.Combo부서) = "" Then
        MsgBox "부서가 선택되지 않았습니다"
        Me.Combo부서.SetFocus
        Exit Sub
    End If
    '======================================================
    
    
    CurrentDb.Execute "INSERT INTO T사원(firstname, lastname, points) " & "VALUES ('" & fname.Value & "', '" & lname.Value & "', " & points.Value & ")"
    ' 새로 생성된 T사원.사원ID
    newID_T사원 = Nz(DMax("사원ID", "T사원"), 0)
    CurrentDb.Execute "INSERT INTO T직원현황 (사원ID,부서ID) " & "VALUES ('" & newID_T사원 & "', '" & Combo부서 & "')"
    ' 새로 생성된 ID
    newID_T직원현황 = Nz(DMax("직원현황ID", "T직원현황"), 0)
    
    Me.직원현황ID = newID_T직원현황
    Me.사원ID = newID_T사원
    Me.부서ID = Combo부서
    Me.subA.Requery
    
    Call select_data
'    Dim sql As String
'    Dim newID As Long
'    Dim db As DAO.Database
'    Dim rs As DAO.Recordset
'
'    Set db = CurrentDb()
'
'    ' 레코드 삽입
'    sql = "INSERT INTO T사원(firstname, lastname, points) " & _
'          "VALUES ('" & fname.Value & "', '" & lname.Value & "', " & points.Value & ")"
'    db.Execute sql
'
'    ' 방금 삽입된 ID 가져오기
'    Set rs = db.OpenRecordset("SELECT @@IDENTITY AS newID", dbOpenSnapshot)
'    newID = rs.Fields("newID").Value
'    rs.Close
'
'    ' ID를 폼의 필드에 할당
'    Me.사원ID = newID
'
'    ' 강제로 목록 상자 갱신
'    Call load_data
'
'    Set db = Nothing
'====================================================
'Dim sql As String
'Dim newID As Long
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'
'Set db = CurrentDb()
'
'' 레코드 삽입
'sql = "INSERT INTO T사원(firstname, lastname, points) " & _
'      "VALUES ('" & fname.Value & "', '" & lname.Value & "', " & points.Value & ")"
'db.Execute sql
'
'' 방금 삽입된 ID 가져오기
'Set rs = db.OpenRecordset("SELECT @@IDENTITY AS newID", dbOpenSnapshot)
'newID = rs.Fields("newID").Value
'rs.Close
'
'' ID를 폼의 필드에 할당
'Me.사원ID = newID
'
'' 목록 상자 강제 갱신
''Call load_data
''Me.List_box.Requery
'
'Set db = Nothing
'
'' 방금 추가된 ID로 목록 상자에서 해당 항목 선택
'Dim i As Integer
'For i = 0 To Me.List_box.ListCount - 1
'    If Me.List_box.Column(0, i) = newID Then
'        Me.List_box = Me.List_box.ItemData(i)
'        Exit For
'    End If
'Next i
End Sub



Private Sub Cmd_New_Click()
    Call form_init

End Sub

Private Sub Cmd_Update_Click()
    'null_check==========================
    ' Null 체크: IsNull 함수를 사용해야 합니다.
    If IsNull(Me.fname) Or Trim(Me.fname) = "" Then
        MsgBox "fname이 입력되지 않았습니다"
        Me.fname.SetFocus
        Exit Sub
    ElseIf IsNull(Me.lname) Or Trim(Me.lname) = "" Then
        MsgBox "lname이 선택되지 않았습니다"
        Me.lname.SetFocus
        Exit Sub
    ElseIf IsNull(Me.points) Or Trim(Me.points) = "" Then
        MsgBox "points가 입력되지 않았습니다"
        Me.points.SetFocus
        Exit Sub
    ElseIf IsNull(Me.Combo부서) Or Trim(Me.Combo부서) = "" Then
        MsgBox "부서가 선택되지 않았습니다"
        Me.Combo부서.SetFocus
        Exit Sub
    End If
    '======================================================
    
    CurrentDb.Execute "Update T사원 Set firstname = '" & Me.fname.Value & "',lastname = '" & Me.lname.Value & "',points = '" & Me.points.Value & "' Where 사원ID = " & Me.사원ID.Value
    CurrentDb.Execute "Update T직원현황 Set 사원ID = '" & Me.사원ID.Value & "',부서ID = '" & Me.Combo부서 & "' Where 직원현황id = " & 직원현황ID.Value
    Me.subA.Requery
    
End Sub