How to move to new Policy in Loop if Report is Null

  • Thread starter DoctorV3774 via AccessMonster.com
  • Start date
D

DoctorV3774 via AccessMonster.com

I have a function below that works perfectly automating output of reports to
PDF. Only problem is this. stDocname which refers to the report
Rpt_ALL_INVOICE_STATEMENT, will ALWAYS contain data. The section for
stDocName2 and stDocName3 may not contain any data, so what i want to happen
is before starting the output commands, test and see if there is data for
these reports stDocname2 or stDocName3. If there is data go through with all
of the commands if not skip that section and go to the next section, or if at
the end go to the next strPolMod

Example Before performing the steps in SEC2, check to see if stdocname2
contains any data for the particular strPolMod, if so continue, if not go to
SEC3. And for SEC3 if data continue, if not go to the next strPolMod

'***************************EXAMPLE SEC2**************************
DoCmd.OpenReport stdocname2, acPreview, , "Policy_Mod='" & rs!Policy_Mod &
"'"
DoCmd.OutputTo acReport, "", "SnapshotFormat(*.snp)", str999, False, ""
Call convertreporttopdf(stdocname2, str999, stLocation999, False, False)

DoCmd.Close acReport, stdocname2, acSaveNo

'Delete the snapshot file
Kill str999





***************CODE****************


Function OutputSnapshots()

On Error Resume Next

Dim rs As DAO.Recordset
Dim stDocName As String
Dim stdocname2 As String
Dim stdocname3 As String
Dim str As String
Dim strPolMod As String
Dim stpath As String
Dim fileendInv As String
Dim fileend999 As String
Dim fileendADIS As String
Dim stLocationInv As String
Dim stLocation999 As String
Dim stLocationADIS As String
Dim str999 As String
Dim strADIS As String

stpath = "S:\AFGITS\Customer & Environmental Management\Business Solution
Center\Funded_Deductible_Phase_II\Practice\"

fileendInv = "Invoice.pdf"
fileend999 = "999.pdf"
fileendADIS = "ADIS.pdf"
Set rs = CurrentDb.OpenRecordset("Select Policy_Mod from
Qry_ListAllValidinvoices;")

'set the report names to the string values

stDocName = "Rpt_ALL_INVOICE_STATEMENT"
stdocname2 = "Rpt_999_Transactions"
stdocname3 = "Rpt_ADIS_Drafts"
'*******************PROCESS STARTS************************
rs.MoveFirst

While Not rs.EOF
'Instantiate the Pol_Mod
strPolMod = rs!Policy_Mod
'Set Output Path of PDF

stLocationInv = stpath & strPolMod & fileendInv
stLocation999 = stpath & strPolMod & fileend999
stLocationADIS = stpath & strPolMod & fileendADIS
'String Location of Output of Snapshots
str = "C:\" & strPolMod & "_" & "Funded_Invoice.snp"
str999 = "C:\" & strPolMod & "_" & "Funded_999Trans.snp"
strADIS = "C:\" & strPolMod & "_" & "Funded_ADIS.snp"
'******************SEC1***********************************************
DoCmd.OpenReport stDocName, acPreview, , "Policy_Mod='" & rs!Policy_Mod & "'"
DoCmd.OutputTo acReport, "", "SnapshotFormat(*.snp)", str, False, ""
Call convertreporttopdf(stDocName, str, stLocationInv, False, False)

DoCmd.Close acReport, stDocName, acSaveNo

'Delete the snapshot file
Kill str
'***************************SEC2**************************
DoCmd.OpenReport stdocname2, acPreview, , "Policy_Mod='" & rs!Policy_Mod &
"'"
DoCmd.OutputTo acReport, "", "SnapshotFormat(*.snp)", str999, False, ""
Call convertreporttopdf(stdocname2, str999, stLocation999, False, False)

DoCmd.Close acReport, stdocname2, acSaveNo

'Delete the snapshot file
Kill str999
'***************************SEC3****************************
DoCmd.OpenReport stdocname3, acPreview, , "Policy_Mod='" & rs!Policy_Mod &
"'"
DoCmd.OutputTo acReport, "", "SnapshotFormat(*.snp)", strADIS, False, ""
Call convertreporttopdf(stdocname3, strADIS, stLocationADIS, False, False)

DoCmd.Close acReport, stdocname3, acSaveNo

'Delete the snapshot file
Kill strADIS



rs!strPolMod = Nothing


'Repeat the Process

rs.MoveNext
Wend

Set rs = Nothing
End Function

'*******************END OF CODE***********************************
 
K

Klatuu

If strdocname2 <> "" Then
DoCmd.OpenReport stdocname2, acPreview, , "Policy_Mod='" &
rs!Policy_Mod & "'"
DoCmd.OutputTo acReport, "", "SnapshotFormat(*.snp)", str999, False,
""
Call convertreporttopdf(stdocname2, str999, stLocation999, False,
False)

DoCmd.Close acReport, stdocname2, acSaveNo

'Delete the snapshot file
Kill str999
End If
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top