Ben,
I don't understand the data or the forms... and I don't really want to study to get the data entered. But I think I have most of this worked out.
I started with the report you want to print (as a PDF). I looked at the record source to see where the data is coming from. Then I found the form that had a button on it that opened the report and looked at the code to see what criteria/ parameters were needed.
I added a "print all" button and added the code below.
So....
The following code allows you to choose where you want the PDF files to be saved.
I created a new standard module ("GetFolderCode") and pasted in the following code:
Code:
Option Compare Database
Option Explicit
'************** Code Start **************
'This code was originally written by Terry Kreft.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code courtesy of
'Terry Kreft
'
'From >> http://access.mvps.org/access/api/api0002.htm
'
Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type
Private Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias _
"SHGetPathFromIDListA" (ByVal pidl As Long, _
ByVal pszPath As String) As Long
Private Declare Function SHBrowseForFolder Lib "shell32.dll" Alias _
"SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) _
As Long
Private Const BIF_RETURNONLYFSDIRS = &H1
Public Function BrowseFolder(szDialogTitle As String) As String
Dim X As Long, bi As BROWSEINFO, dwIList As Long
Dim szPath As String, wPos As Integer
With bi
.hOwner = hWndAccessApp
.lpszTitle = szDialogTitle
.ulFlags = BIF_RETURNONLYFSDIRS
End With
dwIList = SHBrowseForFolder(bi)
szPath = Space$(512)
X = SHGetPathFromIDList(ByVal dwIList, ByVal szPath)
If X Then
wPos = InStr(szPath, Chr(0))
BrowseFolder = Left$(szPath, wPos - 1)
Else
BrowseFolder = vbNullString
End If
End Function
'*********** Code End *****************
Then I opened the form "MannKendallPlot" in design view and added a button. I named the button "cmdPrintAll". I set the button caption to "Print All".
In the form module, I added this code:
Code:
Private Sub cmdPrintAll_Click()
Dim rs As dao.Recordset
Dim sSQL As String
Dim PDFPath As String
Dim PDF_FileName As String
Dim X As Integer 'counter
Dim RC As Long
Dim tmp As String
Dim Printed_PDF As Boolean
Dim Msg, Style, Title, Response
Printed_PDF = False
'get the save path
PDFPath = BrowseFolder("What Folder you want to select?")
'get the well name and COC data
sSQL = "SELECT tblLinearRegressionResults.Well, tblLinearRegressionResults.COC"
sSQL = sSQL & " FROM tblLinearRegressionResults"
sSQL = sSQL & " ORDER BY tblLinearRegressionResults.Well, tblLinearRegressionResults.COC;"
Set rs = CurrentDb.OpenRecordset(sSQL)
If Not rs.BOF And Not rs.EOF Then
rs.MoveLast
'get record count
RC = rs.RecordCount
rs.MoveFirst
X = 1
'loop throough the recordset and generate the PDF file
Do Until rs.EOF
'set the global variables
gLinearWellName = rs("Well")
gLinearChemical = rs("COC")
'generate the file name
PDF_FileName = "\WellChemical" & X & ".PDF"
'print the report
tmp = PDFPath & PDF_FileName
'you will need to modify this line - I don't have A2010 - don't know the proper command arguments
DoCmd.OutputTo acOutputReport, "rptMannKendallbyWellCOC", acFormatpdf, PDFPath & PDF_FileName '<<<<<<=============
Printed_PDF = True
X = X + 1
rs.MoveNext
Loop
If Printed_PDF Then
Msg = "Do you want merge the PDF files?" ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "Merge PDF Files" ' Define title.
' Display message.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.
'==========================================
' here is where you would put the code to combine all of the
'PDF files into one PDF file
'sites I found to combine PDF files into one PDF using VBA
'
'http://www.khk.net/wordpress/2009/03/04/adobe-acrobat-and-vba-an-introduction/
'http://www.tek-tips.com/viewthread.cfm?qid=1648313
'http://www.anysitesupport.com/access-export-report-to-pdf-using-vba/
'==========================================
Else
End If
End If
End If
rs.Close
Set rs = Nothing
End Sub
The individual PDF file names are generated with an incrementing number so that in the future you can merge the PDFs into one file. It makes it easier if you have a file name with incrementing number.
Note that there is not any error handler code.
Hopefully this will get you started in solving your problem.