Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    154
    Thanks
    29
    Thanked 3 Times in 2 Posts

    Access 2010 Report Close Dialogue Issue

    I have an Access 2010 Report that contains an OLE unbound object that is linked to an Excel Chart.

    The Report Activate Event Procedure is:

    Code:
    Private Sub Report_Activate()
    
    
    
    Me!OLEChart.Action = acOLEUpdate
    
    
    End Sub
    The inclusion of this Event Procedure ensures that any updates to the Excel Chart linked to the unbound OLEChart object are displayed.

    Now, however, when I close the Report the following dialogue appears:

    20130120 Report Close Dialogue.JPG

    As I have no need to save the changed Report, I need some VBA code to prevent that dialogue from appearing and requiring to be clicked "OK" so that the Report closes.

    Any suggestions will be appreciated.

    Cheers

    Trevor

  2. #2
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts
    I have had difficulty replicating your report, as I get an error message when I try to activate it. I checked your previous post, and have set all my properties in the same way as you, except that I am unsure how to reference the Source Item, which is just called Chart1 in my TestChart.xlsx file.

    (When I try to open the report, I get Run-time error 2801: "The OLE object isn't loaded because the unbound ActiveX control hasn't been initialized." When I try to activate the open report from another object, I get Run-time Error 2793: "(databasename) can't perform the operation specified in the Action property of the Visual Basic procedure you're trying to run." I assume both relate to inadequate referencing of the source item.)

    In the absence of a reliable test report, I am wondering whether it might help to set Me.Dirty = False in the Close event for the report? Or is that so obvious you've already tried it?

    Cheers

    AliC

  3. The Following User Says Thank You to alifrog For This Useful Post:

    BygAuldByrd (2013-01-24)

  4. #3
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    154
    Thanks
    29
    Thanked 3 Times in 2 Posts
    Hi AliC,

    Thanks for responding.

    I tried Me.Dirty = False, but that doesn't resolve the issue.

    A fundamental problem with the standard error dialogue is that it doesn't indicate what "object is locked". Is it the Report, or the OLEUnbound object on it, that is locked object? The only other objects on the Report are Textboxes and I can't see how they could be an issue as I have the "same" Textboxes on many of my other Reports, and I have no issues (or unbound OLE objects) on those other Reports.

    I'm what I'd class as an advanced novice at VBA programming, so I have no idea as to the inner working of VBA. One thought that springs to mind is that as my Excel Chart is Linked to the OLEChart object, is the error message referring to the OLEChart object being locked because Excel has not closed after being opened to provide the chart for the Report. This thought is based on the Report occasionally closing without throwing up the error dialogue, suggesting a critical timing issue may be involved.

    Any more thoughts or suggestions on this issue will be appreciated.

    Cheers

    Trevor

  5. #4
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts
    Hi Trevor -

    Yes, I'm pretty sure it will be the chart that is causing the problem. It sounds like an issue I've had with Outlook - after creating an e-mail from Access, I would try to leave Outlook in the same state as the system found it, depending on whether the user already had Outlook open before the message was created. It didn't always work, suggesting to me that some kind of timing/sequencing was affecting it.

    Would I be correct in assuming that you would have a similar problem? That is, you can't just ask Access to close Excel, because the user might already have it open for other purposes, and want to leave it that way?

    There are various things I would try if faced with this problem. The simplest might be to see if you can disable warnings just while the report is being closed. Is the report opened via VBA? If so, perhaps you could use DoCmd.SetWarnings False on opening the report, and DoCmd.SetWarnings True after closing it. Bear in mind that resetting them to True afterwards is very important, or you won't get any warnings in the future; so you need to be sure you can guarantee that they will be reset to On.

    Meanwhile, if you can let me know what you have entered in the Source Item property of the report, I can perhaps replicate the problem here and see what would be the best workaround.

    Cheers

    AliC

  6. The Following User Says Thank You to alifrog For This Useful Post:

    BygAuldByrd (2013-01-29)

  7. #5
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    154
    Thanks
    29
    Thanked 3 Times in 2 Posts
    Hi AliC,

    At times patience runs out as frustration takes over. Something really screwing was happening with my chart report - it was causing my computer to freeze completely That frustration finally made me delete the offending report and completely rebuild it - took about 5 minutes after hours of frustration trying to work out what was amiss - and now wouldn't you know, it now works perfectly"Clapping: At least for the moment

    I didn't get to try you latest suggestions, but I'll keep it in mind for the future. I prefer to allow error messages and to resolve any error issues, just in case I've screwed something else up

    Thanks for your help, it is very much appreciated.

    Cheers

    Trevor

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •