Printing Access 2007 Report with Attachments 1

[aph]

Hi All,
I have a form where users can attach word docs too (in the form the data is classed as "attachments").

What I would like to do when the form is printed as a report I would like at the attachments associated with the report to be printed at the same time is this possible?

Any info would be of great help.

 

[MajP]

I think the only way to print an attachment is to first save it to disk. Then you can print, and then you could delete. A form with many records could have records with multiple attachments. So you have to loop the records, and then loop the attachments in each record. Then save each to disk and then print.

Public Sub SaveAllAttachmentsToFile(rsAll As DAO.Recordset, attachmentFieldName As String, Optional SavePath As String = "")
  'An attachment field has a recordset of attachments stored behind the scenes
  Dim rsAtt As DAO.Recordset
  Dim fileName As String
  
  If SavePath = "" Then SavePath = CurrentProject.Path & "\"
  If Right(SavePath, 1) <> "\" Then SavePath = SavePath & "\"
  If Not (rsAll.BOF And rsAll.EOF) Then rsAll.MoveFirst
  Do While Not rsAll.EOF  'Recordset of all records
    Set rsAtt = rsAll.Fields(attachmentFieldName).Value
    Do While Not rsAtt.EOF
       fileName = rsAtt.Fields("FileName").Value
       If Dir(SavePath & fileName) <> "" Then ' the file already exists--delete it first.
          If MsgBox("File already exists. Do you want to overwrite?", vbYesNo, "Overwrite?") = vbYes Then
             VBA.SetAttr SavePath & fileName, vbNormal ' remove any file attributes (e.g. read-only) that would block the kill command.
             VBA.Kill SavePath & fileName ' delete the file.
             rsAtt.Fields("FileData").SaveToFile (SavePath & fileName)
             'Print
              ExecuteFile SavePath & fileName, PrintFile
          End If
       Else
         rsAtt.Fields("FileData").SaveToFile (SavePath & fileName)
         'Print
          ExecuteFile SavePath & fileName, PrintFile
       End If
       rsAtt.MoveNext
     Loop 'The recordset of attachments for a record
       Exit Sub
       rsAll.MoveNext
   Loop 'The complete recordset
End Sub
Public Sub TestSaveAll()
  Const frmName = "frmProducts" 'your report name here
  Const attachmentFieldName = "Attachments" 'your attachment field name
  Dim frm As Access.Form
  Dim rsAll As DAO.Recordset
  DoCmd.OpenForm frmName
  Set frm = Forms(frmName)
  Set rsAll = frm.Recordset
  SaveAllAttachmentsToFile rsAll, attachmentFieldName
End Sub

code to print

Public Enum actionType
  openfile
  PrintFile
End Enum
 
Public Const SW_SHOWNORMAL As Long = 1
 
Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
  (ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, _
  ByVal lpParameters As String, ByVal lpDirectory As String, _
  ByVal nShowCmd As Long) As Long
 
Public Function ExecuteFile(fileName As String, action As actionType)
' action can be either "Openfile" or "Printfile".
 
Dim sAction As String
 
  Select Case action
    Case 0 ' openfile
      sAction = "Open"
    Case 1 ' printfile
      sAction = "Print"
  End Select
 
  ShellExecute 0, sAction, fileName, vbNullString, "", SW_SHOWNORMAL
End Function

 

[aph]

Thanks for the reply MajP