Access Report to individual PDFs with field name filenames

[pjfb]

Hi Everyone,

Hoping someone wouldn't mind donating some time. I know the basics of VBA but I am trying to help out my father-in-law and I'm in over my head. He uses Access 2007 for a business database he runs. Here is the situation:

There is a report that when run asks for a date range (start and end date)
Once this runs, the report will generate 30-40 pages.

We want to:
(i) export to PDF (can do)
(ii) export to individual PDFs
(iii) give each PDF a filename from the fields on each exported page (ie. Client name + unique request number.pdf)

Thank you in advance,
PB.

 

[spikepl]

There is just about 100,000 posts on this subject. Do a search using the Search at the top of the page or google:

Site:http://www.access-programmers.co.uk yourSearchTerms

restricts the results to AWF

 

[pjfb]

OK I've done some research and my code is below.... I have gotten myself confused and I'm hoping for some help.

1. I want the code to save to PDF the output of the report between certain dates (user inputed once).
2. I want individual files produced by the field [CustomerID]

At the moment, PDFs are created but they are blank except the first PDF, which contains all the results.

Can anyone help please?

Private Sub BirthsPDFOutput()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim MyFileName As String
Dim mypath As String
Dim temp As String
Dim strStart As Variant
Dim strEnd As Variant

strStart = InputBox("Please enter Start date (dd/mm/yyyy)")
strEnd = InputBox("Please enter End date (dd/mm/yyyy)")
    
mypath = "E:\Access Test\Output\"

Set db = CurrentDb()

Set rs = db.OpenRecordset("SELECT [CustomerID]FROM [Transcriptions-Birth]", dbOpenDynaset)

Do While Not rs.EOF

temp = rs("CustomerID")
MyFileName = rs("CustomerID") & ".PDF"

DoCmd.OpenReport "Transcriptions - Birth Email", acViewPreview, , "([TRANSCRIPTIONS-BIRTH].Date) Between #" & strStart & "# And #" & strEnd & "# AND ([TRANSCRIPTIONS-BIRTH].CustomerID) = '" & temp & "'"
DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & MyFileName
DoCmd.Close acReport, "Transcriptions - Birth Email"

rs.MoveNext
Loop

Set rs = Nothing
Set db = Nothing

End Sub

 

[jdraw]

Pdf is the output format. If you want individual reports based on CustomerId, then you have to create such reports, then direct the output and format for each.

Are you getting pdf output from the procedure you posted?
Do you get any errors?


temp is dimmed as a string - I'm not sure the data type of CustomerId

You might make strStart and strEnd Date data types.
You can convert string to Date using CDate function.

 

[pjfb]

Thanks jdraw.

The report works ok and outputs the individual reports but generally we only filter by date. That is it would generate output for all entries in a given date range.

The code posted runs, but the first CustomerID for the date range contains all the results anda PDF with only the template and no results are generated for the subsequent customer ids. I'm very new to vba and I'm struggling to get my head around it.

 

[pjfb]

Can anyone help?

 

[vbaInet]

So let's see your code as it stands.

 

[pjfb]

Thanks!

Option Compare Database

Private Sub BirthsPDFOutput()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim dtmStart As Date
Dim dtmEnd As Date
Dim MyFileName As String
Dim mypath As String
Dim temp As String
Dim strStart As String
Dim strEnd As String
Dim strMessage As String
  
  strStart = InputBox("Please enter Start date (dd/mm/yyyy)")
  If IsDate(strStart) Then
    dtmStart = CDate(strStart)
  Else
    strMessage = strMessage & "Start date " & strStart & " is not a valid date." & vbCrLf
  End If
  
  strEnd = InputBox("Please enter End date (dd/mm/yyyy)")
  If IsDate(strEnd) Then
    dtmEnd = CDate(strEnd)
  Else
    strMessage = strMessage & "End date " & strEnd & " is not a valid date." & vbCrLf
  End If
  
  If Len(strMessage) > 0 Then
    MsgBox strMessage, vbOKOnly + vbCritical
  Else
       
    mypath = "E:\Access Test\Output\"
    
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("SELECT [CustomerID] FROM [Transcriptions - Birth Email Query]", dbOpenDynaset)
  
    Do While Not rs.EOF
  
      temp = rs("CustomerID")
      MyFileName = temp & ".PDF"
  
      DoCmd.OpenReport "Transcriptions - Birth Email", acViewPreview, , _
        "([TRANSCRIPTIONS - BIRTH].Date Between " & Format(dtmStart, "\#yyyy\-mm\-dd\#") & _
        " AND " & Format(dtmEnd, "\#yyyy\-mm\-dd\#") & ")" & _
        " AND ([TRANSCRIPTIONS - BIRTH].CustomerID = '" & Replace(temp, "'", "''") & "')"
      DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & MyFileName
      DoCmd.Close acReport, "Transcriptions - Birth Email"
  
      rs.MoveNext
    Loop
  
    Set rs = Nothing
    Set db = Nothing
  End If
  
End Sub

 

[vbaInet]

You didn't find the right post unfortunately. Have a look at the following thread (post #10):

http://www.access-programmers.co.uk/forums/showthread.php?t=206372&highlight=outputto

Or follow these steps:

