add/edit/delete an attachment on the form (1 Viewer)
- Thread starter shishya
- Start date
Hi arnelgp,
This is the code being used for add/edit/delete..
Need to include the attachments code within this.
I should be able to "add" attachments during Add operation and "add/delete" attachments during Edit operation.
This is the code being used for add/edit/delete..
Need to include the attachments code within this.
I should be able to "add" attachments during Add operation and "add/delete" attachments during Edit operation.
Code:
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
Last edited:
arnelgp
..forever waiting... waiting for jellybean!
- Local time
- Today, 09:24
- Joined
- May 7, 2009
- Messages
- 19,230
What file type of attachment do you wish to save? Is it pics, image? I think you need to upload a sample db. There are other controls you need to add to your form like filedialog, image control, etc.
Hi arnelgp,
"....What file type of attachment do you wish to save? Is it pics, image?"
-> .xlsx (excel sheet), .jpg and .docx file.
Attaching the sampleDB.
Also apart from Adding/Deleting attachments, I would also like to View the attachment(s) when clicked on an attachment image (something like that..) when viewing the records.
"....What file type of attachment do you wish to save? Is it pics, image?"
-> .xlsx (excel sheet), .jpg and .docx file.
Attaching the sampleDB.
Also apart from Adding/Deleting attachments, I would also like to View the attachment(s) when clicked on an attachment image (something like that..) when viewing the records.
Hi Arnelgp,
It would be good if we can add an attachment "during" adding of a new user itself. But this seems not possible as the record needs to be existing before an attachment could be made.
One way is - to save the user details before the attachment is done (may be in cmdExcelAttach_Click event) and then update this record to save the attachment.
Or do we have any better option?
It would be good if we can add an attachment "during" adding of a new user itself. But this seems not possible as the record needs to be existing before an attachment could be made.
One way is - to save the user details before the attachment is done (may be in cmdExcelAttach_Click event) and then update this record to save the attachment.
Or do we have any better option?
