RTE 3852 (1 Viewer)

[chizzy42]

Hi, hope all is well. I'm trying to add a button to an access form that will add a more frequently used attachment to a table. I'm getting the following error . Run time error 3852 cannot update a multivalued field if its parent recordset is not in edit mode. To place the parent recordset in edit mode use the AddNew or Edit mode.
Im not sure why the field is multivalued as it is just an attachment field and im trying to go into edit mode and addnew. The code is below , can someone have a look please and see if they can find what im doing wrong..thanks

Dim rsfile As Recordset
Set rsfile = CurrentDb.OpenRecordset("tblRPS")
   'Instantiate the child recordset.
    Set rsReport = rsfile.Fields("File1").Value
    'Add a new attachment here.
    filePath = "C:\timerrf\rf.txt"
    rsReport.Edit
    rsReport.AddNew
    rsReport.Fields("FileData").LoadFromFile (filePath)
    rsReport.Update
    'Update parent record
    rsfile.Update

 

[arnelgp]

you need to put rsfile to edit mode also:

Dim rsfile As DAO.Recordset2
Dim rsReport As DAO.Recordset2
Set rsfile = CurrentDb.OpenRecordset("tblRPS")
   'Instantiate the child recordset.
    Set rsReport = rsfile.Fields("File1").Value
    'Add a new attachment here.
    filePath = "C:\timerrf\rf.txt"

    rsfile.Edit


    rsReport.AddNew
    rsReport.Fields("FileData").LoadFromFile (filePath)
    rsReport.Update
    'Update parent record
    rsfile.Update

Note that the code will add New Attachment to the First record of the Parent recordset.
If you need to put the New Attachment to a specific Record on the parent recordset
you need to filtered recordset on rsfile:

"select * from tblRPS where ID = <idnumberhere>"

also you declare your recordset as Recordset2

 

[chizzy42]

you need to put rsfile to edit mode also:

Dim rsfile As Recordset
Set rsfile = CurrentDb.OpenRecordset("tblRPS")
   'Instantiate the child recordset.
    Set rsReport = rsfile.Fields("File1").Value
    'Add a new attachment here.
    filePath = "C:\timerrf\rf.txt"

    rsfile.Edit


    rsReport.AddNew
    rsReport.Fields("FileData").LoadFromFile (filePath)
    rsReport.Update
    'Update parent record
    rsfile.Update

Hi anrnelgp, thanks that works. It doesn't seem to update the way i thought as it only updates the first record in the table no matter if i open a new form. Probably have to look at updating the table by the record ID....thanks again

 

[arnelgp]

see post#2 i edited it.

 

[chizzy42]

i just saw the bottom part of your answer.....ooops thanks

 

[moke123]

Hope you've researched the cautions\problems with MVF's and attachment fields.

 

[chizzy42]

Hope you've researched the cautions\problems with MVF's and attachment fields.

HI Moke123...is there an issue using vba to insert an attachment?

 

[moke123]

HI Moke123...is there an issue using vba to insert an attachment?

No, but the general consenses is that attachment fields and MVF's are not the best option. Attachment fields tend to bloat your database. Depending on the type of attachment you may find you reach the 2 gig limit rather quickly.
That is why I suggested you do some research on them.

 

[chizzy42]

ok thanks, the database shouldn't be getting too much info entered and i thought this method was more efficient as the user only has to add the attachment if required

 

[arnelgp]

you can have Another table (tblAttachments):

ID (autonumber) PK
RPSID (number) FK to table RPS on field ID.
Description (short text) describe the purpose of the attachment.
Attachment (short text) the location of the attachment


you will then use Insert query to this table for the
rpsID and the path+filename of the attachment.

you can use Application.FollowHyperlink, to open the attachment.

 

[NauticalGent]

The affirm Moke's comments, have a look at this article from one of our members (Isladogs).

 

[arnelgp]

getting too much info entered and i thought this method was more efficient as the user only has to add the attachment if required

it's your db and your choice.
you can always save it on separate Linked database.

 

[Pat Hartman]

Attachments and MVF use the same concept so MS probably just used the same message.

A word to the wise, NEITHER field type has a corresponding data type in SQL server and so if you ever have to upsize, (in addition to the other more immediate issues), you won't have a direct path. Not only will you need to create a properly normalized schema with additional tables but, you'll have to modify your app to use them.

 

[chizzy42]

Thanks to all for the info im trying to get more of an understanding of databases/sql/vba from the ground up, arnelgp ill try the new table idea for next db. The code i got working is below.

Dim rsfile As Recordset
Dim rps As String
Dim sql As String
rps = Me.Text191.Value

sql = "select * from tblRPS where Rpsno = " & rps
Set rsfile = CurrentDb.OpenRecordset(sql)
   'Instantiate the child recordset.
    Set rsReport = rsfile.Fields("File6").Value
    'Add a new attachment here.
    filePath = "\\RPSdb\RPSForms\PS2019TemplateRevB.xlsm"

    rsfile.Edit

    rsReport.AddNew
    rsReport.AddNew
    rsReport.Fields("FileData").LoadFromFile (filePath)
    rsReport.Update
    'Update parent record
    rsfile.Update
    Me.Requery

 

[moke123]

filePath = "\\RPSdb\RPSForms\PS2019TemplateRevB.xlsm"

Is this the file you are storing in the attachment field?

Why not just store the path to the file?

 

[chizzy42]

hi moke123, the spreadsheet that gets attached is on a server, i only put a snippet of the absolute path that is used.

 

[moke123]

My point was why store the file as an attachment when you can just store the path to the file as text. That doesn't bloat your database. You then use code to open it when needed.

 

[chizzy42]

Hi moke123, i initially had the spreadsheet open by clicking a button on the form and this could be saved on the hard drive , the users then said they wanted it all on the one page to keep everything accessible and visible. Its getting used for quality audits .I've been given another to try so ill use some of the ideas put forward here for that, its been interesting learning some new points


thanks again to all