Report to pdf revisited, Again

[kmclane]

Hi there all, I have automated this as far as I can, but I still have to manually assign a file name to each report as it is printed. It seems to me that going one step further should be fairly easy, but nothing I have tried has worked so far. I am stuck in Access 97 for the time being and her is a sample of my code.

Public Function PrintAgntSettleRep()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim StrFilter As String
Dim StrName As String
Set db = CurrentDb
Set rs = db.OpenRecordset("AgentSettlementList")
If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst
End If
Do While Not rs.EOF
StrFilter = "sortcode = """ & rs(0).Value & """"
StrName = strReplaceInString(rs(1).Value, "/", "-")
If rs(0).Value = "30032001005026" Then
DoCmd.OpenReport "SettlementReport123", acViewPreview, , StrFilter
DoCmd.PrintOut acPrintAll, , , acMedium, 1
DoCmd.Close acReport, "SettlementReport123", acSaveNo
DoCmd.OpenReport "MerchantEOMExcel123", acViewPreview, , StrFilter
DoCmd.PrintOut acPrintAll, , , acMedium, 1
DoCmd.Close acReport, "MerchantEOMExcel123", acSaveNo
ElseIf rs(0).Value = "30052002001000" Then
DoCmd.OpenReport "SettlementReportAVPSWithDetail", acViewPreview, , StrFilter
DoCmd.PrintOut acPrintAll, , , acMedium, 1
DoCmd.Close acReport, "SettlementReportAVPSWithDetail", acSaveNo
DoCmd.OpenReport "MerchantEOMExcelAVPS", acViewPreview, , StrFilter
DoCmd.PrintOut acPrintAll, , , acMedium, 1
DoCmd.Close acReport, "MerchantEOMExcelAVPS", acSaveNo
ElseIf rs(0).Value = "30032001004002" Then
DoCmd.OpenReport "SettlementReportTrust", acViewPreview, , StrFilter
DoCmd.PrintOut acPrintAll, , , acMedium, 1
DoCmd.Close acReport, "SettlementReportTrust", acSaveNo
DoCmd.OpenReport "MerchantEOMExcelTrust", acViewPreview, , StrFilter
DoCmd.PrintOut acPrintAll, , , acMedium, 1
DoCmd.Close acReport, "MerchantEOMExcelTrust", acSaveNo
ElseIf rs(0).Value = "30032004001000" Then
DoCmd.OpenReport "SettlementReportPaySys", acViewPreview, , StrFilter
DoCmd.PrintOut acPrintAll, , , acMedium, 1
DoCmd.Close acReport, "SettlementReportPaySys", acSaveNo
DoCmd.OpenReport "MerchantEOMExcelPaySys", acViewPreview, , StrFilter
DoCmd.PrintOut acPrintAll, , , acMedium, 1
DoCmd.Close acReport, "MerchantEOMExcelPaySys", acSaveNo
ElseIf rs(0).Value = "30032005029000" Then
DoCmd.OpenReport "SettlementReport154", acViewPreview, , StrFilter
DoCmd.PrintOut acPrintAll, , , acMedium, 1
DoCmd.Close acReport, "SettlementReport154", acSaveNo
DoCmd.OpenReport "MerchantEOMExcel154", acViewPreview, , StrFilter
DoCmd.PrintOut acPrintAll, , , acMedium, 1
DoCmd.Close acReport, "MerchantEOMExcel154", acSaveNo
ElseIf rs(0).Value = "30032001006053" Then
DoCmd.OpenReport "SettlementReportPBPWithDetail", acViewPreview, , StrFilter
DoCmd.PrintOut acPrintAll, , , acMedium, 1
DoCmd.Close acReport, "SettlementReportPBPWithDetail", acSaveNo
DoCmd.OpenReport "MerchantEOMExcelPBP", acViewPreview, , StrFilter
DoCmd.PrintOut acPrintAll, , , acMedium, 1
DoCmd.Close acReport, "MerchantEOMExcelPBP", acSaveNo
Else
DoCmd.OpenReport "SettlementReportWithDetail", acViewPreview, , StrFilter
DoCmd.PrintOut acPrintAll, , , acMedium, 1
DoCmd.Close acReport, "SettlementReportWithDetail", acSaveNo
DoCmd.OpenReport "MerchantEOMExcelTotal", acViewPreview, , StrFilter
DoCmd.PrintOut acPrintAll, , , acMedium, 1
DoCmd.Close acReport, "MerchantEOMExcelTotal", acSaveNo
End If
rs.MoveNext
Loop
rs.Close
Set db = Nothing
End Function

I chopped a bunch of repetitve code out. As you can see I have several different versions of reports that all get run at the same time. I am filtering on a unique identifier. This all works great, but I wish I could have the code dynamically assign the path and filename and suppress the dialog box. Then I could tell it to run and be done with it. Instead I spent 7 hours clicking and typing on a Saturday to get these generated, and I have more reports every month than I had the month before. I have tried implementing the faq here and could not get it to work. I do not believe it is compatible with 97. Any suggestions?
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[M8KWR]

Have you tried the link below.

http://www.tek-tips.com/faqs.cfm?pid=703&fid=2533

if you have and are having issues with it, let me know because i use A97 and it works fine.

 

[kmclane]

Yes I have tried it, I can't get it to work. It also names the files all the same. I need different names for each file. I may try to use parts of that when I have more time to change the name as I go. I already have the code that puts the name I want to use in a variable. That code is a bit of overkill. I have each of my reports set to use the pdf printer, so I don't need to mess with the default printer, all I need to do is set the filename before printing, and suppress the dialog box that asks for it.
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg