Attaching a file if conditions are met

[vgarzon]

Hi,

I'm trying to do something that may not make sense at first sight, but it has, haha.

I have a List Box in a form, that serves as an attachments list of an item. I managed to make it record the complete filepath of the files that I want to attach to a mail that is being sent by the form.

The only thing is that I also have to store the attachments in a table, but I don't know how to do it if I'm not using an attachment-type field in the form.

Does somebody know how to tell Access 2007 that it has to attach all the files that are specified in the list form? Or do you have any idea of how to accomplish my goal in a different way?

Thanks in advance,

Victor

 

[HiTechCoach]

Hi,

I'm trying to do something that may not make sense at first sight, but it has, haha.

I have a List Box in a form, that serves as an attachments list of an item. I managed to make it record the complete filepath of the files that I want to attach to a mail that is being sent by the form.

The only thing is that I also have to store the attachments in a table, but I don't know how to do it if I'm not using an attachment-type field in the form.

Does somebody know how to tell Access 2007 that it has to attach all the files that are specified in the list form? Or do you have any idea of how to accomplish my goal in a different way?

Thanks in advance,

Victor

Here is some sample code that will add an attachment that you could adapt to your looping code:

On Error GoTo Err_AddImage

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("AttachmentTest").Value

rsChild.AddNew
rsChild.Fields("FileData").LoadFromFile ("c:\Sunset.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

End If

 

[vgarzon]

I think that you got the idea and I think that the code you wrote might help..... The only problem is that I don't get it enterely.


What's the purpose of creating those 2 recordsets? I paste an example of how I go through the list.

Dim i As Integer
    For i = 0 To ListAttachments.ListCount - 1
       PathName = ListAttachments.ItemData(i)
 
       'Having the Pathname of the file that I want to add as an attachment
       'How can I insert it in the attachments column of my table?
 
    Next

So, If I got it, you propose that I put the contents of the attachment list into the parent recordset, right? And then to add each one to the child recordset.... but how could I replace the child recordset for my table? I dont get the syntax of what you replyed to me. sorry i'm a little bit bad for this recordset handling. Could you explain it to me please?

Thanks again!!!

 

[HiTechCoach]

I think that you got the idea and I think that the code you wrote might help..... The only problem is that I don't get it enterely.


What's the purpose of creating those 2 recordsets? I paste an example of how I go through the list.

Dim i As Integer
    For i = 0 To ListAttachments.ListCount - 1
       PathName = ListAttachments.ItemData(i)
 
       'Having the Pathname of the file that I want to add as an attachment
       'How can I insert it in the attachments column of my table?
 
    Next

So, If I got it, you propose that I put the contents of the attachment list into the parent recordset, right? And then to add each one to the child recordset.... but how could I replace the child recordset for my table? I dont get the syntax of what you replyed to me. sorry i'm a little bit bad for this recordset handling. Could you explain it to me please?

Thanks again!!!

I did not write the code. It came from an Access MVP that is also a AWF member.
See:
Microsoft® Access 2007 Working with the Attachment DataType
Hopefully you will find a better explanation there.

 

[vgarzon]

Thanks mate, I think it will suit what I need.

 

[HiTechCoach]

Thanks mate, I think it will suit what I need.

Glad I could assist.

Cheers.

 

[vgarzon]

Thanks mate, I think it will suit what I need.

man it certainly resolved my issue now that I know more about the structure and the use of the attachments fields....

Good page the one which from you got the code.


Thanks again!!!

 

[HiTechCoach]

Glad it was helpful. There are lots of great info on that sire about Access 2007. Oli was a beta tester for Access 2007.