Export Access Rpt to single .pdfs on network using variable folder names

[A12help]

Hi...I could use assistance from someone with more expertise than I. Dealing with this type of code in Access is new for me

I am trying to export a report containing multiple letters to people (from Access 2012) to individual .pdf files on the network. I understand I need a loop to do this which I have never done before. The path to save the letters in (folder and subfolder) as well as the file name should be derived from values/columns contained in the table or query which the report is written on. I am using ADO commands but not even sure that is the best option as I have found conflicting information as to which is best ADO or DAO. Any assistance will be so greatly appreciated. kindest regards.

[TheSmileyCoder]

Please confirm that you are using access 2012, I have heard of no such thing.

A few details would be nice as well. What type of records are we dealing with? Whats the table and report name? What the unique ID on which you wish to filter the report?

[A12help]

Additional Info

Hi. I am using Access 2012.

Here is a very scaled back version of the database; it will contain many more letter types, each with different data. The only data currently contained in the database is what I am dealing with (table, query, report and form.).

All assistance is greatly appreciated.

Thanks.

[zmbd]

Is that on the Apples?
That is not a version that I can find any information on outside of that context.

Also, unless asked for, most people here will not download an attched file from a new user... nothing personal... just precautionary.

What we need to see is just the SQL, VBA, and a detailed description of your tables with any relationships between them.

ADO vs. DAO...
Depends on what you want to do... if you'll never use a "real" SQL server such as Oracle, SQL-Server, etc.. and will be staying in the Access structure then you'll be much happier with the DAO construct. HOWEVER, if you might be upgrading or needing to connect to the other types of severs then ADO.
I use both - I just explicitly declare my connections and record sets to ensure that the correct structure is used.
Choosing ADO or DAO...

[TheSmileyCoder]

No offense intended, but as zmbd says, I for one will not download a db from a unknown user.

Also remember it is your responsibility to declare a question in a clear and understandable manor with the details required, it is not fair to expect us to spend alot of time decoding the construct of your database. If you, the developer, are unable to describe it in a post, chances are I wont be able to guess it from looking at your db.

[NeoPa]

Hi A12Help.

As a new user I doubt you would be aware of some of the conventions (and indeed rules) that we expect members to adhere to. These are mainly for members' own benefit, as we know from experience which types of questions get the most benefit from responders. Attachments can be requested at times, but generally a clear description of the situation is called for, to enable experts to determine whether they feel they can or want to help. I'm sure you appreciate that we have many threads to deal with every day and if every one of them required that much effort on the part of the expert just to understand what the question is then little would be achieved, and many experts would find it hard to keep any interest.

Your attitude seems fine. I just feel you need to understand the dynamics a little to realise why it's important that you, the questionner, take responsibility for formulating a clear question that we can work with.

I can certainly say that most members who appreciate this, and who have the capability of clear expression (Not all manage that unfortunately), get great help from our experts whose willingness to help is unquestioned.

There are links to the rules in various places of course, and threads in the Access Answers forum that explain how best to go about formulating a question for best results. I'll leave that with you :-)

[A12help]

Dear zmbd,

Thank you for the information on ADO vs DAO; I will keep this in mind and retain the shared link you have provided. Many thanks from my side for this. And I understand on the attachment; someone else recommended if maybe I included then it would be easier to for someone to assist me.

Dear NeoPa,

Thank you kindly for all information you have shared and the manner in doing so. I apologize to all for my lack of expertise in dealing with this; it is all very new. I fear that the expertise level found in this forum far exceeds even my ability to properly formulate the questions I have. I will reattempt though one additional time.

Access Vsn: 2012
Windows Vsn: Windows 7, 32-bit

Question: Can someone give me the code when doing an export of a multi-page report in Access 2012, to individual .pdf file format documents with each file having its own variable destination folder and sub-folder?

I have attempted to work through this in every way possible. All other code using variables works correctly except for the destination folder and sub-folder. Here is my code (again I am very new at this and just starting to learn):

Private Sub ctlP_LetterA_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
Dim NTWK_FOLDER As String
Dim NTWK_SUBFOLDER As String
Dim LAST_NAME As String
Dim FIRST_NAME As String
Dim FULL_NAME As String
Dim INFOYR As String


