none
Type in MS Word and attach it automatically in a Access form as an attachment RRS feed

  • Question

  • I am adding a new feature to my program. I want the secretary to open a form in Access, enter the date,number and the subject of the letter. Click a button that opens a new file and page in MS Word. She should type the letter and after completing it close it. I want that after closing, the Word file be saved as an attachment to that form in Access.

    I tried to type the letter in Word and after completing, attach it to related form as an attachment, but this procedure makes a lot of Word files. I don't want it. I want that the process be automate.


    Karim Vaziri Regards,


    • Edited by kvaziri Thursday, November 24, 2016 8:58 PM
    Thursday, November 24, 2016 8:57 PM

Answers

  • Hi kvaziri,

    I can see that your form having multiple fields and all are already having the data and you are just adding the file in your code.

    so its like you modify the table and added a attachment field to attach particular document to particular record.

    so you need to check the record id and then you can attach the file to that record.

    currently you are not checking anything and trying to attach the file so it is attaching the file to first record.

    please refer the example code mentioned below to update the record.

    Sub DAOUpdating()
    On Error GoTo ErrorHandler
    'This sub-produre will add 'z' to the first name of
    'the record that corresponds to TeacherID 5
    Dim sql As String
    Dim rs As DAO.Recordset
    
    sql = "SELECT * FROM tblTeachers WHERE TeacherID=5"
    'We are using a select statement that will return only
    'one record (TeacherID 5)
    
    Set rs = CurrentDb.OpenRecordset(sql)
    'Open RecordSet
    
    With rs
    
        If Not .BOF And Not .EOF Then
        'Ensure that the recordset contains records
        'If no records the code inside the if...end if
        'statement won't run
        
            .MoveLast
            .MoveFirst
            'Not necessary but good practice
            
            If .Updatable Then
            'It is possible that the record you want to update
            'is locked by another user. If we don't check before
            'updating, we will generate an error
            
                .Edit
                'Must start an update with the edit statement
                
                ![FirstName] = "z" & ![FirstName]
                'Another way of accessing the fields would be to use
                '.fields("FirstName") = z" & .fields("FirstName")
                
                .Update
                'And finally we will need to confirm the update
                
            End If
        End If
        
        .Close
        'Make sure you close the recordset...
    End With
    
    ExitSub:
        Set rs = Nothing
        '...and set it to nothing
        Exit Sub
    ErrorHandler:
        Resume ExitSub
    
    End Sub
    

    Reference:

    Updating, Adding And Deleting Records In a Recordset

    your form is already having the Record id, so you not need to fire a query like shown in example. you can get the id from textbox on the form.

    and update the attachment field of the record whose id is mentioned in the textbox.

    so by this way you will be able to attach the file to every record.

    Regards

    Deepak 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by kvaziri Friday, December 2, 2016 4:55 PM
    • Unmarked as answer by kvaziri Friday, December 2, 2016 4:55 PM
    • Marked as answer by kvaziri Friday, December 2, 2016 4:56 PM
    Thursday, December 1, 2016 3:49 AM
    Moderator

