Results 1 to 5 of 5
  1. #1
    EddieN1 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2011
    Posts
    313

    Modifying a Report dynamically using VBA when ADODB Connection is Open

    I want to print a series of reports. All are driven by the same report except that the report.caption varies. I am using an ADODB connection and recordset to get the successive captions.

    First I define and open the ADODB connection as follows:

    Dim Cnxn As New ADODB.Connection
    Dim strCnxn As String

    strCnxn = "Provider=Microsoft.Jet.OLEDB.4.0;" ' Access 2003
    strCnxn = strCnxn & "Data Source=" & Chr(34) & CurrentDb.Name & Chr(34)

    Cnxn.Open strCnxn



    Then I define, open, and move through the RecordSet (which I've determined isn't relevant to this problem).

    For each record in the rsList recordset (all of which works fine), I am trying to execute the following commands:

    DoCmd.OpenReport "Sales Report", acViewDesign
    Reports![Sales Report].Caption = "New Report Caption" ' this is replaced by rsList!RptName normally
    DoCmd.Close acReport, "Sales Report", acSaveYes

    All this works fine if I comment out the "Cnxn.Open strCnxn" statement. However, apparently that statement causes it to fails. It appears to open correctly, the caption is changed, however, the Close, Save doesn't save the new caption. If I simply comment out the Cnxn.Open statement, it updates the report caption.

    Is there something in the Open Connection process that put the objects in an "exclusive" state which prevents the update from working?

    Eddie

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Why do you need to edit caption? What purpose is served to open, edit, then immediately close report for each record? Is the recordset only one record?

    I open connection and recordset with:
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set cn = CurrentProject.Connection
    Set rs As New ADODB.Recordset
    rs.Open "SELECT ..."
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    EddieN1 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2011
    Posts
    313
    I left out all the irrelevant stuff, like the Recordset, SELECT, etc. The user want to "queue" up a series of about 200 reports. Each report is identical except for the filter which specifies what district is to be included (this is handled easily with the "WHERE" option in the docmd.openreport command) and the pathname that the report is to be output to (these are pdf names in the default pdf writer folder). So the report for district 100 might be Rpt100.pdf, for district 101 it's Rpt101.pdf, etc. The way to specify the output pathname is by modifying the Caption property of the report.

    Since I published my question, I have found a work around. Apparently, while the ADODB connection is open, you can't modify the report object. So, I open the connection, retrieve all the district numbers and report names and put them in an array, then close the connection. Now, I can open the reports in design mode for each entry in the array, modify the caption, and close/save the report design. Then I open the report for printing with the district number in the filter. Then I get the next element in the array. Although this is a bit of a cludge, it does work. Any better ideas are welcome.

    Eddie

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    There are 200 identical report objects?

    What code are you using to output to pdf? I have a procedure that outputs to pdf and the name is what I assign it to be, regardless of caption.

    DoCmd.OpenReport strReport, acViewPreview, , "Submit.LabNum='" & rs!LABNUM & "'"
    DoCmd.OutputTo acOutputReport, "", acFormatPDF, strPath & rs!LABNUM & ".pdf", True
    DoCmd.Close acReport, strReport, acSaveNo
    Dim objWMIService As Object, objProcList As Object, objProcess As Object
    Set objWMIService = GetObject("winmgmts:")
    Set objProcList = objWMIService.ExecQuery("Select * from Win32_Process Where Name = 'acrobat.exe'")
    For Each objProcess In objProcList
    objProcess.Terminate (0)
    Next
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    EddieN1 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2011
    Posts
    313
    Outputting to the pdf writer is the "no brainer" part of the project. The user simply makes the pdf writer the default printer. In the properties section he designates the output to overwrite existing files. So, all I need to do is give the output pdf file a name (this is where I use the Caption property) and specify a filter for each report so it includes only the necessary parts of the report. All the other stuff you normally might need aren't necessary. However, thanks for your response. I might be able to use it for another project next week or so.

    Eddie

Please reply to this thread with any new information or opinions.

Similar Threads

  1. ADODB Problem
    By albe_ACC in forum Access
    Replies: 7
    Last Post: 10-06-2011, 11:34 AM
  2. 2010 Runtime ADODB Connection Failure
    By SteveDurham in forum Access
    Replies: 2
    Last Post: 10-04-2011, 03:31 PM
  3. Closing a Dynamically Changed Report
    By EddieN1 in forum Access
    Replies: 5
    Last Post: 07-18-2011, 05:41 PM
  4. Runtime 2010 ADODB Connection failure
    By SteveDurham in forum Import/Export Data
    Replies: 5
    Last Post: 07-18-2011, 12:53 PM
  5. Adodb
    By sassy in forum Programming
    Replies: 2
    Last Post: 10-26-2009, 06:40 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums