Button that can output reports for all files (1 Viewer)

Sebbyy · Jul 27, 2017

So i have a report in my database which is generated by FID, and i would like for a button that I can use to export a PDF for every ID to a designated folder named by the FamilyName associated with that FID?

Is this possible with VBA? I'm not great at coding?

I know how to get to
DoCmd.OutputTo acOutputQuery, "YourQueryName", acFormatPDF, strPathAndFile, True

In my situation it would be

Code:

DoCmd.OutputTo acOutputReport, "Family Report", acFormatPDF,strPathAndFile=L\\\\Seb\Database Work\Family Reports\"FamilyName" & Date()

So the name ends for the PDF ends up being the familyname associated with that FID with the date the output happened.

But how do I code for it to happen for all FIDs as well?

Thanks,
Seb

isladogs · Jul 27, 2017

Your code is incorrect. Try something like this for each file:

Code:

Dim strPathAndFile As String, strDate As String

strDate=Format(Date(),"yyyymmdd") 'NOTE use a different format if you want but don't include '/' in your format as the file name won't be allowed

strPathAndFile="L:\Seb\Database Work\Family Reports\" & [FamilyName] & strDate & ".pdf"

DoCmd.OutputTo acOutputReport, "Family Report", acFormatPDF, strPathAndFile

Next to get each of the PDFs created you will need to create a recordset and loop through each item in the recordset in turn

Suggest you do a search for using recordsets and come back with your code if you have any difficulties

Sebbyy · Jul 27, 2017

Thanks alot for this.

Something like this?

Code:

Private Sub Command115_Click()
    Dim MyRs As DAO.Recordset
    Dim rpt As Report
    Dim strPathAndFile As String, strDate As String
    strDate = Format(Date, "ddmmyyyy")
    strPathAndFile = "L:\Lab_NickH\Seb\Database Work\Family Reports\" & [FamilyName] & strDate & ".pdf"
    Set MyRs = CurrentDb.OpenRecordset("FIDq")
    DoCmd.OpenReport "Family Report", acPreview, , , acHidden
    Set rpt = Reports("Family Report")
    
  
    With MyRs
        .MoveFirst
            Do While Not .EOF
                ' open report hidden and filtered
                ' save the hidden report as a PDF
            
                DoCmd.OutputTo acOutputReport, "Family Report", acFormatPDF
    
               .MoveNext
            Loop
    End With
End Sub
End Sub

isladogs · Jul 27, 2017

You just caught me before I sign off for the night
The code was close but a few mistakes

e.g. 2 End Subs ; incomplete code in DoCmd line

Make sure you have Option Explicit at the top of your code module
Then compile & check there are no code errors

Lines in RED aren't needed
Lines in BLUE MAY also not be needed - try with & without

I've added error handling to the code

NOTE: the code below is untested.
Hopefully this will work but I may have missed something

Code:

Private Sub Command115_Click()

On Error GoTo Err_Handler

    Dim MyRs As DAO.Recordset
    [COLOR="Red"]Dim rpt As Report 'NOT NEEDED[/COLOR]
    Dim strPathAndFile As String, strDate As String
    Dim strFamilyName As String

    strDate = Format(Date, "ddmmyyyy")
    
    Set MyRs = CurrentDb.OpenRecordset("FIDq")

  [COLOR="DarkSlateBlue"] 'I'm NOT sure this part is needed - try omitting it
   ' open report hidden and filtered
    DoCmd.OpenReport "Family Report", acPreview, , , acHidden[/COLOR]

  [COLOR="Red"]  'Not used anywhere - delete it
    Set rpt = Reports("Family Report")[/COLOR]
      
    With MyRs
        .MoveFirst
            Do While Not .EOF

		strFamilyName= !FamilyName	'this gets the family name field for each record
                
                ' save the report as a PDF
                strPathAndFile = "L:\Lab_NickH\Seb\Database Work\Family Reports\" & strFamilyName & strDate & ".pdf"

                DoCmd.OutputTo acOutputReport, "Family Report", acFormatPDF, strPathAndFile
    
               .MoveNext
            Loop
    End With

    Set MyRs = Nothing 'IMPORTANT - always clear a recordset after use

Exit_Handler:
	Exit Sub

Err_Handler:
	MsgBox "Error " & err.Number & " : " & err.Description
	Resume_Exit_Handler

End Sub

Sebbyy · Jul 27, 2017

So I have got the code working but my report was originally being generated by choosing from a combo box on the main menu for what FID to display.

So I made duplicates of the report and subreport and their corresponding queries however, the link on FID between report and subreport is broken and if I enter an FID for it to display to see if the code works to output everything, it shows that FID info on the report but every data point for the subreport?

Thoughts?

isladogs · Jul 27, 2017

Sorry but I don't understand.

Sebbyy · Jul 27, 2017

ridders said:
Sorry but I don't understand.

So with my report that is unlinked to a particular FID until the script, when i just open the report by itself and give it a FID, the subform does not link to that FID?

Does that make sense?

isladogs · Jul 27, 2017

Not to me I'm afraid but perhaps to someone else....?

Suggest you upload a stripped down copy of your db with a further explanation for someone to know what code to look at.

Sebbyy · Aug 4, 2017

ridders said:
Not to me I'm afraid but perhaps to someone else....?

Suggest you upload a stripped down copy of your db with a further explanation for someone to know what code to look at.

So I figured out that my subreport wasn't linking to my main report through the FID because apparently my FID is in binary data type in one of the queries and I have no clue how??

Button that can output reports for all files (1 Viewer)

Sebbyy

Registered User.

isladogs

MVP / VIP

Sebbyy

Registered User.

isladogs

MVP / VIP

Sebbyy

Registered User.

isladogs

MVP / VIP

Sebbyy

Registered User.

isladogs

MVP / VIP

Sebbyy

Registered User.

Similar threads

Users who are viewing this thread