add an attachment of an expanded attachment field as new record

[aspen]

Hi
I have a form based on an expanded query based on a table wher each record has about 100 attachments. The query criteria is set to show only one recrd based on a combobox in the main form.So my form shows all the attachments in record 1 of the table. lets say record 1 of the table has 10 attacments. Then my form displays the ten attachments in 10 rows with the same ID.Can some one help me how to delet and make say five of them attachments a new record for the table. so now my form will have 5 records and the other 5 is in a new record in the table. I believe access can make the imposible to happen. I hope you guys got what iam trying to do.
I am very confident that this is possible.

please help me.

[June7]

What you might have to do is save the attachments to a folder outside the db then attach them back into the new record.

Review
http://blogs.office.com/b/microsoft-...cess-2007.aspx
https://www.accessforums.net/showthr...ght=attachment

[aspen]

Hi super moderator

Its so nice being with you again. here is the code you gave me in our old post for savind a file to a folder out side. I am using it in my DB and it saves the file to a folder called new on my desktop. I think we can acomplish what this thread requires if we can change the code so that it delets the current file from the database , before or after its saved to folder called New . then we can either paste it using or changing code or manually back to database as a new record.
so can you please help me change this code of yours so as to it would delet the record along the way of saving.

to refresh
here is that code you gave made

Private Sub Attachment23_Click()
Dim strFilePath
With Me.RecordsetClone
.Bookmark = Me.Bookmark
strFilePath = "C:\Users\eve\Desktop\New\" & Me.tbxFileName
If Dir(strFilePath) <> "" Then VBA.Kill strFilePath
![Atachments.Field1.FileData].SaveToFile strFilePath
End With
End Sub

Please help me change it

Thank you

[June7]

The first link in my previous post has example code.

[aspen]

Hi super Moderator

Thanks for that. Now i have modified the code like this and so It saves the clicked attachment and deletes the attachment from the records attachments. so that part is ok
Now can you please help me with the second part : to save the files in the folder as a new record in the table. and delet the files in the folder.
can you please change the code like that.

This code saves to folder and deletes from the table

Private Sub Attachment17_Click()
Dim strFilePath
With Me.RecordsetClone
.Bookmark = Me.Bookmark
strFilePath = "C:\Users\eve\Desktop\New\" & Me.tbxFileName
If Dir(strFilePath) <> "" Then VBA.Kill strFilePath
![Table1.Files.FileData].SaveToFile strFilePath
DoCmd.RunCommand acCmdDeleteRecord
End With
End Sub

Thank you

[June7]

First would have to create the new record (without attachment) either by data entry or with an append query. Then insert the attachment to the new record.

[aspen]

Hi thanks

Now I am able to load image to a record from a folder by using this code i found somewhere.

Dim db As DAO.Database
Dim rsParent As DAO.Recordset2
Dim rsChild As DAO.Recordset2
Set db = CurrentDb
Set rsParent = Me.Recordset
rsParent.Edit
Set rsChild = rsParent.Fields("Files").value
rsChild.AddNew
rsChild.Fields("FileData").LoadFromFile ("C:\Users\eve\Desktop\New1\aman_b.jpg")
rsChild.Update
rsParent.Update
Exit_AddImage:
Set rsChild = Nothing
Set rsParent = Nothing
Exit Sub
Err_AddImage:
If Err = 3820 Then
MsgBox ("File already part of the multi-valued field!")
Resume Next
Else
MsgBox "Some Other Error occured!", Err.Number, Err.Description
Resume Exit_AddImage


The problem is this line "rsChild.Fields("FileData").LoadFromFile ("C:\Users\eve\Desktop\New1\aman_b.jpg")"

I have to write the image name "aman_b.jpg" The file name every time.

Is there a way to tell access that its all the attachments in the folder that i want to load into the tables record

Any Ideas
Thank you

[June7]

Need looping code that will read the files in the designated folder and do something with them, in your case save to attachment field. Review http://allenbrowne.com/ser-59.html

[aspen]

Hi
How about making a code that would write the file path to a field in the table in the proccess of saving the attachment to folder as in post 5 of this thread. can you change the code for me so it would write the file path to a new field : like combine the file name field and folder path .

