none
An error when I want to attache a word file to my Form RRS feed

  • Question

  • I write a code for being able to automatically attach word file that I create to a record(Form).It works good for the first record but in second record and more I got this error.

    my code is as follow:

    Private Sub cmdSaveWordDoc_Click()
         Dim dbs As DAO.Database
                Dim rst As DAO.Recordset2
                Dim rsA As DAO.Recordset2
                Dim fld As DAO.Field2
                Dim strPath As String
                Dim strFile As String
                
                
               'Get the database,recordset,and attachment field
               
                Set dbs = CurrentDb
                Set rst = dbs.OpenRecordset("tblIndicatorBook")
                Set fld = rst("LetterScan")
                strPath = "Z:\Virsa\temp\temp.docx"
        
               'Navigate through table for the attachment field
               Do While Not rst.EOF
               
               'Get the recordset for the attachment field
                Set rsA = fld.Value
                
                'Load all attachment in the specified path
                strFile = Dir(strPath)
                  
                rst.Edit
                
                Do While Len(strFile) > 0
                 rsA.AddNew
                 rsA("FileData").LoadFromFile strPath
                 rsA.Update
                 
                 'Increment the number of files added
                 LoadAttachments = LoadAttachments + 1
                 
                 strFile = Dir
                 Loop
                 
                rsA.Close
                rst.Update
                'Next record
                rst.MoveNext
                Loop
                      
           
                rst.Close
                dbs.Close
                Set fld = Nothing
                Set rsA = Nothing
                Set rst = Nothing
                Set dbs = Nothing
                
                MsgBox ("Record Inserted Successfully")
                Kill ("Z:\Virsa\temp\temp.docx")
    
    End Sub

    My table that is linked to this form is as follow:


    Karim Vaziri Regards,

    Monday, November 28, 2016 10:00 PM

All replies

  • You need to perform an rst.Update after each insert I believe

    Here my function which works

    Function AddAtt()
        Dim db                    As DAO.Database
        Dim rs                    As DAO.Recordset2
        Dim rsAtt                 As DAO.Recordset2
        Dim sSQL                  As String
    
        Set db = CurrentDb()
        sSQL = "SELECT * FROM tbl_Projects"
        Set rs = db.OpenRecordset(sSQL, dbOpenDynaset)
        If rs.RecordCount <> 0 Then
            '        Do While Not rs.EOF
            rs.MoveFirst
            rs.Edit
            
            Set rsAtt = rs![AttFiles].Value
            rsAtt.AddNew
            rsAtt.Fields("FileData").LoadFromFile "C:\Users\Me\Desktop\10-30-2016 1-50-11 PM.jpg"
            rsAtt.Update
            rs.Update
    
            rs.Edit
            Set rsAtt = rs![AttFiles].Value
            rsAtt.AddNew
            rsAtt.Fields("FileData").LoadFromFile "C:\Users\Me\Desktop\10-30-2016 2-18-11 PM.jpg"
            rsAtt.Update
            rs.Update
            '            rs.MoveNext
            '        Loop
        End If
    
        Set rsAtt = Nothing
        rs.Close
        Set rs = Nothing
        Set db = Nothing
    End Function


    Daniel Pineault, 2010-2016 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Monday, November 28, 2016 10:39 PM
  • Hi Karim,

    I think you are getting this error because you're attempting to attach the same file over and over to the same record. What exactly are you trying to accomplish? Are you trying to attach the same multiple files to all the records, or are you trying to attach one file to each record? How many files were you expecting to find in the folder?

    These parts in your code relate to each other and could be causing the issue:

    strPath = "Z:\Virsa\temp\temp.docx"

    strFile = Dir(strPath)

    rsA.("FileData").LoadFromFile strPath

    I think you meant to assign a folder to strPath but assigned a file instead. It then results in strFile "always" having the same value (temp.docx). Then, when you attach the file, you're using strPath, which "always" have the same value (file name).

    I think the error is telling you duplicate filenames are not allowed in an Attachment field.

    Just my 2 cents...

    Tuesday, November 29, 2016 3:11 AM
  • Dear theDBguy,

    My plan is this:

    1-The operator click a button on a form ( a record) and an empty word documents opens.

    2- The operator type the letter and save it. It automatically save to "Z:\Virsa\temp\temp.docx" and its name is temp.docx.

    3- The operator click another button in the form and the word file attach to this form (Record).

    4- The created word file will be deleted by "Kill" command.

    5- The operator save the form and go to next form (Record).

    6-Again she repeats the mentioned steps for another Word document. The Word document name is again "temp.docx" but the previous file deleted.

    My goal is automating attaching word documents to Records. I don't want to have for example 1,000 Word file in a folder with various and strange names.  


    Karim Vaziri Regards,

    Tuesday, November 29, 2016 8:37 PM
  • Hi Karim,

    Thanks for the additional information. As I said earlier, you seem to be trying to "loop" through the files in a directory but only passing the name of the file rather than a folder to the loop. After your explanation, I think you really don't want to "loop" through a folder at all since you know the name of the file and its location, and the file will be deleted as soon as it's attached to the record.

    So, the first thing I would do is remove the line Do While Len(strFile)>0 and its associated Loop statement. Besides, the result of Len(strFile) will never be equal or less than 0 anyway because you're always looking for the same file within the loop (in other words, you were not "killing" the file until you were outside the loop).

    Next, I would also take out the loop to go through all the records in the table (ie. Do While Not rst.EOF) because you said the user will click on a button for each record anyway. So, in effect, you only want to attach one file to one record each time the user clicks a button.

    In summary, you'll need to modify your code to take the specific record the user is editing and then attach the Word file to it then delete the Word file, and then you're done. 

    Hope it helps...

    Tuesday, November 29, 2016 8:57 PM
  • 6-Again she repeats the mentioned steps for another Word document. The Word document name is again "temp.docx" but the previous file deleted.

    My goal is automating attaching word documents to Records. I don't want to have for example 1,000 Word file in a folder with various and strange names.  

    Hi Karim,

    Of course you choose what suits you the best, but I am afraid that you will reach the size limits of workable database far too soon, by including all those Word-documents in the database.

    How many Word files with what names are in the folder, is not relevant in my opinion. The same two buttons for storing and retrieving the documents as you use now, are equally used for storing and retrieving. The folder can be completely intransparant, in the same way as it is intransparant how Access stores the Attachement files. You could even store the Word files with the concerning record_id as name.

    But that is my way of working.

    Imb.

    Tuesday, November 29, 2016 9:55 PM
  • Hi kvaziri,

    Here I find that your requirement in your last post match with another thread created by you.

    it is ok. if you wan to continue the discussion on this thread then please try to close the below mentioned thread or just let me know if you want to merge these both threads.

    because if you get the answer here then other thread will still open forever.

    so please take action on this issue.

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

    did you try to run my code that I suggested you in above mentioned thread. I also provide you a suggestion to solve that error.

    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.

    Wednesday, November 30, 2016 6:59 AM
    Moderator