Looping though records

[leafs19]

Hello
I am using the following code to print records to pdf. It is not fully working. My issue is that everytime time the code prodcues a pdf, a pop up box appears and showing records under the field name 'strDEC. For example:
Code runs: pop up for DE01, I have to enter de01, saves pdf
Code runs: pop up for DE02, I have to enter de02, saves pdf
Code runs: pop up for DE03, I have to enter de03, saves pdf
and so on...HOPE THIS MAKES SENSE.

thanks in advance for your time

Function ConvertToSnp(Import As Boolean) As Long

Dim dbs As Database
Dim tdf As TableDef
Dim qry As QueryDef
Dim rstRecordSource As Recordset
Dim rst As Recordset
Dim fldReportName As Field
Dim fldFirmNumber As Field
Dim rpt As Report
Dim frm As Form
Dim adoRS As Recordset


'User defined variables declaration
Dim strRecordSource As String
Dim strReportNameField As String
Dim strFirmNumberField As String
Dim strDirectory As String
Dim strYear As String
Dim strDEC As String

Dim i As Long
Dim strReportName As String
Dim strFirmNumber As String
Dim strCurrentFilter As String
Dim strNewFilter As String
Dim strRevisedFilter As String
Dim strReportYear As String
Dim strFileName As String
Dim strPath As String
Dim blnFilter As Boolean





'''''''''''''''''
'User defined variable definition'
'''''''''''''''''

'Name of record source for report.

Let strRecordSource = "qryCertification"

'Name of field in record source that references report name.
Let strReportNameField = "ReportName"

'Name of field in record source that references firm name.
Let strFirmNumberField = "strDEC"

'Name of directory to output files to.
Let strDirectory = "S:\NonCon\Business_Analytics\Andrea_Palermo\DataGovernance\DE Profiles\"
Let strYear = "2011_"

''''''''''
'Object definitions'
''''''''''

Set dbs = CurrentDb
Set qry = dbs.QueryDefs(strRecordSource)
Set rstRecordSource = dbs.OpenRecordset(qry.Name, dbOpenForwardOnly)
Set fldReportName = rstRecordSource.Fields(strReportNameField)
Set fldFirmNumber = rstRecordSource.Fields(strFirmNumberField)
Set tdf = dbs.TableDefs("tblSnapShotFile")





''''''''''''''
'Load table with snapshots'
''''''''''''''
Let i = 0
Do While rstRecordSource.EOF = False

''''''''''''''
'Export individual snapshot'
''''''''''''''

'Open report
DoCmd.OpenReport fldReportName.Value, acDesign
Set rpt = Reports(fldReportName.Value)

'Create new filter
Let strNewFilter = "[" & strRecordSource & "]![" & strFirmNumberField & "]=" & fldFirmNumber.Value




If rpt.FilterOn = False Then
Let blnFilter = False
Let rpt.FilterOn = True
Let strRevisedFilter = strNewFilter
Else
Let blnFilter = True
Let strCurrentFilter = rpt.Filter
Let strRevisedFilter = strCurrentFilter & " And " & strNewFilter
End If

Let rpt.Filter = strRevisedFilter

'Save new filter
DoCmd.Save acReport, fldReportName.Value

'Specifiy path
Let strReportYear = Left(fldReportName.Value, 15) & strYear & Right(fldReportName.Value, 0)
Let strFileName = fldFirmNumber.Value & "_" & strReportYear & ".pdf"
Let strPath = strDirectory & strFileName

'Output file
Set rpt = Reports(fldReportName.Value)
DoCmd.OutputTo acOutputReport, rpt.Name, acFormatPDF, strPath, False


'Restore original filter
DoCmd.OpenReport fldReportName.Value, acDesign
Set rpt = Reports(fldReportName.Value)
Let rpt.FilterOn = blnFilter
Let rpt.Filter = strCurrentFilter

'Close Report, saving restored filter
DoCmd.Save acReport, fldReportName.Value

Debug.Print fldReportName.Value

'''''''''
'Import snapshot'
'''''''''

If Import = True Then

'Open imort form
DoCmd.OpenForm "frmSnapshotFile", acNormal
Set frm = Forms("frmSnapshotFile")

'Goto new record
DoCmd.GoToRecord acDataForm, "frmSnapshotFile", acNewRec

'Assign valuation
frm.Controls("txtValuation") = "2003"

'Assign firm number
frm.Controls("txtFirmNumber") = fldFirmNumber.Value

'Assigns report name
frm.Controls("txtReportName") = fldReportName.Value

'Assign OLE snapshot
With frm.Controls("olePDF")
.Class = "PDFFile"
.OLETypeAllowed = acOLEEmbedded
.SourceDoc = strPath
.Action = acOLECreateEmbed

'With frm.Controls("oleSnapShot")
'.Class = "SnapShotFile"
'.OLETypeAllowed = acOLEEmbedded
'.SourceDoc = strPath
'.Action = acOLECreateEmbed
End With

'Commit to table
frm.Repaint

End If

'Close form
DoCmd.Close

'Move to next record
rstRecordSource.MoveNext

'Stop
Let i = i + 1
Loop

ConvertToSnp = i

End Function

 

[CP60]

This looks like VBA in some MS Office application. Ask in one of the VBA forums.

 

[MazeWorX]

is strDEC the valid name of the field? it looks as if the function doesn't know what it is so it prompts you for the value

Let strFirmNumberField = "strDEC"

How about a Debug.print strFirmNumberField after the recordset is loaded



HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[leafs19]

Thanks
Got it working