Counting Attachments in Access 2007

[Fred Boer]

Hello!

I am trying to learn how to manipulate attachments in Access 2007 using
code. I have some code that works (given to me, not created originally by
me), and I am having trouble modifying it.

The code below works, but only if an attachment already exists. If the
attachment does not exist it fails on "rsattachcoverpicture.delete", (as you
might expect), and I am pretty sure it's because there's nothing to delete.
So, I set up a select case structure to check the count of attachments, but
I am having trouble getting the count of attachments right and making the
code work. I tried a "movelast" thinking that might help but it doesn't.
Basically, even with 2 attachments, the code sees only 1 attachment. I've
copied what I think is the relevent code below. Any suggestions? Ideally, I
want a process that will add a new attachment if none exists, or replace the
current attachment if one exists, or produce a messagebox if more than one
attachment exists....

Thanks!


Code Snippet 1 : This code works, but not if the attachment field is empty

Dim db As DAO.Database
Dim rsItem As DAO.Recordset
Dim rsattachcoverpicture As DAO.Recordset

Set db = CurrentDb
Set rsItem = db.OpenRecordset("tblItem")

' Instantiate the child recordset.
Set rsattachcoverpicture = rsItem.Fields("AttachCoverPicture").Value


.........code

' Activate edit mode.
rsItem.Edit

' Add a new attachment.
rsattachcoverpicture.Delete
rsattachcoverpicture.AddNew
rsattachcoverpicture.Fields("FileData").LoadFromFile
oAWSDom.documentelement.selectSingleNode("Items/Item/MediumImage/URL").Text
rsattachcoverpicture.Update

' Update the parent record
rsItem.Update
Me.Refresh




Code Snippet 2: My attempt to use a select case. Even with 2 attachments,
the count of attachments is shown as 1

Option Compare Database
Option Explicit

Private Sub cboISBN_AfterUpdate()
On Error GoTo Errorhandler

..... other variables declared but omitted
Dim db As DAO.Database
Dim rsItem As DAO.Recordset
Dim rsattachcoverpicture As DAO.Recordset

Set db = CurrentDb
Set rsItem = db.OpenRecordset("tblItem")

' Instantiate the child recordset.
Set rsattachcoverpicture = rsItem.Fields("AttachCoverPicture").Value


...... other code omitted (code to pull data from Amazon web site using web
services...)


' Activate edit mode.

rsItem.Edit
rsattachcoverpicture.MoveLast

Select Case rsattachcoverpicture.RecordCount

Case 0
' Add a new attachment.
rsattachcoverpicture.AddNew
rsattachcoverpicture.Fields("FileData").LoadFromFile
oAWSDom.documentelement.selectSingleNode("Items/Item/MediumImage/URL").Text
rsattachcoverpicture.Update

' Update the parent record
rsItem.Update
Me.Refresh
Case 1
' Add a new attachment.
rsattachcoverpicture.Delete
rsattachcoverpicture.AddNew
rsattachcoverpicture.Fields("FileData").LoadFromFile
oAWSDom.documentelement.selectSingleNode("Items/Item/MediumImage/URL").Text
rsattachcoverpicture.Update

' Update the parent record
rsItem.Update
Me.Refresh
Case Is > 1
MsgBox "There are multiple images for this item!"
End Select

Else
'No items matched search
MsgBox ("No results were returned..")

End If
End If

 

[Fred Boer]

Never mind.. I solved it... I had an "rsitem.edit" ahead of the record count
process. Not sure exactly why, but moving it after the record count fixed
it.

Cheers!