1. Create three textboxes on your form and set their Visible property to False
2. Call them txtFilterStartDate, txtFilterEndDate and txtFilterID
3. Use the following amended code:

Private Sub BirthsPDFOutput()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strFileName As String
    Dim temp As String
    Dim strInput As String
    Dim strMessage As String
    
    Const MYPATH As String = "E:\Access Test\Output\"
    
    strInput = InputBox("Please enter Start date (dd/mm/yyyy)")
    If IsDate(strInput) Then
        Me.txtFilterStart = CDate(strStart)
    Else
        strMessage = strMessage & "Start date " & strInput & " is not a valid date." & vbCrLf
    End If
    
    strInput = InputBox("Please enter End date (dd/mm/yyyy)")
    If IsDate(strInput) Then
        Me.txtFilterEnd = CDate(strInput)
    Else
        strMessage = strMessage & "End date " & strInput & " is not a valid date." & vbCrLf
    End If
    
    If Len(strMessage) > 0 Then
        MsgBox strMessage, vbOKOnly + vbCritical
    Else
        
        Set db = CurrentDb()
        Set rs = db.OpenRecordset("SELECT [CustomerID] " & _
                                  "FROM [Transcriptions - Birth Email Query]", dbOpenDynaset)
        
        Do While Not rs.EOF
            
            temp = rs!CustomerID
            strFileName = temp & ".PDF"
            Me.txtFilterID = Replace(temp, "'", "''")
            
            DoCmd.OutputTo acOutputReport, "", acFormatPDF, MYPATH & strFileName
            DoCmd.Close acReport, "Transcriptions - Birth Email"
        
            rs.MoveNext
        Loop
        
        Set rs = Nothing
        Set db = Nothing
    End If
  
End Sub

4. In the underlying query of your report, point to the textboxes in criteria of the relevent fields, e.g:

BETWEEN Forms!FormName!txtFilterStart AND Forms!FormName!txtFilterEnd

... this will go under the Criteria row for the Date field. Do the same for CustomerID

By the way, Date is a reserved keyword and shouldn't be used as a field name.

 

[pjfb]

Thanks vbaInet.

I haven't made the form yet, but I just inserted Between <StartDate> and <EndDate> in the query. This is now outputting the report for right clients during the right date period ... Hooray!

But, I wasn't sure what to put in the criteria for CustomerID. At the moment it outputs all entries into all the PDFs for each customer. Can you help me with what to put in the criteria?

Thanks

 

[vbaInet]

Ok, all you do is put the full reference to txtFilterID in the Criteria row under the CustomerID field. Make sure it's on the same line as the BETWEEN criteria so that when you view it in SQL View it looks likes this:

WHERE [DateField] BETWEEN <StartDate> AND <EndDate> AND [CustomerID] = <CustomerID>

 

[pjfb]

I'm still confused. I've read so many threads and I think what I want to do I can't without forms... but I don't know forms at all.

I was just hoping to specify the dates in the query just to test without having to create a form. I have specified the dates and this works. Can I link the CustomerID criteria to the rs.CustomerID without a form or am I just fighting a losing battle? Otherwise I will have to learn the forms?

Option Compare Database

Private Sub BirthsPDFOutput()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strFileName As String
    Dim temp As String
    Dim strInput As String
    Dim strMessage As String
    
    Const MYPATH As String = "E:\Test Output\"
    
    'strInput = InputBox("Please enter Start date (dd/mm/yyyy)")
        
    'strInput = InputBox("Please enter End date (dd/mm/yyyy)")
    
    
    If Len(strMessage) > 0 Then
        MsgBox strMessage, vbOKOnly + vbCritical
    Else
        
        Set db = CurrentDb()
        Set rs = db.OpenRecordset("SELECT [CustomerID] " & _
                                  "FROM [Transcriptions - Birth Email Query]", dbOpenDynaset)
        
        Do While Not rs.EOF
            
            temp = rs!CustomerID
            strFileName2 = Replace(temp, "'", "''")
            strFileName = strFileName2 & ".PDF"
                        
            DoCmd.OutputTo acOutputReport, "Transcriptions - Birth Email", acFormatPDF, MYPATH & strFileName
            DoCmd.Close acReport, "Transcriptions - Birth Email"
        
            rs.MoveNext
        Loop
        
        Set rs = Nothing
        Set db = Nothing
    End If
  
End Sub

 

[vbaInet]

What is the point of doing things half and half? If you want to use forms and textboxes for specifying criteria you should use that. And yes you have to learn how to use forms because they are indespensable. I thought you already have a form where the code is called from so I can't see why you can't use that and make the textboxes invisible in that.

If you looked carefully at the link I gave you in post #9 you will find that I didn't use textboxes there.

 

[pjfb]

I understand what you are saying. I don't really want forms though. All I want are 2 input boxes for Start and End date. If I have to have a form I'll build one. I guess I'm asking can I get out of this without the hassle of forms?

 

[vbaInet]

Perhaps I should re-iterate to answer the question again.

If you looked carefully at the link I gave you in post #9 you will find that I didn't use textboxes there.

Professional looking dbs for entering data are made with forms not with the InputBox function. If the first InputBox pops up, the user wishes to cancel and clicks the Cancel button, the second box will pop up. You cannot circumvent this because you can't tell which button was clicked.