add/edit/delete an attachment on the form

S

shishya

New member
Local time
Today, 05:23
Joined
Feb 23, 2014
Messages
9
Hi Everyone,

Can someone please provide me vba code for Adding/editing/deleting an attachment from a form?

Regards
 
you just need to drag the attachment field on the form.
use the right click context menu of the attachment form to add/edit/delete the attachment.
 
Thanks for the reply but I'm using vba code to add/edit/delete the records on the form. So I need vba code for attachments also (pl see screen shot attached).
 

Attachments

  • userForm.JPG
    userForm.JPG
    64.2 KB · Views: 195
if you are using vba, what code do you have so far for add/edit/delete
 
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.


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:
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.
 

Attachments

There is no attachment , shishya.
 
Hi arnelgp,

SampleDB.accdb (1.66 MB) ... file attached.
please check the #7 post.
 
Ok i see it. Ill work on it.
 
Here is the installment, on the form click on the attachnent pic.
 

Attachments

Here is the 2nd installment
 

Attachments

thank you very much arnelgp..
exactly what i was looking for..
 
You are welcome.
 
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?
 
I cant deliver it till tomorrow, im at the field right now
 
You must log in or register to reply here.

Similar threads

MajP
Access Item Genie demo for Organizing / Tracking your Items
2 3 4
Replies
74
Views
2,345
ClaraBarton
C
Cotswold
Add, Edit, or Delete a Function() using VBA
Replies
7
Views
509
isladogs
isladogs
E
Solved Add and Edit VBA for form and relative subforms
Replies
12
Views
400
theDBguy
theDBguy
S
Solved Second SQl delete query raises runtime error 424
Replies
8
Views
190
Pat Hartman
P
M
Solved Attachment enabled if
Replies
2
Views
181
mtagliaferri
M

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom