Save a Report to An Attachment field via Button?

[galvinjaf]

Good Afternoon,

I have an attachment field in my employees table (tblEmployee) where I store unique documents to the employee (Loaned Equipment docs, warning docs, etc) and this works fine. Is is possible to save reports that I run specific to the employee to this particular employees attachment record?

Said differently, can I run a report about an employee, then click a button that will populate perhaps a list of my employees and I can select which one I want to save that report to in their attachment field?

I've done searches on google and this forum, and while I 'think' I've found something close, I can't quite find a solution...any ideas?

 

[MarkK]

No, attachments are files as recognized by Windows on your computer. A report, by contrast, is internal to Access. What you would have to do is save the report to a pdf or xps file, and then save that file as an attachment. You cab do all that in code too.

 

[galvinjaf]

What you're saying makes sense. I'm able to save my report as a pdf etc...but where might I go to figure out how to code something like that?

 

[MarkK]

I would back up a little though before exporting a file and saving it as an attachment. If you can run a specific report, why save it? You can just run it again later!!! Maybe save the parameters somewhere if it's got some complex criteria that needs to be applied, but this latter approach would be much more efficient.

 

[galvinjaf]

I hear you. The report I'm running requires a signature at the bottom. I have my employees digitally sign the report (form) which I then attach to their attachment field for their employee record.

Much of this is a manual process, and was wondering what, if any, Access can do to automate this.

 

[MarkK]

In Access you can
1) programmatically print a report to disk as a pdf, and
2) programmatically read that file in to an attachment field.

Code for 1) looks something like . . .

DoCmd.OutputTo acOutputReport, "rReportName", acFormatPDF, "C:\DestinationFolder\DestinationFileName.pdf", True

For 2), check out the DAO.Field2.LoadFromFile method, which reads a file from disk into an attachment field.

 

[arnelgp]

here is a modified version of what i posted earlier.
you must past the tablename, the attachment fieldname, pk fieldname, pk value, the name of report/pdf to save in attachment field, ie:

SaveAnyToAttachment "table1","attachment", "id", 1, "c:\somepath\sample.pdf"

Public Sub SaveAnyToAttachment( _
                                ByVal strTable As String, _
                                ByVal strAttachmentFieldName As String, _
                                ByVal pkFieldName As String, _
                                ByVal pkValue As Variant, _
                                ByVal strFileName As String)
    
    ' strTable                  Table name with attachment field
    ' strAttachmentFieldName    the fieldname of attachment
    ' pkFieldName               Primary key of table, or autonumber field
    ' pkValue                   the value that will be searched from strTable
    ' strFileName               the complete path and filename to be saved in
    '                           attachment field.

    Dim rsParent As Dao.Recordset2
    Dim rsChild As Dao.Recordset2
    Dim db As Dao.Database
    
    Set db = CurrentDb
    
    ' open table with attachment field
    Set rsParent = db.OpenRecordset(strTable, dbOpenDynaset)
    With rsParent
        '// add new record to the table
        .FindFirst "[" & pkfield & "] = " & pkValue
        If Not .NoMatch Then
            '// if we find the record save the attachment
            '// edit this record, then we can add the attachment
            .Edit
            '// set recordset to our attachment field
            Set rsChild = rsParent.Fields(strAttachmentFieldName).value
            With rsChild
                '// add new record to attachment
                .AddNew
                '// save report to attachment field
                .Fields("FileData").LoadFromFile (strSaveToFilePath)
                .Update
                .Close
            End With
            .Update
        End If
        .Close
    End With
    '// release all instance of our recordset object
    Set rsChild = Nothing
    Set rsParent = Nothing
    Set db = Nothing
End Sub

 

[galvinjaf]

I've attached the following screen shot of an error. Also, I've replaced all field/table names to match, with the exception of the highlighted piece. I'm unsure of those two, and what and where to put the code? Any help? Thank you!

J

 

[arnelgp]

on the highlighted, replace the strSaveToFilePath with strFileName.

 

[galvinjaf]

I fixed that piece, but now when I attempt to run the code, the blue highlighted part comes back with this error box?

 

[arnelgp]

you made my day bright.
you don't put it in there, put the code in a Module.
then on the Click event of your button:

Private Sub command53-Click()
SaveAnyToAttachment "tblEmployee","Field1", "ID", [Field2], "\\ts\aib\Saved_PDF\pdfFileName.pdf"
End Sub

 

[galvinjaf]

I've created a module, as you said, and created a click event, however, I get this error?

HA. I'm no programmer, that's for sure. I have a new found respect!

 

[galvinjaf]

I've figure out that I named my module the same as my event..or at least changing the module name solved the error, but now I get the following error?

 

[arnelgp]

what is your tablename, the fieldname of your primary key, the primary key value in your form, the fielname of attachment?

these are all needed by the function. please read the parameters that you must pass to this function. i already commented this inside the function.

 

[galvinjaf]

I know you commented the inside functions already, but I'm still very new to understanding how to read the codes and if i'm grabbing the correct information. My table is attached with the fields. The fieldname of the attachment is simply the paperclip icon, so it's small things like that, that confuse me.

I have a button on a report that I'm attempting to run this code on (through a module ofcourse...) All of my data comes from this table.

 

[arnelgp]

what are the controls on your form? on your table you have id, what is equivalent of this in your form?

 

[galvinjaf]

I'm sorry for being difficult. I'm not sure what you mean. That field on my form says the same thing, ID for both the label and field. In fact, I didn't opt to have that ID field visible on my form.

 

[arnelgp]

no problem if it is hidden as long as it is in the form, is it a label or textbox?

 

[arnelgp]

ok i found some problem with the code i gave you, here is the fix:

Public Sub SaveAnyToAttachment( _
                                ByVal strTable As String, _
                                ByVal strAttachmentFieldName As String, _
                                ByVal pkFieldName As String, _
                                ByVal pkValue As Variant, _
                                ByVal strFileName As String)
    
    ' strTable                  Table name with attachment field
    ' strAttachmentFieldName    the fieldname of attachment
    ' pkFieldName               Primary key of table, or autonumber field
    ' pkValue                   the value that will be searched from strTable
    ' strFileName               the complete path and filename to be saved in
    '                           attachment field.

    Dim rsParent As Dao.Recordset2
    Dim rsChild As Dao.Recordset2
    Dim db As Dao.Database
    
    Set db = CurrentDb
    
    ' open table with attachment field
    Set rsParent = db.OpenRecordset(strTable, dbOpenDynaset)
    With rsParent
        '// add new record to the table
        If IsNumeric(pkValue) Then
            .FindFirst "[" & pkFieldName & "] = " & pkValue
        Else
            .FindFirst "[" & pkFieldName & "] = " & Chr(34) & pkValue & Chr(34)
        End If
        If Not .NoMatch Then
            '// if we find the record save the attachment
            '// edit this record, then we can add the attachment
            .Edit
            '// set recordset to our attachment field
            Set rsChild = rsParent.Fields(strAttachmentFieldName).value
            With rsChild
                '// add new record to attachment
                .AddNew
                '// save report to attachment field
                .Fields("FileData").LoadFromFile (strFileName)
                .Update
                .Close
            End With
            .Update
        End If
        .Close
    End With
    '// release all instance of our recordset object
    Set rsChild = Nothing
    Set rsParent = Nothing
    Set db = Nothing
End Sub

now on the click event on your button:

Private Sub command53-Click()
SaveAnyToAttachment "tblEmployee","Field1", "ID", Me![ID], "\\ts\aib\Saved_PDF\[COLOR=Blue]pdfFileName.pdf[/COLOR]"
End Sub

substitute the blue-colored filename with the correct filename that you want to save in the attachment field.

 

[galvinjaf]

I believe it is a textbox.