INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Printing Access 2007 Report with Attachments

Printing Access 2007 Report with Attachments

(OP)
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.

RE: Printing Access 2007 Report with Attachments

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.

CODE

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

CODE

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 

RE: Printing Access 2007 Report with Attachments

(OP)
Thanks for the reply MajP

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close