All replies

  • Hi kvaziri,

    please refer the code mentioned below.

    as you had mentioned above I created a table "attachment_data" in my access database looks like below.

    then I create a form looks like below.

    use the code mentioned below.

    Option Compare Database
    Option Explicit
    
    Private Sub Command6_Click()
    Call FnWriteToWordDoc
    End Sub
    
    Private Sub Command8_Click()
     Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    
       Set dbs = CurrentDb
       Set rst = dbs.OpenRecordset("attachment_data")
    
       rst.AddNew
       rst!dt = txtdt.Value
       rst!Number = txtnum.Value
       rst!Subject = txtsub.Value
       rst.Fields("attachment").LoadFromFile ("C:\Users\v-padee\Desktop\temp.docx")
       Fields("attachment").SaveToFile "C:\Documents and Settings\Username\My Documents"
       rst.Update
       MsgBox ("Record Inserted Successfully...")
        Kill ("C:\Users\v-padee\Desktop\temp.docx")
    End Sub
    Function FnWriteToWordDoc()
    
       Dim objWord
       Dim objDoc
       Dim objSelection
    
       Set objWord = CreateObject("Word.Application")
    
       Set objDoc = objWord.Documents.Add
    
       objWord.Visible = True
    
       Set objSelection = objWord.Selection
    
       objSelection.TypeText ("Please Save the File Before you close the document...")
    
       objDoc.SaveAs ("C:\Users\v-padee\Desktop\temp.docx")
    
    End Function
    
    
    
    

    after entering the data in form click on "create document" button.

    it will create a new document. document will be saved on desktop(you can change the path to save it temporary.)

    write a letter in that , save and close it.

    then click on "Save Data" button.

    when we insert it in to db then I change the save location of that document to somewhere else. you can also change it to your desired location.

    then record will be inserted to the table with word file.

    after that we delete that temporary created word file. because the file is already saved with in table.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 25, 2016 1:39 AM
    Moderator
  • I tried to type the letter in Word and after completing, attach it to related form as an attachment, but this procedure makes a lot of Word files. I don't want it. I want that the process be automate.

    Hi Karim,

    I prefer to store all kind of "external documents" (Word files, Excel files, jpg-file, mp3-files, ...) external to Access in a separate map. In Access to name of the "document" is stored, together with some other fields to characterize the "document", AND the path to the document.

    How many documents there are in the map or maps is not so important, while the user never need to go in those map(s). You can even make a routine that moves the document automatically after changing the stored pathname.

    Documents are very easily to "see" or to "hear", and it causes no bloating of the Access database.

    Just my way of working.

    Imb.

    Friday, November 25, 2016 9:23 AM
  • Dear lmb,

    I don't know what is your purpose of "External Map". Could you explain more about it?

    Thanks.


    Karim Vaziri Regards,

    Friday, November 25, 2016 11:47 AM
  • I don't know what is your purpose of "External Map". Could you explain more about it?

    Hi Karim,

    It is a map on a disk or on a server to store all the documents, thus outside Access.

    Imb.

    Friday, November 25, 2016 11:59 AM
  • This is not a good way to do things (in my opinion). I would have the MS Word document saved in a special folder with the file named in such a way that the name is linked to the table record (presumably your form is bound to a table record?). In this way the Word files are saved external to the Access database but can be accessed via a hyperlink from the form without any additional data entry since the record bound to the form 'knows' what the document is named.

    For example for table record with primary id of 123456.

    Document is called: MyWordfiles-123456.docx

    This assumes you have one attachment per record. If you need more, just adjust the naming scheme.

    Friday, November 25, 2016 9:38 PM
  • It is a map on a disk or on a server to store all the documents, thus outside Access.

    Thank you ATGNWT,

    "map" is the Dutch word for "folder".

    Imb.


    • Edited by Imb-hb Friday, November 25, 2016 9:47 PM
    Friday, November 25, 2016 9:46 PM
  • Hi kvaziri,

    did your issue solved?

    if yes , update the status of this thread.

    if not, let us know about that.

    we will try to suggest you further to this issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, November 28, 2016 4:33 AM
    Moderator
  • Dear Deepak,

    It's a good solution. But when I want to test it I get this error.


    Karim Vaziri Regards,

    Monday, November 28, 2016 11:39 AM
  • Dear Deepak,

    Why you don't use attachment field type for storing this Word file and use OLE object?

    Regards,

    Karim


    Karim Vaziri Regards,

    Monday, November 28, 2016 11:42 AM
  • Hi kvaziri,

    please replace that line with the below mentioned line.

     rst.Fields("attachment").SaveToFile "C:\Documents and Settings\Username\My Documents"

    by mistake I remove the "rst.". so it is giving you an error.

    other question you had asked that,"Why you don't use attachment field type for storing this Word file and use OLE object?"

    it seems that you are using .accdb database. so attachment field is available.

    if you create .mdb database then attachment datatype is not available. in stead of that we have to use OLE Object. so I am using ole object.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 29, 2016 5:32 AM
    Moderator
  • Dear Deepak,

    I amend your code for attaching the Word documents to a field as attachment type. But there is a problem. When I am in record 3 and I attach the word document it be attached to the first record. I think the code doesn't understand to which record it should attach the document.  

    Private Sub cmdSaveWordDoc_Click()
                Dim dbs As DAO.Database
                Dim rst As DAO.Recordset2
                Dim rsAttachments As DAO.Recordset2
                Dim strFileName As String
                
                
               'Get the database,recordset,and attachment field
               
                Set dbs = CurrentDb
                Set rst = dbs.OpenRecordset("tblIndicatorBook")
                strFileName = Me.txtIndicatorNumber & ".docx"
        
               rst.FindFirst IndicatorNumber = Me.txtIndicatorNumber
               
                  
                rst.Edit
                
                'Instantiate the child recordset.
                Set rsAttachments = rst.Fields("LetterScan").Value
                
                'Add a new attachment.
                rsAttachments.AddNew
                rsAttachments.Fields("FileData").LoadFromFile "Z:\Virsa\temp\" & strFileName
                rsAttachments.Update
                
                
                rsAttachments.Close
                'Update the Parent record
                
                 rst.Update
                 
                          
                         
                      
           
                rst.Close
                dbs.Close
                Set rsA = Nothing
                Set rst = Nothing
                Set dbs = Nothing
                
                MsgBox ("Record Inserted Successfully")
                
    
    End Sub


    Karim Vaziri Regards,

    Wednesday, November 30, 2016 3:16 PM
  • Hi kvaziri,

    I can see that your form having multiple fields and all are already having the data and you are just adding the file in your code.

    so its like you modify the table and added a attachment field to attach particular document to particular record.

    so you need to check the record id and then you can attach the file to that record.

    currently you are not checking anything and trying to attach the file so it is attaching the file to first record.

    please refer the example code mentioned below to update the record.

    Sub DAOUpdating()
    On Error GoTo ErrorHandler
    'This sub-produre will add 'z' to the first name of
    'the record that corresponds to TeacherID 5
    Dim sql As String
    Dim rs As DAO.Recordset
    
    sql = "SELECT * FROM tblTeachers WHERE TeacherID=5"
    'We are using a select statement that will return only
    'one record (TeacherID 5)
    
    Set rs = CurrentDb.OpenRecordset(sql)
    'Open RecordSet
    
    With rs
    
        If Not .BOF And Not .EOF Then
        'Ensure that the recordset contains records
        'If no records the code inside the if...end if
        'statement won't run
        
            .MoveLast
            .MoveFirst
            'Not necessary but good practice
            
            If .Updatable Then
            'It is possible that the record you want to update
            'is locked by another user. If we don't check before
            'updating, we will generate an error
            
                .Edit
                'Must start an update with the edit statement
                
                ![FirstName] = "z" & ![FirstName]
                'Another way of accessing the fields would be to use
                '.fields("FirstName") = z" & .fields("FirstName")
                
                .Update
                'And finally we will need to confirm the update
                
            End If
        End If
        
        .Close
        'Make sure you close the recordset...
    End With
    
    ExitSub:
        Set rs = Nothing
        '...and set it to nothing
        Exit Sub
    ErrorHandler:
        Resume ExitSub
    
    End Sub
    

    Reference:

    Updating, Adding And Deleting Records In a Recordset

    your form is already having the Record id, so you not need to fire a query like shown in example. you can get the id from textbox on the form.

    and update the attachment field of the record whose id is mentioned in the textbox.

    so by this way you will be able to attach the file to every record.

    Regards

    Deepak 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by kvaziri Friday, December 2, 2016 4:55 PM
    • Unmarked as answer by kvaziri Friday, December 2, 2016 4:55 PM
    • Marked as answer by kvaziri Friday, December 2, 2016 4:56 PM
    Thursday, December 1, 2016 3:49 AM
    Moderator
  • Dear Deepak,

    I rechecked my code and amend it based on your instruction and the problem was solved.

    Regards,

    Vaziri


    Karim Vaziri Regards,

    Friday, December 2, 2016 4:55 PM
  • Hi kvaziri,

    from your last post we can know that your issue is solved now.

    but the another thread for the same issue is still open.

    so I would suggest you to please try to update the status of that thread and mark it as an Answer.

    so that we can close that case too.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 5, 2016 4:24 AM
    Moderator