Run-time error '3820'

[Bradborg]

"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."

This is the error I get when I run the following code. All I'm trying to do here is add a .jpg file to the "attachment" field in a table. I've been working through everything I can find on Google to resolve this and nothing has worked.

Currently I've hard coded the file and it's location in the code but what I get when I run it is the above error. If I put an "on error resume next" the code finishes but the file that is linked in the Attached field is NOT the one that is hard coded. It's like it picks a random picture from that folder and assigns it to the field. Also I'm only trying to assign one picture per record and it *seems* like that if I run the this bad boy twice the second time through I DO get the right file assigned to the "attachment" field.

In a nut shell. I cannot figure out why with the following code that the "attached" file is anything but "l\pics\145CHWY.jpg". Every time I run this I either get the above error or if I use the "on error resume next" (I commented it out below) I don't get any errors (go figure) but the data that is stored in the attachment field of the table is simply NOT CORRECT!!!!! GGGRRRRRRRR

I'm hoping that made some type of sense if not bring on the questions.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim rs3 As DAO.Recordset

Set rs1 = db.OpenRecordset("select * from tblwebdump where style = '" & Style & "' and GoldDescription = '" & golddescription & "'", dbOpenDynaset)
Set rs2 = db.OpenRecordset("tblMemoPicture")
While Not rs1.EOF
rs2.AddNew
stringpath = "l:\Pics" & rs1.Fields("Image") & ".jpg"
rs2.Fields("GoldType") = rs1.Fields("GoldDescription")
rs2.Fields("DiamondDes") = rs1.Fields("DiamondDescription")
rs2.Fields("Style") = rs1.Fields("Style")
rs2.Fields("StoneDes") = rs1.Fields("StoneDescription")
rs2.Fields("RetailPrice") = rs.Fields("Price") * RetailMarkup
Set rs3 = rs2.Fields("Picture").Value
rs3.AddNew
'On Error Resume Next
rs3.Fields("Filedata").LoadFromFile "l:\Pics\145CHWY.jpg"
rs3.Update
rs3.Close
rs2.Update
rs1.MoveNext
Wend
rs2.Close
rs1.Close

 

[pbaldy]

I'm confused, does this resolve to the name of a table?

Set rs3 = rs2.Fields("Picture").Value

I don't see how that opens a recordset on a table, but maybe it's a method I haven't seen before. I don't (won't) use multi-value or attachment data types, but I'd expect there to be an association with a record. I'd also expect this:

rs3.Fields("Filedata").LoadFromFile "l:\Pics\145CHWY.jpg"

to use stringpath.

 

[Bradborg]

rs2 is the recordset that is used to gain access to the table ("tblMemoPicture"). rs3 is used to access the "attached" field that's associated with the rs2 recordset ("tblMemoPicture".

As for the "string path" not sure what you are meaning there. I was under the impression that the "string path" was "l:\Pics\145CHWY.jpg".

 

[pbaldy]

By string path I meant this:

stringpath = "l:\Pics" & rs1.Fields("Image") & ".jpg"

I guess that rs3 is something peculiar to an attachment field, which I don't use. I store paths to files so that the db doesn't bloat with attachments. I'm going to plead ignorance and hopefully somebody who uses the attachment field will chime in. I see searching quickly code that converted to yours wouldn't use rs3, it would look like:

rs2("Picture").LoadFile "l:\Pics\145CHWY.jpg"

So you might see if that works.