Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Looping though records

Looping though records

Looping though records

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

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
                    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
                End If
                'Close form
            'Move to next record
        Let i = i + 1

    ConvertToSnp = i
End Function

RE: Looping though records

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

RE: Looping though records

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"

RE: Looping though records

Got it working

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close