Exporting Report to Multiple .pdfs on Network Location

[sren]

Hi! I am wondering if someone can assist. I am trying to write code (which I am a little familiar with at the basic level) that will do the following within a given database:

1. Export a Report containing "Letters" to individual .pdf files (i.e. If report has 5 separate letters, I should end up with 5 separate files)
2. Save EACH report to a specific network location (i.e. d:\database\region\manager name). The "region" value and the "manager value" are contained in fields that make up the table on which the report is based. I can add them as hidden fields on the report if that helps in any way. These two fields will vary by letter. I would estimate there is 100 combinations between the two, but not sure at this point.

The letters are grouped by userID.

This is the first time I have ever posted to a forum so I am not familiar with everything I may need to provide for someone with more expertise to assist. Please let me know if I am missing anything. Again, doing this type of stuff is fairly new for me (including Access 2010; used to work on older versions).

Thanks,
sreni

[June7]

Review:
https://www.accessforums.net/import-...ges-25042.html

[sren]

Hi. Thanks so much. So I started going through the code found in the link you sent for review. Since I am new at this aspect of Access, some of it makes sense and some of it does not. Here is what I currently have:


Private Sub ctlP_LetterA_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset


rs.Open "SELECT LabNum FROM Submit;", cn, adOpenStatic, adLockPessimistic
While Not rs.EOF
DoCmd.OpenReport "rpt_Letter_A_SupportStaff", acViewPreview, , "LabNum='" & rs!LABNUM & "'"
DoCmd.OutputTo acOutputReport, "", acFormatPDF, "h:\main\testing\" & rs!LABNUM & ".pdf", True
DoCmd.Close acReport, "rpt_Letter_A_SupportStaff", acSaveNo
'kill Adobe Acrobat so I don't have thousands of open pdf documents
Dim objWMIService As Object, objProcList As Object, objProcess As Object
Set objWMIService = GetObject("winmgmts:")
Set objProcList = objWMIService.ExecQuery("Select * from Win32_Process Where Name = 'acrobat.exe'")
For Each objProcess In objProcList
objProcess.Terminate (0)
Next
rs.MoveNext
Wend
End Sub

The items in blue are the ones I am not sure of at all. Can you give me any additional guidance on these aspects of the code? I am very interested in learning the "why" not just knowing the answer.

Additional Notes/FYI:

• On the network path, I want it to determine the folder, subfolder and document name (+ ".pdf") from data contained on the report (or in the query) that the report is based on.
• This is not currently linked through an ODBC connection but may be eventually.
• This is a database that multiple people can be editing at the same time. I doubt anyone would be editing at the same time we are trying to write the files to the network drive however I do want to ensure that any changes made by others who also have the database open will be picked up and used in what the code writes to the letter.

I appreciate your assistance! (I am attaching a simple scaled back version of the database.)

Thanks so much!!!
sren

[June7]

The conventional advice for multi-user database is to split. Frontend has queries, forms, reports, code and copy is placed on each user workstation. Backend has tables and is placed on network server. This describes how I manage multi-user db http://forums.aspfree.com/microsoft-...ue-323364.html.

rs.Open "SELECT LabNum FROM Submit;"
This code opens the recordset. Change LabNum to your CustomerID field and include any other fields you want to retrieve, separated by comma and change Submit to your table name. Research SQL, here is one tutorial site http://w3schools.com/sql/default.asp.

Change LabNum to your CustomerID field in the OpenReport and OutputTo lines.

Research syntax for OutputTo method. My code names the output pdf same as the LabNum value.

'kill Adobe Acrobat so I don't have thousands of open pdf documents
I have since discovered the 'kill' code is not need if the True parameter of OutputTo is changed to False. The 6 lines of code following that comment are then not needed.

[sren]

Awesome; thank you so much! I will take a look at all of this and respond if I encounter any additional issues. Thanks again!

[sren]

Access 2010
Windows
Goal: Output of various letter types (each containing multiple people) as individual .pdf files, to a variable network folder and subfolder location

