No Data when running report

system · September 15, 2003, 1:07pm

I have a report that ran perfectly fine. Then I added some macro code to split the report out into seperate pdf files. Now when I run the report the report does not contain any data. I mean when I first open up the report and do a refresh the data refreshes fine. If I go and run the macro it causes the report to lose all it’s data, the macro prints the report pdf files but the files don’t have any data because the report doesn’t have any data in it. If I go back and try and refresh the report by itself, it still won’t bring the data back in. I know the data is fine because I can see it when I do view data. So what is causing the report to lose it’s data and how do I get it back?

Thanks
Lori

bachman (BOB member since 2002-09-24)

system · September 15, 2003, 1:10pm

Lori -

Could you share the code of the “guilty” macro?

JennFisher (BOB member since 2002-06-25)

system · September 15, 2003, 1:24pm

Sure, No Problem.

Sub Export_as_PDF()

Dim objSession As MAPI.Session ' Local
Dim objMessage As Message  ' local
Dim objRecip As Recipient
Dim NameVar As Variant
Dim NameVal As Variant, ccval As Variant, FullNameVal As Variant
Dim GendernameVar As Variant
Dim SectionVar As Variant
Dim ToLanIdVar As Variant
Dim DocName, NewName, DocPath As String
Dim Userid As String
Dim CCName As String
Dim aCCName() As String
Dim aBCCName() As String
Dim rs As New ADODB.Recordset
Dim sConnectString As String
Dim sSQL As String
Dim sDSN As String
Dim sServerName As String
Dim sUID As String
Dim sPWD As String
Dim aData As Variant
Dim X As Integer, NameIndex As Integer
Dim sDelim As String
Dim pdfApp As Acrobat.CAcroApp
Dim pdfDoc As Acrobat.CAcroPDDoc
Dim DocPathDate As String
Dim Sender As AddressEntry
Dim Lists As MAPI.AddressLists
Dim list As MAPI.AddressList
Dim entries As MAPI.AddressEntries
Dim filter As MAPI.AddressEntryFilter
Dim entry As MAPI.AddressEntry


'Dim SepVal As Variant
'Dim filtervar As Variant
'Dim filter, filterText As String

Dim TodaysDate As String
Dim n As Integer

Set pdfApp = CreateObject("AcroExch.App")
Set pdfDoc = CreateObject("AcroExch.PDDoc")

sDSN = "PROD"
sServerName = "SERVER_NAME"
sUID = "xxx"
sPWD = "xxxxx"
NameIndex = 1

TodaysDate = Format(Date, "MMDDYYYY")
'Pathing = "\\ATRMJBO2\DATA1\MISDATA\HR\PRINT\BUSOBJ\"
DocPath = "H:\MISDATA\HR\PRINT\BUSOBJ\Q106"
'DocPath = "Q:\MISDATA\HR\print\BUSOBJ\Q106"

'filterText = "v_section"
'filtervar = ActiveDocument.Evaluate("=<" &amp; filterText &amp; ">", boUniqueValues)

