Printing multiple attachments in Microsoft Access

Question

Hi all,

I have searched & searched for the answer to this.

I have a Office 365 database in which I have saved multiple attachments to each record.

What I want is some Macro or code I can run to print these attachments automatically.

The attachments are mostly pdf's and run to 10 to 15 pages each.

There are about 150 records in the database.

Can I set it up to open and/or print the pdf attachment for each record?

If it opens the attachment as a pdf it would also need to close it after sending the print command (otherwise it would bring the system to a standstill). I don't mind if I can set it to print and leave for a day to do its business.

TIA,

Kevin

Answer 1

The ID field is the univoc key of record.

I suppose you are in a continuos form, a list of records, and every record is identified by an ID.

Then the Id field is a table field, the Me.Id is a form control.

Mimmo

Answer 2

Hi Mimmo,

Done as told but don't know what to substitute the "Id = " & Me.ID for?

Tried "MSDS_Attachment" and "Current record" and a few others all to no avail. Any ideas?

With current record it printed out all the records with product_name, Manufacturer and the attachment (icon only) and saved the attachment to C:\temp.

Have also adjusted the Shell to reflect file path & version of Adobe Reader. (?)

Have copied the code as below.

Cheers Kevin

Private Sub Command7_Click()

    Dim rstCurr As Recordset

    Dim rstAll As Recordset2

    Dim fldAttach As DAO.Field2

    Set rstCurr = Me.RecordsetClone

    rstCurr.FindFirst "Id = " & Me.ID   ' substitute the bold fields

    Set rstAll = rstCurr.Fields("MSDS_Attachment").Value

    Set fldAttach = rstAll.Fields("FileData")

    strFilePath = "c:\Temp" & rstAll.Fields("FileName")

    If Dir(strFilePath) <> "" Then

       VBA.SetAttr strFilePath, vbNormal

       VBA.Kill strFilePath

    End If

    fldAttach.SaveToFile "c:\temp"

    Shell "C:\Program Files (x86)\Adobe\Reader 11.0\Reader\AcroRd32.exe " & strFilePath, vbMaximizedFocus

    SendKeys "^p~", True

    SendKeys "%{F4}", True

End Sub

Answer 3

Hi,

on your form add a button to print attachment then, on this button On-click event add the code:

Private Sub PrintButton_Click()

    Dim rstCurr As Recordset

    Dim rstAll As Recordset2

    Dim fldAttach As DAO.Field2

    Set rstCurr = Me.RecordsetClone

    rstCurr.FindFirst "Id = " & Me.Id' substitute the bold fields

    Set rstAll = rstCurr.Fields("My_AttachmentFieldName").Value

    Set fldAttach = rstAll.Fields("FileData")

    strFilePath = "c:\Temp" & rstAll.Fields("FileName")

    If Dir(strFilePath) <> "" Then

       VBA.SetAttr strFilePath, vbNormal

       VBA.Kill strFilePath

    End If

    fldAttach.SaveToFile "c:\temp"

    Shell "C:\Programmi\Adobe\Reader 9.0\Reader\AcroRd32.exe " & strFilePath, vbMaximizedFocus

    SendKeys "^p~", True

    SendKeys "%{F4}", True

EndSub

Mimmo

Answer 4

Thanks Mimmo, but what do I do with the code?

I know a tiny bit but I'm afraid I'm not exactly an expert in coding.

Kevin

Answer 5

Hi Kevin,

    Dim rstCurr As Recordset

    Dim rstAll As Recordset2

    Dim fldAttach As DAO.Field2

    Set rstCurr = Me.RecordsetClone

    rstCurr.FindFirst "Mn_Id = " & Me.Mn_Id

    Set rstAll = rstCurr.Fields("Mn_Allegato").Value

    Set fldAttach = rstAll.Fields("FileData")

    strFilePath = "c:\Temp" & rstAll.Fields("FileName")

    If Dir(strFilePath) <> "" Then

       VBA.SetAttr strFilePath, vbNormal

       VBA.Kill strFilePath

    End If

    fldAttach.SaveToFile "c:\temp"

    Shell "C:\Programmi\Adobe\Reader 9.0\Reader\AcroRd32.exe " & strFilePath, vbMaximizedFocus

    SendKeys "^p~", True

    SendKeys "%{F4}", True

Mimmo