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
 

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

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


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"

Thanks
Got it working