Based on our prior posts and working on this project, I now have the following code in place:

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 DISPLAY_NAME As String
Dim YR As String
Dim strFolder As String
'This line indicates which data values you will be using from your record source.
rs.Open "SELECT EmpID, LAST_NAME, FIRST_NAME, DISPLAY_NAME, NTWK_FOLDER, NTWK_SUBFOLDER, YR FROM qry_Letter_A;", cn, adOpenStatic, adLockPessimistic
While Not rs.EOF
      'The next line determines the path for the output of the selected documents.
      strFolder = "z:\2012_testing\" & rs!NTWK_FOLDER & "\" & rs!NTWK_SUBFOLDER & "\"
      DoCmd.OpenReport "rpt_Letter_A_SupportStaff", acViewPreview, , "EmpID='" & rs!EMPID & "'"
      DoCmd.OutputTo acOutputReport, "", acFormatPDF, strFolder & rs!LAST_NAME & "," & rs!FIRST_NAME & " - " & rs!YR & " Letter" & ".pdf", False
      
      DoCmd.Close acReport, "rpt_Letter_A", acSaveNo
          
      rs.MoveNext
Wend
MsgBox "Export of Letter A is complete.", vbOKOnly, "Export Complete"
End Sub
_________________________________________________________________

Private Sub ctlP_LetterB_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 DISPLAY_NAME As String
Dim YR As String
Dim strFolder As String

'This line indicates which data values you will be using from your record source.
rs.Open "SELECT EmpID, LAST_NAME, FIRST_NAME, DISPLAY_NAME, NTWK_FOLDER, NTWK_SUBFOLDER, YR FROM qry_Letter_B;", cn, adOpenStatic, adLockPessimistic
While Not rs.EOF
      'The next line determines the path for the output of the selected documents.
      strFolder = "z:\2012_testing\" & rs!NTWK_FOLDER & "\" & rs!NTWK_SUBFOLDER & "\"
      DoCmd.OpenReport "rpt_Letter_B", acViewPreview, , "EmpID='" & rs!EMPID & "'"
      DoCmd.OutputTo acOutputReport, "", acFormatPDF, strFolder & rs!LAST_NAME & "," & rs!FIRST_NAME & " - " & rs!YR & " Letter" & ".pdf", False
      
      DoCmd.Close acReport, "rpt_Letter_B", acSaveNo
      rs.MoveNext
Wend
MsgBox "Export of Letter B is complete.", vbOKOnly, "Export Complete"
End Sub

As noted above, I repeat the code from Letter A for each of the letter types (I have included B above). I do this for Letter Types B-R. (We have to do them separately instead of all at one time.) The Letter A group works fantastic. When I get to the other letter types, I am receiving errors. The errors are:

Letter B:

Run-time error '-2147217900 (80040e14)':
Syntax error in FROM clause.

Letter C:

Run-time error '-2147467259 (80004005)':
Method 'Open' of object '_Recordset' failed

Can someone assist with helping me understand where I am making my mistake(s)? Do I need to repeat all of the Dim and Set statements each time? This is all still relatively new and I appreciate both your patience if I failed to state or post anything correctly as well as your expertise in resolving.

Thank you.
sren

[June7]

Yes, the variables all need to be declared and set in each procedure unless you do a Global declaration - let's not go there for this. You declare variables but don't use them.

I don't know why the code is erroring. It looks fine to me. Not sure why Letter_A sub doesn't throw an error. The report name that opens is not the same as the report name that is closed.

However, all this should be doable with one procedure. How many letter types are there? Do you always want all letters output at same time? Or do users need to pick which letters? This should do all:

Private Sub ctlP_Letter_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
Dim strFolder As String, i As Integer, LETTER As String
For i = 1 to 3
LETTER = Choose(i,"A","B","C")
'This line indicates which data values you will be using from your record source.
rs.Open "SELECT EmpID, LAST_NAME, FIRST_NAME, DISPLAY_NAME, NTWK_FOLDER, NTWK_SUBFOLDER, YR FROM qry_Letter_" & LETTER & ";", cn, adOpenStatic, adLockPessimistic
While Not rs.EOF
      'The next line determines the path for the output of the selected documents.
      strFolder = "z:\2012_testing\" & rs!NTWK_FOLDER & "\" & rs!NTWK_SUBFOLDER & "\"
      DoCmd.OpenReport "rpt_Letter_" & LETTER, acViewPreview, , "EmpID='" & rs!EMPID & "'"
      DoCmd.OutputTo acOutputReport, "", acFormatPDF, strFolder & rs!LAST_NAME & "," & rs!FIRST_NAME & " - " & rs!YR & " Letter.pdf", False
      DoCmd.Close acReport,  "rpt_Letter_" & LETTER, acSaveNo
      rs.MoveNext
