Attaching Files

[Lhuffst]

thread702-1701099

Hi I would like to open a scanned document on a hard drive that is tied to a record. I have never done this and from all the research, I'm thinking that I need to have a browse button that opens to the root directory and then I could walk down to the child folder to select the pdf that I want to open and attach.

Looking at the thread above, it seems the closest to what I want to do but I'm not totally understanding the highlighted line. If there are going to be many different reports that could be opened, shouldn't I be directed to the folder at this point?

What would I pass to openreportandsave to make it open to a directory instead of the actual report name. I'm really not sure what is the easiest but userfriendly way to proceed. I do get that I probably don't want to save the documents in a table in access due to sizing.

Dim tempLocation As String
  Dim fldName As String
  Dim rs As DAO.Recordset
  Set rs = Me.Recordset
  fldName = "Attachments"
  [COLOR=#FCE94F]tempLocation = OpenReportAndSave("rptProductsByCategory")[/color]
  loadAttachFromFile tempLocation, rs, fldName
  'Could add code here to send and email with the outlook attachment using the saved file
  'if you want to remove the temp file at this point
  'VBA.Kill tempLocation
End Sub

Public Function OpenReportAndSave(strReportName As String) As String
    Dim myCurrentDir As String
    Dim myReportOutput As String
    Dim myMessage As String
    On Error GoTo ErrorHandler
    DoCmd.OpenReport strReportName, acViewPreview
    myCurrentDir = CurrentProject.Path & "\"
    myReportOutput = myCurrentDir & strReportName & Format(Date, "YYYYMMDD") & ".pdf"
    DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, myReportOutput, , , , acExportQualityPrint
    OpenReportAndSave = myReportOutput
    Exit Function
ErrorHandler:
    MsgBox Error$
End Function

Public Sub loadAttachFromFile(strPath As String, rsAll As DAO.Recordset, attachmentFieldName As String)
  'An attachment field has a recordset of attachments stored behind the scenes
  Dim rsAtt As DAO.Recordset
  'Add a new record to the tables recordset
  Set rsAtt = rsAll.Fields(attachmentFieldName).Value
  rsAll.Edit
    rsAtt.AddNew
     'This is the confusing part.  The value property of an attachment field returns a recordset of attachments
     'All recordset of attachments has a field named filedata which holds the data.
     'The loadfromfile data loads an attachment from a path
     rsAtt.Fields("FileData").LoadFromFile (strPath)
    rsAtt.Update
  rsAll.Update
End Sub

Thanks for any and all help
lhuffst

 

[MajP]

That code is more difficult than what you are asking. In that code the user wanted to save one of their access reports as a pdf and attach it into a record. Basically it was a snap shot of the report at a specific time. So that code had to:
1.Save the access report to the hard drive as a pdf
2. Attach the pdf to the record
3. Remove the pdf from the harddrive

It sounds as if you are just browsing for a file or have the path already. I did not write the file browser code since there are several ways to do this. You can use the standard file browser of use the API. Should be an easy google. Bottom line any version returns a path to a file.

Private Sub cmdAttach_Click()
 
  Dim fldName As String 'name of the attachement field
  Dim rs As DAO.Recordset
  Dim filePath as string
 
  Set rs = Me.Recordset
  fldName = "Attachments"
  
  'add file browser code here to get full path and name
  'filePath = ......
  
  loadAttachFromFile filePath, rs, fldName
 
End Sub


Public Sub loadAttachFromFile(strPath As String, rsAll As DAO.Recordset, attachmentFieldName As String)
 'An attachment field has a recordset of attachments stored behind the scenes
  Dim rsAtt As DAO.Recordset
 'Add a new record to the tables recordset
  Set rsAtt = rsAll.Fields(attachmentFieldName).Value
  rsAll.Edit
    rsAtt.AddNew
     'This is the confusing part.  The value property of an attachment field returns a recordset of attachments
     'All recordset of attachments has a field named filedata which holds the data.
     'The loadfromfile data loads an attachment from a path
     rsAtt.Fields("FileData").LoadFromFile (strPath)
    rsAtt.Update
  rsAll.Update
End Sub