Private Sub cmdU_AED_Click() ' Add/Edit/Delete User
On Error GoTo errMsg
Dim db As Database
Dim rs_acnt_dtls As Recordset
Dim rs_user As Recordset
Dim str_acnt_dtls As String
Dim str_user As String
Dim intExists As Integer
Dim intResponse As Integer
Dim intUserId As Integer
Set db = CurrentDb
If fraU_Operation.Value = 1 Then 'Add User
If Len(cboU_NameAdd & vbNullString) = 0 Then
intResponse = MsgBox("Please enter the User Name.", , "User Details")
cboU_NameAdd.SetFocus
Exit Sub
End If
'Check for duplicate record
str_user = "select * from tblUser where user_name ='" & cboU_NameAdd.Value & "'"
intExists = RecordExists(str_user)
If intExists > 0 Then
intResponse = MsgBox("User already exists.", , "User Details")
cboU_NameAdd = ""
cboU_NameAdd.SetFocus
Else
str_user = "select * from tblUser"
Set rs_user = db.OpenRecordset(str_user)
rs_user.AddNew
If Len(cboU_NameAdd & vbNullString) > 0 Then
rs_user.Fields("user_name") = cboU_NameAdd
Else
MsgBox ("Please enter Account Name(shortId)")
End If
If Len(txtDispName & vbNullString) > 0 Then
rs_user.Fields("disp_name") = txtDispName
Else
MsgBox ("Please enter Display Name")
End If
If Len(txtGivenName & vbNullString) > 0 Then
rs_user.Fields("given_name") = txtGivenName
End If
' If Len(cboU_NameAdd & vbNullString) > 0 Then
' rs_user.Fields("exist_skype") = cboU_NameAdd
' End If
If Len(cboCompany & vbNullString) > 0 Then
rs_user.Fields("company_id") = cboCompany
Else
MsgBox ("Please enter Company.")
End If
rs_user.Fields("is_active") = True
rs_user.Update
rs_user.Close
Set rs_user = Nothing
str_user = "select user_id from tblUser where user_name ='" & cboU_NameAdd.Value & "'"
Set rs_user = db.OpenRecordset(str_user)
intUserId = rs_user.Fields("user_id")
rs_user.Close
Set rs_user = Nothing
If intUserId <> 0 Then
str_acnt_dtls = "select * from tblAccount_Dtls"
Set rs_acnt_dtls = db.OpenRecordset(str_acnt_dtls)
rs_acnt_dtls.AddNew
rs_acnt_dtls.Fields("user_id") = intUserId
rs_acnt_dtls.Fields("prod_env") = chkProd
rs_acnt_dtls.Fields("dr_env") = chkDR
rs_acnt_dtls.Fields("qa_env") = chkQA
rs_acnt_dtls.Fields("preprod_env") = chkPreProd
If Len(txtCreatedDt & vbNullString) > 0 Then
rs_acnt_dtls.Fields("created_dt") = txtCreatedDt
Else
MsgBox ("Please enter Create Date.")
End If
rs_acnt_dtls.Fields("hadoop_root_access") = chkHadoopAdminRoot
rs_acnt_dtls.Fields("hadoop_data_access") = chkHadoopData
rs_acnt_dtls.Fields("nonhadoop_root_access") = chkNonHadoopAdmin
rs_acnt_dtls.Fields("postgres_access") = chkPostgreSQLdata
If Len(txtJobDtls & vbNullString) > 0 Then
rs_acnt_dtls.Fields("business_just") = txtJobDtls
Else
MsgBox ("Please enter Business Justification.")
End If
If Len(cboGroup & vbNullString) > 0 Then
rs_acnt_dtls.Fields("grp_id") = cboGroup
Else
MsgBox ("Please enter Group Name.")
End If
If Len(cboTeam & vbNullString) > 0 Then
rs_acnt_dtls.Fields("team_id") = cboTeam
Else
MsgBox ("Please enter Team Name.")
End If
If Len(cboResMngr & vbNullString) > 0 Then
rs_acnt_dtls.Fields("res_mngr_id") = cboResMngr
Else
MsgBox ("Please enter Resource Manager.")
End If
If Len(cboProjType & vbNullString) > 0 Then
rs_acnt_dtls.Fields("proj_type_id") = cboProjType
End If
If Len(txtComments & vbNullString) > 0 Then
rs_acnt_dtls.Fields("comments") = txtComments
End If
If Len(cboAccessFreq & vbNullString) > 0 Then
rs_acnt_dtls.Fields("access_freq_id") = cboAccessFreq
End If
If Len(txtZDticket & vbNullString) > 0 Then
rs_acnt_dtls.Fields("zd_ticket_no") = txtZDticket
Else
MsgBox ("Please enter ZD Ticket No.")
End If
If Len(txtRemedyTicket & vbNullString) > 0 Then
rs_acnt_dtls.Fields("remedy_no") = txtRemedyTicket
Else
MsgBox ("Please enter Remedy No.")
End If
rs_acnt_dtls.Fields("is_active") = True
'---------------------------------------------
' Code to ADD Attachments will go here...
'....
'....
'----------------------------------------------
'User Activity log code will go here...
'.....
'.....
'----------------------------------------------
rs_acnt_dtls.Update
rs_acnt_dtls.Close
Set rs_acnt_dtls = Nothing
db.Close
cboU_NameAdd = ""
cboU_NameAdd.SetFocus
cboU_NameAdd.Requery
intResponse = MsgBox("Record Added", , "User Details")
Call U_ClearFields
End If
End If
ElseIf fraU_Operation.Value = 2 Then 'Edit User
str_acnt_dtls = "select * from tblAccount_Dtls where user_id = (select user_id from tblUser where user_name='" & cboU_NameEdit.Value & "')"
Set db = CurrentDb
Set rs_acnt_dtls = db.OpenRecordset(str_acnt_dtls)
rs_acnt_dtls.Edit
rs_acnt_dtls.Fields("prod_env") = chkProd
rs_acnt_dtls.Fields("dr_env") = chkDR
rs_acnt_dtls.Fields("qa_env") = chkQA
rs_acnt_dtls.Fields("preprod_env") = chkPreProd
If Len(txtCreatedDt & vbNullString) > 0 Then
rs_acnt_dtls.Fields("created_dt") = txtCreatedDt
Else
MsgBox ("Please enter Create Date.")
End If
rs_acnt_dtls.Fields("hadoop_root_access") = chkHadoopAdminRoot
rs_acnt_dtls.Fields("hadoop_data_access") = chkHadoopData
rs_acnt_dtls.Fields("nonhadoop_root_access") = chkNonHadoopAdmin
rs_acnt_dtls.Fields("postgres_access") = chkPostgreSQLdata
If Len(txtJobDtls & vbNullString) > 0 Then
rs_acnt_dtls.Fields("business_just") = txtJobDtls
Else
MsgBox ("Please enter Business Justification.")
End If
If Len(cboGroup & vbNullString) > 0 Then
rs_acnt_dtls.Fields("grp_id") = cboGroup
Else
MsgBox ("Please enter Group Name.")
End If
If Len(cboTeam & vbNullString) > 0 Then
rs_acnt_dtls.Fields("team_id") = cboTeam
Else
MsgBox ("Please enter Team Name.")
End If
If Len(cboResMngr & vbNullString) > 0 Then
rs_acnt_dtls.Fields("res_mngr_id") = cboResMngr
Else
MsgBox ("Please enter Resource Manager.")
End If
If Len(cboProjType & vbNullString) > 0 Then
rs_acnt_dtls.Fields("proj_type_id") = cboProjType
End If
If Len(txtComments & vbNullString) > 0 Then
rs_acnt_dtls.Fields("comments") = txtComments
End If
If Len(cboAccessFreq & vbNullString) > 0 Then
rs_acnt_dtls.Fields("access_freq_id") = cboAccessFreq
End If
If Len(txtZDticket & vbNullString) > 0 Then
rs_acnt_dtls.Fields("zd_ticket_no") = txtZDticket
Else
MsgBox ("Please enter ZD Ticket No.")
End If
If Len(txtRemedyTicket & vbNullString) > 0 Then
rs_acnt_dtls.Fields("remedy_no") = txtRemedyTicket
Else
MsgBox ("Please enter Remedy No.")
End If
'---------------------------------------------
' Code to ADD/DELETE Attachments will go here...
'....
'....
'----------------------------------------------
'User Activity log code will go here...
'.....
'.....
'----------------------------------------------
rs_acnt_dtls.Update
rs_acnt_dtls.Close
Set rs = Nothing
db.Close
cboU_NameEdit.Visible = True
txtU_Name.Visible = False
cboU_NameEdit = ""
cboU_NameEdit.Requery
intResponse = MsgBox("Record Updated", , "User Details")
Call U_ClearFields
cboU_NameEdit.SetFocus
Else ' 'Delete User
intResponse = MsgBox("Do you want to Delete the Record?", vbYesNoCancel, "Confirmation...")
If intResponse = vbYes Then
str_user = "Update tblUser set is_active = 0 where user_id= (select user_id from tblAccount_Dtls where user_name ='" & cboU_NameEdit.Value & "')"
DoCmd.SetWarnings False
DoCmd.RunSQL str_user
intResponse = MsgBox("Record Deleted", , "User Details")
End If
cboU_NameEdit.Visible = True
txtU_Name.Visible = False
cboU_NameEdit = ""
cboU_NameEdit.Requery
Call U_ClearFields
'----------------------------------------------
'User Activity log code will go here...
'.....
'.....
'----------------------------------------------
End If
Exit Sub
errMsg:
MsgBox Err.Description
End Sub