[resolved] print access reports to pdf
I am new to vb in access. Basically this code runs a quary w/ the proper sql and then I want it to print a report to pdf and go to a new query and print another pdf. I can get the report to print. My pdf printer is PdfCreator, however I get prompted for the filename. The problem is I don't know how to automatically input the filename in the PDFcreator and then Print and close, so that there is no interaction. I googled this site/FAQ/internet and found the code that was commented out, but it didn't work. Does anyone know how to do this from the vb in access.
Code:
Sub test()
Dim db As Database
Dim qry As QueryDef
Dim test As Integer
Set db = CurrentDb
db.QueryDefs.Refresh
For Each qry In db.QueryDefs
If qry.Name = "Frac Wells to Print" Then
For i = 1 To 5
Select Case i
Case 1
qry.SQL = "SELECT WellInfo.Company, WellInfo.WellID, First(WellData.Date) AS FirstOfDate, WellInfo.[Current Status], WellInfo.Pumper, WellInfo.[Year in Prod], WellInfo.Area, WellInfo.Subarea, WellData.Type, Max(WellData.Date) AS MaxOfDate FROM WellInfo INNER JOIN WellData ON WellInfo.WellID = WellData.WellID GROUP BY WellInfo.Company, WellInfo.WellID, WellInfo.[Current Status], WellInfo.Pumper, WellInfo.[Year in Prod], WellInfo.Area, WellInfo.Subarea, WellData.Type HAVING (((WellInfo.Pumper) = ""Cajun"") And ((welldata.Type) = 1)) ORDER BY WellInfo.WellID;"
Case 2
qr.SQL = "SELECT WellInfo.Company, WellInfo.WellID, First(WellData.Date) AS FirstOfDate, WellInfo.[Current Status], WellInfo.Pumper, WellInfo.[Year in Prod], WellInfo.Area, WellInfo.Subarea, WellData.Type, Max(WellData.Date) AS MaxOfDate FROM WellInfo INNER JOIN WellData ON WellInfo.WellID = WellData.WellID GROUP BY WellInfo.Company, WellInfo.WellID, WellInfo.[Current Status], WellInfo.Pumper, WellInfo.[Year in Prod], WellInfo.Area, WellInfo.Subarea, WellData.Type HAVING (((WellInfo.Pumper) = ""Delrick"") And ((welldata.Type) = 1)) ORDER BY WellInfo.WellID;"
End Select
'SaveSetting "Test1", "PDFCreator", "Document Title", "c:\Final.pdf"
DoCmd.OpenReport "ChartReportFrac+Avg"
Next i
End If
Next qry
End Sub