Attaching a word document to the attachment field for every new record (1 Viewer)

[KenGra]

Hi,
I'm trying to work out how to attach a word doc to the attachment field of a table when I add a new Record. The following code work's fine if I add it to the On Load Event on the Form, but the doc is attach'd to the first record. When I add the code to the Add New Record button, I get the error msg: "File already part of the Multi-Valued Field" and nothing in the attachment field. I can't work out what is going on, can anybody help.

Private Sub Add_Record_Click()

DoCmd.GoToRecord , , acNewRec
Me.Description.SetFocus

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

    rsChild.AddNew
    rsChild.Fields("FileData").LoadFromFile ("C:\Users\Public\Sample.docx")
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

Regards
Ken

 

[JHB]

The error message is a user created one in the error handling part, (for err 3820), so it doesn't really tell anything.
Comment out the error handling, then you'll get the codeline in which the error occure + the system description of the error, then maybe you're able to find out what is going wrong.
Else post the code line + the system description of the error!

 

[KenGra]

The error message is a user created one in the error handling part, (for err 3820), so it doesn't really tell anything.
Comment out the error handling, then you'll get the codeline in which the error occure + the system description of the error, then maybe you're able to find out what is going wrong.
Else post the code line + the system description of the error!

I Commented out the error handling, and tried it again and the system description of the error is:
"Run Time Error '3820'
you cannot enter that value because it duplicates
an existing value in the multi-valued lookup or attachment field.
multi-valued lookup or attachment fields cannot contain duplicate values."

When I click Debug the following line of code is highlighted.
rsChild.Update
Regards
Ken

 

[spikepl]

Dim rsParent As DAO.Recordset2 ??? Why 2?

 

[KenGra]

From what I have read the Recordset2 in Access is for the Attachment field. Don't I need that should it be Dim rsParent As Recordset and Dim rsChild As Recordset2

 

[CJ_London]

From what I have read the Recordset2 in Access is for the Attachment field

Don't know where you have read that, it is for multivalue fields - see

http://msdn.microsoft.com/en-us/library/bb243540(v=office.12).aspx

And the error description presumably answers your question

you cannot enter that value because it duplicates
an existing value ....

 

[KenGra]

I understand the error msg, but on a new record with nothing in any field, how can it duplicate an existing value.

 

[Galaxiom]

You are misunderstanding how a multivalue field works.

The values are not independent between records. They are more like a listbox where you select which of a list of available options is associated with each record. When you add another value you are in effect adding to the equivalent of the rowsource, hence the error.

If you want to have multiple independent attachements on each record then you need to use a related table for the attachments.

However think carefully before you do this. Storing files in a databse takes up a lot of space and you may find Access's 2GB limit is arrived at rather quickly.

A better alternative that won't expand the database near so much is to store the path to the file.

 

[KenGra]

Thanks Galaxion,
The penny has dropped, I will have to re-think the project and look into storing the path to the file's instead. Will just have to work out the other function of sending the record by email and
attaching the file's to the email.
Regards
Ken