Wend
rs.Close
MsgBox "Export of Letter " & LETTER & " is complete.", vbOKOnly, "Export Complete"
Next
End Sub

You have multiple similar queries. It is possible to do this with only a single query as the source data by use of filter and/or sort criteria. Is there a field in table or query that indicates letter type?

[sren]

Hi June7,

Thanks for the feedback. Couple of responses:
- In my actual db, the report name opened and closed is the same. When deleting confidential portions in order to post it in the forum, I inadvertently deleted too much from the report close line and did not catch that.
- There are letter types A-R
- We do have to be able to select which one we want to run at the moment; we do not want to run them together since we are exporting to the network. The volume is fairly high and it will be easier to resolve issues if we only have to rerun letters for a specific letter type.
- The queries are similiar however the amount fields populated vary between letter types as does the criteria that the query looks at. It can be quite different.
- The queries are determining the logic of who gets pulled for each letter type. I suppose it is possible to turn those into update queries and update the letter type to the main table. Would it be better to work from a query we select the letter type versus multiple queries?

I tried the code you included and received the following error:
Run-time error '-2147217904 (80040e10)':
No value given for one or more required parameters.

When I go to <Debug>, it has this line highlighted:

rs.Open "SELECT EmpID, LAST_NAME, FIRST_NAME, DISPLAY_NAME, NTWK_FOLDER, NTWK_SUBFOLDER, YR FROM qry_Letter_" & LETTER & ";", cn, adOpenStatic, adLockPessimistic

I am not sure why it is erroring out.

Another thought: I have noticed that the code (orig message from yesteday) does not actually have anything to close the recordset or make it start from the beginning when the next letter type is opened. Is that required with ADO and/or could this be the problem? If so, any recommendations on what I need to include and where?

We are getting into a major timeline crunch on getting this to work. Your assistance is greatly appreciated.

Thanks,
sren

[sren]

Update: I was looking back through the code you provided and realized I was missing these lines:

Dim NTWK_FOLDER As String
Dim NTWK_SUBFOLDER As String
Dim LAST_NAME As String
Dim FIRST_NAME As String
Dim DISPLAY_NAME As String
Dim YR As String

I added these back in. Now it works fine as far as running and exporting. The only concern is that it does not allow me to pick which letter I want to do; it runs all of them. This is nice in one way however I do need to be able to run a single letter type at a time. How would I do that?

Thanks.

[June7]

If the queries are so different then I guess best to stick with what you have.

I don't understand why declaring those variables helps. They aren't used. I do wonder why fieldnames in the SQL are identical to variables.

You can explicitely close the recordset but it should close itself when the procedure ends normally. However, use a single procedure for all reports and closing recordset should not be an issue. I do show explicitely closing in my code.

Modify the procedure to have an argument. Then when calling the procedure from each button, pass letter value by argument.

Private Sub LetterReport(LETTER As String)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
Dim strFolder As String
'This line indicates which data values you will be using from your record source.
rs.Open "SELECT EmpID, LAST_NAME, FIRST_NAME, DISPLAY_NAME, NTWK_FOLDER, NTWK_SUBFOLDER, YR FROM qry_Letter_" & LETTER & ";", cn, adOpenStatic, adLockPessimistic
While Not rs.EOF
      'The next line determines the path for the output of the selected documents.
      strFolder = "z:\2012_testing\" & rs!NTWK_FOLDER & "\" & rs!NTWK_SUBFOLDER & "\"
      DoCmd.OpenReport "rpt_Letter_" & LETTER, acViewPreview, , "EmpID='" & rs!EMPID & "'"
      DoCmd.OutputTo acOutputReport, "", acFormatPDF, strFolder & rs!LAST_NAME & "," & rs!FIRST_NAME & " - " & rs!YR & " Letter.pdf", False
      DoCmd.Close acReport,  "rpt_Letter_" & LETTER, acSaveNo
      rs.MoveNext
Wend
rs.Close
MsgBox "Export of Letter " & LETTER & " is complete.", vbOKOnly, "Export Complete"
End Sub

Call the procedure from button event: Call LetterReport("A")

[sren]

Unfortunately, with my limited knowledge I am not able to answer the "why's". The main thing is that your code is a lifesaver. Thank you so very much!