ACCESS FEED: Re: [MS_AccessPros] Re: List of Attachements

Kamis, 29 Mei 2014

Re: [MS_AccessPros] Re: List of Attachements

Here is a relatively simple solution for maintaining a list of time stamps for all of the attachments in the database. Then you could use a subreport to show the lists of attachments with time stamps.

1) Create a table called "tblAttachmentTimeStamps"

with the following fields

attachmentName ' name of the attachment must be set to unique value

Parent_FK ' a foreign key to the record

AttachmentTimeStamp ' the date time

2) set the default value of the attachmentTimeStamp to

now()

3) on your form where you enter attachements add some code like this

Private Sub AttachmentControl_AfterUpdate()

Me.Dirty = False

UpdateTimeStamps

End Sub

4) Add this code

Public Function UpdateTimeStamps()

'Add additions to list

CurrentDb.Execute "qryLoadTimeStamps"

'remove deleted attachments from list

CurrentDb.Execute "qryRemoveTimeStamps"

End Function

5) You need the two queries

qryLoadTimeStamps

INSERT INTO

tblAttachmentTimeStamps

( attachmentName, Parent_FK )

SELECT

Products.Attachments.FileName, Products.ID

FROM

Products

WHERE

Not Products.Attachments.FileName Is Null

qryRemoveTimeStamps

DELETE

tblAttachmentTimeStamps.attachmentName,

tblAttachmentTimeStamps.attachmentName

FROM

tblAttachmentTimeStamps

WHERE

tblAttachmentTimeStamps.attachmentName

Not In (SELECT Products.Attachments.FileName FROM Products WHERE Not Products.Attachments.FileName Is Null)

So when you add or delete an attachment it upates the tblAttachmentTimeStamps. You can then join this table by the PK (in my case the Product.id)

The first time you run it, it should update your table with all the current attachments and and todays date. You may want to edit the dates to a more appropriate date.

On Wed, May 28, 2014 at 5:49 PM, ka0t1c_ang3l <no_reply@yahoogroups.com> wrote:

I inherited this database which was created by a previous employee. My supervisor just decided that he wanted this information to be printed on the compliance report. There are 72 records and each of these holds more 1 or more attachments, some of them up to 15.

I'm not very experienced with Access, thus needing the help :)

If i were to do this, I would create a new table, lets say tblAttachments. I would assume the vendor name would need to be a field to associate the attachment to the proper vendor, and an attachment field, as well as timestamp field? I would then enter a record for each attachment, correct?

I'll give it a try! :)