rs.Open "SELECT EmpID, LAST_NAME, FIRST_NAME, NTWK_FOLDER, NTWK_SUBFOLDER, INFOYR FROM qry_Letter_A_supportinfo;", cn, adOpenStatic, adLockPessimistic
While Not rs.EOF
       DoCmd.OpenReport "rpt_Letter_A_SupportStaff", acViewPreview, , "EmpID='" & rs!EMPID & "'"
       DoCmd.OutputTo acOutputReport, "", acFormatPDF, "z:\2012_testing\" & rs!LAST_NAME & "," & rs!FIRST_NAME & " - " & rs!INFOYR & " Info" & ".pdf", False
       DoCmd.Close acReport, "rpt_Letter_A_SupportStaff", acSaveNo
      rs.MoveNext
Wend
End Sub

Kindest regards.
A12help

Hi A12Help.

As a new user I doubt you would be aware of some of the conventions (and indeed rules) that we expect members to adhere to. These are mainly for members' own benefit, as we know from experience which types of questions get the most benefit from responders. Attachments can be requested at times, but generally a clear description of the situation is called for, to enable experts to determine whether they feel they can or want to help. I'm sure you appreciate that we have many threads to deal with every day and if every one of them required that much effort on the part of the expert just to understand what the question is then little would be achieved, and many experts would find it hard to keep any interest.

Your attitude seems fine. I just feel you need to understand the dynamics a little to realise why it's important that you, the questionner, take responsibility for formulating a clear question that we can work with.

I can certainly say that most members who appreciate this, and who have the capability of clear expression (Not all manage that unfortunately), get great help from our experts whose willingness to help is unquestioned.

There are links to the rules in various places of course, and threads in the Access Answers forum that explain how best to go about formulating a question for best results. I'll leave that with you :-)

[TheSmileyCoder]

I dont know if the Docmd.OutputTo will create the directory for you (You can test that yourself, or try to look it up.)

If not, you can use this code (Place in a public module) to create the folder first:

Public Function MakeDir(ByVal strPath As String) As Boolean
    'creates a directory independent of whether the parent directory exists
    'Code by TheSmileyOne
    'Version 0.1
    'Date 2010-05-19
    
    'Known issues
    'No error handling for cases such as network drives, with restricted permissions to create folders.
    'No input validation
    
    
    On Error GoTo err_Handler
    
    'Check if rightmost char is a \
    If Right(strPath, 1) = "\" Then
        'Strip it
        strPath = Left(strPath, Len(strPath) - 1)
    End If
    
    'Check if each individual directory exists, and if not, create it
    Dim strSplitPath() As String
    ReDim strSplitPath(UBound(Split(strPath, "\")))
    strSplitPath = Split(strPath, "\")
    Dim intI As Integer
    Dim strCombined As String
    For intI = 0 To UBound(strSplitPath)
        If intI <> 0 Then strCombined = strCombined & "\"
        
        strCombined = strCombined & strSplitPath(intI)
        If Dir(strCombined, vbDirectory) = "" Then
            MkDir strCombined
        End If
        
    
    
    Next
    MakeDir = True
Exit Function
err_Handler:
    MakeDir = False
        MsgBox "Error " & Err.Number & " occured." & vbNewLine & Err.Description
    
End Function

Your code could look something like this then:

Dim strFolder as string
While Not rs.EOF
  strFolder="z:\2012_testing\" & rs!LAST_NAME & "\"
  If MakeDir(strFolder) then
    DoCmd.OpenReport "rpt_Letter_A_SupportStaff", acViewPreview, , "EmpID='" & rs!EMPID & "'"
    DoCmd.OutputTo acOutputReport, "", acFormatPDF, strFolder & rs!FIRST_NAME & " - " & rs!INFOYR & " Info" & ".pdf", False
       DoCmd.Close acReport, "rpt_Letter_A_SupportStaff", acSaveNo
  Else
    Msgbox "Folder [" & strFolder & "] could not be created"
  End If
      rs.MoveNext
Wend

[A12help]

Resolved!

TheSmileyCoder,

My code, as I had it, would not create the directories if they did not already exist. Having them created would have been awesome but I was afraid to even think of going there yet. So you have already assisted more than I dreamed.

Thank you for covering the code I will need to make this a success! I appreciate your time (and everyone's who has looked at this).

Kindest regards,
A12help

[NeoPa]

Excellent answer from Smiley. I'm so pleased to see such a positive result.

@A12Help - If you were at all wondering, had you not reset my post from Best Answer then I would have had to anyway. I appreciate that it was helpful to you, but it didn't deal with the question of the thread so was not an appropriate choice for that, so you did absolutely the right thing there. Nevertheless I'm very pleased you found it helpful of course :-)

[A12help]

Dear NeoPa....I understand. At the time, your's was the best answer and while very helpful, very grateful for Smiley's help which directly addressed my question/problem (once I was able to formulate it better) and thus it moved into 1st place for best answer. ;)