DocName = ActiveDocument.Name
NameVar = ActiveDocument.Evaluate("=<v_section>", BoAllValues)
'GendernameVar = ActiveDocument.Evaluate("=<GENDERNAME>", BoAllValues)
'FullNameVar = ActiveDocument.Evaluate("=<FULLNAME>", BoAllValues)
SectionVar = ActiveDocument.Evaluate("=<Section Code>", BoAllValues)
ToLanIdVar = ActiveDocument.Evaluate("=<Lan User Id>", BoAllValues) 'don't need this for Q106
'DocPath = "\\atrobj01\edrive\busobj\nobookmarks\Notification Reports"
DocPathDate = Format(Now, "mmm") &amp; Year(Now)
    
    For Each NameVal In NameVar
        
        For n = 1 To ActiveDocument.Reports.Count
           Call ActiveDocument.Reports(n).AddComplexFilter("v_section", "=<Section Code> = """ &amp; _
                                                            NameVal &amp; """")
           ActiveDocument.Reports(n).ForceCompute
        Next n
        'Creating the directory if it doesn't exist ie: Jun2003
        If Dir(DocPath &amp; "\" &amp; DocPathDate, vbDirectory) = "" Then
           MkDir DocPath &amp; "\" &amp; DocPathDate
        End If
        
        NewName = DocPath &amp; "\" &amp; DocPathDate &amp; "\" &amp; NameVal    'for PathName\Filter.rep
        ActiveDocument.ExportAsPDF (NewName)  'creates file on server
        
        'The following lines of code opens the document and resaves it without the bookmarks
        If pdfDoc.Open(NewName &amp; ".PDF") Then   'if the document is opened successfully
           pdfDoc.SetPageMode 1
           Call pdfDoc.Save(1, NewName &amp; ".PDF")
           pdfDoc.Close
        Else
           MsgBox "Could not open PDF file " &amp; NewName &amp; ".PDF", vbCritical
           End
        End If
        
        sSQL = "SELECT LAN_USER_ID FROM HR.PERSNOTF WHERE MEMO = 'TO' and Section = " &amp; SectionVar(NameIndex) &amp; _
               " ORDER BY DECODE(ADMIN_CODE, 'AC','1','HS','2','AD','3','4')"
        'sConnectString = "Data Source=" &amp; sDSN &amp; ";SERVER=" &amp; sServerName &amp; ";UID=" &amp; sUID &amp; ";PWD=" &amp; sPWD &amp; ";"
        sConnectString = "Provider=MSDAORA;" &amp; "Data Source=prod;" &amp; "User ID=XX;" &amp; "Password=XXXX;" &amp; "database=prod;"
        'open a recordset
        rs.Open sSQL, sConnectString
        ReDim aCCName(0) As String
        
        Do Until rs.EOF
            ReDim Preserve aCCName(UBound(aCCName) + 1) As String
            aCCName(UBound(aCCName) - 1) = rs!LAN_USER_ID
            rs.MoveNext
        Loop

        rs.Close
        Set rs = Nothing
        
        'Doing a select to get the BCC names.
        sSQL = "SELECT LAN_USER_ID FROM HR.HR_STAFF WHERE NAME LIKE 'BACHMAN, LORI%' " &amp; _
                                                      "OR NAME LIKE 'CARTER, LINDA%' "
        'sConnectString = "Data Source=" &amp; sDSN &amp; ";SERVER=" &amp; sServerName &amp; ";UID=" &amp; sUID &amp; ";PWD=" &amp; sPWD &amp; ";"
        sConnectString = "Provider=MSDAORA;" &amp; "Data Source=prod;" &amp; "User ID=XX;" &amp; "Password=XXXX;" &amp; "database=prod;"
        'open a recordset
        rs.Open sSQL, sConnectString
        ReDim aBCCName(0) As String
        
        Do Until rs.EOF
            ReDim Preserve aBCCName(UBound(aBCCName) + 1) As String
            aBCCName(UBound(aBCCName) - 1) = rs!LAN_USER_ID
            rs.MoveNext
        Loop

        rs.Close
        Set rs = Nothing
        
        
        
        'Try sending out the e-mail here
        Set objSession = CreateObject("MAPI.Session")
        'Use a profile which exists on your Exchange Server as mailid.We created bo_admin for ours
        objSession.Logon profileName:="Lori Bachman", ProfilePassword:="XXXX", newSession:=False, showDialog:=False
        
        If objSession Is Nothing Then
            Err.Raise 10, "MA MACRO", "must first log on; use Session->Logon"
            Exit Sub
        End If
        
        Set objMessage = objSession.Outbox.Messages.Add
        If objMessage Is Nothing Then
            Err.Raise 11, "MA MACRO", "could not create a new message in the Outbox"
            Exit Sub
        End If
        
        With objMessage
            Set Lists = objSession.AddressLists
            Set list = Lists.Item(1)
            Set entries = list.AddressEntries  'Gets address entries
            Set filter = entries.filter
            filter.Name = "Jennifer Hill"    'Sending e-mail out for this person
            Set entry = entries.GetFirst()
            Set .Sender = entry
 
            .Subject = "Quarterly Promotion Report"
            .Text = "Attached is the quarterly professional promotion eligibility report for all attorneys, economists, and financial analysts." &amp; Chr(13) &amp; _
                    (Chr(13) + Chr(10)) &amp; (Chr(13) + Chr(10)) &amp; "Thanks," &amp; Chr(13) &amp; "Michele Garvey"
            Set objAttach = .Attachments.Add      ' add the attachment
            
            If objAttach Is Nothing Then
                 Err.Raise 12, "MA MACRO", "Unable to create new Attachmentobject"
                 Exit Sub
            End If
            
            With objAttach
                .Name = NameVal &amp; ".pdf"  'this is the name of the report
                .Source = DocPath &amp; "\" &amp; DocPathDate &amp; "\" &amp; NameVal &amp; ".pdf"
            End With
              
            .Update      'update message to save attachment in MAPI system
        
            Set objRecip = .Recipients.Add
            With objRecip
                objRecip.Name = ToLanIdVar(NameIndex) 'Substitute with individual mailid or groupname
                objRecip.Type = CdoTo
                objRecip.Resolve
            End With
                
            For X = 0 To UBound(aCCName) - 1
                Set objRecip = .Recipients.Add
                With objRecip
                   objRecip.Name = aCCName(X)      'Substitute with individual mailid or groupname
                   objRecip.Type = CdoCc
                   objRecip.Resolve
                End With
                .Update
            Next X
            
            For X = 0 To UBound(aBCCName) - 1
                Set objRecip = .Recipients.Add
                With objRecip
                   objRecip.Name = aBCCName(X)      'Substitute with individual mailid or groupname
                   objRecip.Type = CdoBcc
                   objRecip.Resolve
                End With
                .Update                'update message to save Bcc names in MAPI system
            Next X
            
         '  .Send showDialog:=False
        End With
        
        'Kill DocPath &amp; "\" &amp; DocPathDate &amp; "\" &amp; NameVal &amp; "-" &amp; DocName &amp; ".pdf"
           
        objSession.Logoff
        Set objSession = Nothing
        Set objRecip = Nothing
        Set objAttach = Nothing
        Set objMessage = Nothing
        NameIndex = NameIndex + 1
    Next
    
    For n = 1 To ActiveDocument.Reports.Count
        Call ActiveReport.AddComplexFilter("NAME", "=<NAME> =<NAME>")
        ' To delete a Complex Filter, you set it equal to itself...
        ActiveReport.ForceCompute
    Next n
    
End Sub

bachman (BOB member since 2002-09-24)

system · September 15, 2003, 1:30pm

First comment – it looks like you’re adding a complex filter. Is that filtering all your values out? Take a look at the active filters…

JennFisher (BOB member since 2002-06-25)

system · September 15, 2003, 2:26pm

That filter is used to split the reports up into the different PDF files.

If I delete the VB macro and try to refresh the report the values still do not come back to the screen. What would cause it from putting the values on the screen?

Thanks

bachman (BOB member since 2002-09-24)

system · September 16, 2003, 7:35am

You code has me a little confused.

You should be using something like:


filterText = "Section Code"
filtervar = ActiveDocument.Evaluate("=<" &amp; filterText &amp; ">", boUniqueValues)

For Each sepval In filtervar
    Filter = sepval
    For n = 1 To ActiveDocument.Reports.Count
        Call ActiveDocument.Reports(n).AddComplexFilter(filterText, "=<" &amp; filterText &amp; "> = " &amp; _
            Chr(34) &amp; Filter &amp; Chr(34))
        ActiveDocument.Reports(n).ForceCompute
    Next n
        NewName = Pathing &amp; Filter &amp; "-" &amp; DocName             'for Filter-Report.rep
        ActiveDocument.ExportAsPDF  (NewName)          'to Save as PDF
Next

    For n = 1 To ActiveDocument.Reports.Count
      Call ActiveReport.AddComplexFilter(filterText, "=<" &amp; filterText &amp; "> = <" &amp; filterText &amp; ">")
        ' To delete a Complex Filter, you set it equal to itself...
      ActiveReport.ForceCompute
    Next n
   
End Sub

This would create/edit the filter to read as: = = “BLAH”
Stepping through each unique value of Section Code. Once it was complete it would set: = =
thereby “removing” the filter (since the current Object Model does not allow for deleting filters.

Your Add Complex filter object doesn’t match the object that you are filtering, nor do I see where you turn this off. At the bottom of your Script you set the equal to itself, but you aren’t applying the filter to the name.

I guarantee that if you choose to Edit your Filters, you will still find one active… with no values active (meaning you are filtering on values and not showing any results). Delete this filter and your report should show your refreshed data.

-RM

digpen (BOB member since 2002-08-15)

system · September 16, 2003, 12:50pm

ok, thanks. I fixed my code. I also changed it to be uniquevalues instead of all values.

One more question - Can you tell me why the ToLanId does not go with the section? In the code I say objRecip.Name = ToLanIdVar(NameIndex)

I am on the first section, so shouldn’t I be getting the lanid that goes with that section? My NameIndex is 1.

NameVar = ActiveDocument.Evaluate("=<v_section>", BoAllValues) 
BoAllValues) 
SectionVar = ActiveDocument.Evaluate("=<Section Code>", BoAllValues) 
ToLanIdVar = ActiveDocument.Evaluate("=<Lan User Id>", BoAllValues)

The answers to these variables should all go together, but they seem to sorting alphabetically. For instance SectionVar should go with ToLanidVar, meaning the persons lanid should match the section he is in.

Thanks

bachman (BOB member since 2002-09-24)