thank you

[June7]

Sometimes I will write code for posters if no relevant samples can be found and I find the issue interesting enough. You have enough sample code to write a procedure specific to your situation. You should attempt code and debug and provide for analysis if you have issues you can't resolve.

You can write the path string to a field in table or unbound box on form or to a variable in code and use it as you see fit. I really don't know why you would want it saved to field in table.

[aspen]

Dear Jun 7
I am exremly sorry for mis understanding your name. I just happen to realize that i have been refering you with the a wrong name. Please forgive me for my carelessness.
I have tried the looping code in various ways and i get errors like database is red only and tis line is Yellow "rsParent.Edit". that code in post 7 this thread attaches the file .rsChild.Fields("FileData").LoadFromFile ("C:\Users\eve\Desktop\New1\aman_b.jpg") . But have to write "aman_b.jpg" The file name every time. is there a way to write All the file names in the folder autometically. I mean attach all the files in the folder to the attach field. can you please get me some link to a ressembling code that i may be able to use. because i am very knew to codes

please help
thank you

[June7]

That last link I reference has code for dynamically listing all files in a specified folder. It is the Function called 'ListFiles'. Instead of the code to add to a listbox, you would have the code that inserts the file into attachment field. Just have to adapt the code samples to your situation, using what is relevant from each and make it all work together.

[aspen]

Hi jun 7
Thanks
just as the link say I have created a new table with fileID, FilePath and so on .But i have two confutions 1- In the code strPath = "E:\" and in the immidiate window & Call ListFilesToTable("C:\Data")---These two names should be changed to the folder location where I have stored my files to be imported. Right? and 2- Please give me an idea as to how to use it with the code in post #7.
Please help
Thank you

*Now I am able to load the file name and path to table. so whats next?

[aspen]

Hi Jun 7
I followed your advice and its working .Thank you so much. what i did was created the table and module as adv. in the link. created another field "files" attacment. and created a query for multiple items. created a multiple item form. on the click event of id i put this code- It copies file path and file name to the table.

Private Sub FileID_Click()
'Usage example.
Dim strPath As String _
, strFileSpec As String _
, booIncludeSubfolders As Boolean

strPath = "C:\Data"
strFileSpec = "*.*"
booIncludeSubfolders = True

ListFilesToTable strPath, strFileSpec, booIncludeSubfolders
End Sub

Then on the click event of another field I put the code in post#7 - with a little change like this- Now if i click on a record it automatically attaches the apropriate file

Private Sub Attachment26_Click()
Dim db As DAO.Database
Dim rsParent As DAO.Recordset2
Dim rsChild As DAO.Recordset2
Set db = CurrentDb
Set rsParent = Me.Recordset
strFilePath = "C:\Data\" & Me.FName
rsParent.Edit
Set rsChild = rsParent.Fields("Files").Value
rsChild.AddNew
rsChild.Fields("FileData").LoadFromFile strFilePath
rsChild.Update
rsParent.Update
Exit_AddImage:
Set rsChild = Nothing
Set rsParent = Nothing
Exit Sub
Err_AddImage:
If Err = 3820 Then
   MsgBox ("File already part of the multi-valued field!")
   Resume Next
Else
   MsgBox "Some Other Error occured!", Err.Number, Err.Description
   Resume Exit_AddImage
End If
End Sub

So now all the parts are working seperately. so i am thinking using the code on a go to focus event and use macros to move around records. Thats how I am sending files to get saved in the folder in the first part.
What I am trying to do is "save some of the attachments in a record to a folder .delet them from table.bring back the deleted and saved files in the folder back to table in to another record.
well, Thank you very much. i 'll let you know the progress. if you hapen to ponder into a better and easy way please let me know. i wiill be checking the thread from time to time
Thanks for every one
thank you

[aspen]

Hi dear jun 7
Its working perfectly. But can you give me some i dea as to write the file paths (more than one file) to one record in the table. may be by making the record set the the expanded query to which my form is based on.

the thing is i want to delet a few attachments from record 1 and paste the same attachments in record 2.
whats hapening now is I am able to delet say six attachments from record 1 and past it back . but each attachment sits on a new record.
can you please help me solve this - Keep them all in the same record

Thank you