Code HELP

[toleafs]

Hello
Any help is appreciated...
I have the following code that produce pdf files. The issue I am having is that everytime the code loops to another record I get a prompt Box with the 'strDEC' as the tiltle.
Little confusing....
Any ideas.... Thanks in advance for your help.

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


'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 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

With frm.Controls("olePDF")
.Class = "PDFFile"
.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

 

[Trevor G]

Have you declared strDEC it doesn't seem to show in your code

 

[toleafs]

Yes

Dim strDEC as String

Same error message

I also the following in the report:

Filter: qryCertification!ReportName="rptCertificate"
Filter on Load: yes
Order by on load: yes
allow filters: yes

 

[Trevor G]

Sorry but I have looked again and don't see it in the code you have posted.

you have strDirectory but not strDEC

 

[toleafs]

Trevor
I added after you mentioned it. See Below

I think the issue is with:

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


______
Function ConvertToSnp(Import As Boolean) As Long

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

Thanks

'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

 

[james_lankford]

in this query
"qryCertification"
do you have a field named strDEC ?

and why do you have all those LETs all over the place ?
they're probably not hurting, but you don't need them and I always think that if you don't need it, don't put it in
just less to go wrong

 

[toleafs]

Yes there is a field called strDEC

this was code I used elsewhere, and didn't bother to change it..

thanks