Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Dec 2009
    Bogangar NSW Australia
    Thanked 2 Times in 2 Posts

    Access 2010 VBA error message when closing report

    I call a Report from a Form (that provides filters for the underlying Query) which then displays an Excel Chart via a linked OLEUnbound object. The Excel Chart is updated by data from an Access Query as the Report is being "Open"ed and thus the chart changes to reflect changes in the Query data.

    Sometimes, but not always, when I close the report using the Close"X" at the top right corner of the Report I get the following warning message:

    "This object is locked. Any changed you make will be discard when the form is closed.
    Click the File tab, point to Save As, and save the object under a different name."

    I have no need to save the object, presumably the Report. If it is not the Report how can I identify the offending object?

    How can I prevent this warning from appearing, regardless of the object that is triggering it?

    Thanks in anticipation of a solution.



  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Evergreen, CO, USA
    Thanked 58 Times in 58 Posts
    Is the query that serves as the data source for the Excel Chart a saved query? If so, I suspect that query may be the thing that is giving you the warning message, as filters can be saved as a part of the query. If that's the case, you might want to consider constructing the data source for the Excel chart in code and actually saving the query before opening the report. Are you comfortable in modifying saved queries in VBA?

  3. #3
    2 Star Lounger
    Join Date
    Dec 2009
    Bogangar NSW Australia
    Thanked 2 Times in 2 Posts
    Hi Wendell,

    Thanks for your response. However I am unclear as to what you are suggesting. Below is more detail on the process being pursued in creating the Report for viewing only.

    The Query that forms to basis of the Report is a saved Query that when run takes filter parameters from the Report Selector Form that calls it.

    The Report Selector Form that provides the filter parameters:

    20150215 Trade Report Selector.JPG

    The saved Query SQL is:

    SELECT Left([Instrument_Description],InStr([instrument_Description]," ")) AS Markets, Sum(IIf((IIf(IsNull([Profit_A]),0,[Profit_A])+IIf(IsNull([Profit_B]),0,[Profit_B])+IIf(IsNull([Profit_C]),0,[Profit_C])+IIf(IsNull([Profit_D]),0,[Profit_D]))>=0,1,0)) AS NumProfits, Sum(IIf((IIf(IsNull([Profit_A]),0,[Profit_A])+IIf(IsNull([Profit_B]),0,[Profit_B])+IIf(IsNull([Profit_C]),0,[Profit_C])+IIf(IsNull([Profit_D]),0,[Profit_D]))<0,1,0)) AS NumLosses, [NumLosses]*-1 AS NegSumLosses, [Tbl Evaluations and Trades].Broker_Name
    FROM [Tbl Evaluations and Trades]
    WHERE ((([Tbl Evaluations and Trades].Instrument_Description) Like "*" & [Forms]![Fm Trade Report Selector].[Tbx_Instrument_Description] & "*") 
    AND (([Tbl Evaluations and Trades].Instrument) Like [Forms]![Fm Trade Report Selector].[Tbx_Instrument_Code] & "*") 
    AND (([Tbl Evaluations and Trades].Instrument_Type) Like [Forms]![Fm Trade Report Selector].[Tbx_Instrument_Type] & "*") 
    AND ((IIf([C1_Date]>[C_Date],[C1_Date],[C_Date])) Between [Forms]![Fm Trade Report Selector].[Tbx_Period_Start] And [Forms]![Fm Trade Report Selector].[Tbx_Period_End]) 
    AND (([Tbl Evaluations and Trades].Trade_Type) Like [Forms]![Fm Trade Report Selector].[Tbx_Trade_Type] & "*") 
    AND (([Tbl Evaluations and Trades].Trade_Entry_Price_A)>0))
    GROUP BY Left([Instrument_Description],InStr([instrument_Description]," ")), [Tbl Evaluations and Trades].Broker_Name
    HAVING ((([Tbl Evaluations and Trades].Broker_Name) Like [Forms]![Fm Trade Report Selector].[Tbx_Broker_Name] & "*"))
    ORDER BY Left([Instrument_Description],InStr([instrument_Description]," "));
    Using the Report Open Event, the data resulting from this Query is then transferred using Access VBA to an existing Excel Workbook in which Access VBA first deletes any existing data and charts then inserts the data, creates the required Chart and saves the Excel Workbook and Quits Excel.

    The code for this process is:

    Private Sub Report_Open(Cancel As Integer)
        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
        Dim fld As DAO.Field
        Dim qdf As DAO.QueryDef     '   Used in conjuction with UDF ResolveQueryParams("Query")
        Dim strTQName As String     '   Query from which data is to be transfered to Excel
        Dim varReturn As Variant    '   Use for display process status on the StatusBar
        Dim xlApp As Excel.Application
        Dim xlWkBk As Excel.Workbook
        Dim xlWkBkName As String
        Dim xlWkSht As Excel.Worksheet
        Dim xlChtObj As ChartObject
        Dim xlChtYData1 As Range
        Dim xlChtYData2 As Range
        Dim xlChtXVal As Range
        Dim xlChtXaxisCategoryFormat As String
        Dim xlChtType As String
        Dim xlChtYSeriesName1 As String
        Dim xlChtYSeriesColour1 As String
        Dim xlChtYSeriesName2 As String
        Dim xlChtYSeriesColour2 As String
        Dim xlChtTitle As String
        Dim xlChtName As String
        Dim Msg, Button, Title, Response As String
        Const xlCenter As Long = -4108
        Const xlBottom As Long = -4107
        On Error GoTo err_handler
        Button = vbExclamation
        Title = "Private Sub Report_Load()..."
    '   Identify the Query from which data is to be extracted
        strTQName = "Qry Markets, Num Profits&Losses Summary"
    '   Update the status bar
        varReturn = SysCmd(acSysCmdSetStatus, "Initializing Excel Workbook...")
    '   Open existing Excel Wookbook
        xlWkBkName = "C:\Users\Trevor R Bird\Documents\Trading the Market\Trading Database\Trading Database Charts - Markets, Number of Profits & Losses.xlsx"
        Set xlApp = New Excel.Application
        Set xlWkBk = xlApp.Workbooks.Open(xlWkBkName)
        Set xlWkSht = xlWkBk.Sheets("Sheet1")
        xlApp.Visible = False
    '   Set Named Ranges in Worksheet to be charted
        With xlWkBk
            .Names.Add Name:="Dates", RefersTo:="=OFFSET(sheet1!$A$2,0,0,COUNTA(sheet1!$A:$A),1)"   '   X-Axis
            .Names.Add Name:="Data1", RefersTo:="=OFFSET(sheet1!$B$2,0,0,COUNTA(sheet1!$B:$B),1)"   '   Y-Axis 1st series
            .Names.Add Name:="Data2", RefersTo:="=OFFSET(sheet1!$D$2,0,0,COUNTA(sheet1!$D:$D),1)"   '   Y-Axis 2nd series
        End With
    '   Delete data in the worksheet
    '        Msg = "Ok I've deleted existing data in the sheet"
    '        Response = MsgBox(Msg, Button, Title)
    '   Update the status bar
        varReturn = SysCmd(acSysCmdSetStatus, "Transfering Access Data to Excel Workbook...")
    '   Resolve parameters in Query using Public Function ResolveQueryParams()
        Set qdf = ResolveQueryParams(strTQName)
    '   Open recordset
        Set rst = qdf.OpenRecordset
        '   Insert headings in row 1 of Excel Worksheet
        For Each fld In rst.Fields
            xlApp.ActiveCell = fld.Name
            xlApp.ActiveCell.Offset(0, 1).Select
        '  Insert Query data below the headings
        xlWkSht.Range("A2").CopyFromRecordset rst
        '   Close the recordset
        Set rst = Nothing
    '   Update the status bar
        varReturn = SysCmd(acSysCmdSetStatus, "Access Data transferred to Excel Workbook...")
    '   Set Chart Type and data sources
        xlChtType = xlColumnStacked
        Set xlChtData1 = xlWkSht.Range("Data1")
        Set xlChtData2 = xlWkSht.Range("Data2")
        Set xlChtXVal = xlWkSht.Range("Dates")
    '    xlChtXaxisCategoryFormat = "dd Mmm yyyy"
        xlChtXaxisCategoryFormat = "#####"
        xlChtYSeriesName1 = "Data1"
        xlChtYSeriesColour1 = RGB(255, 0, 0)
        xlChtYSeriesName2 = "Data2"
        xlChtYSeriesColour2 = RGB(0, 255, 0)
        xlChtTitle = "Market Trading Performance"
        xlChtName = "MyChart"             '   Chart name for easy reference when called by Office application
    '   Remove any existing charts form the workbook
        Do Until xlWkSht.ChartObjects.Count = 0
    '   Add a chartto the Excel Workbook
        Set xlChtObj = xlWkSht.ChartObjects.Add(Left:=150, Width:=700, Top:=100, Height:=400)
        With xlChtObj.Chart
    '   Set chart name for easy reference
            .Parent.Name = xlChtName
            .ChartType = xlChtType
    '   Delete any default series created when creating the chart
            Do Until .SeriesCollection.Count = 0
    '   Delete default Legend
    '   Create required Data1 series
            With .SeriesCollection.NewSeries
                .Name = xlChtYSeriesName1
                .Values = xlChtData1
                .XValues = xlChtXVal
    '   Select chart Title and set location
            With .ChartTitle
    '            .HasTitle = True
                .Text = xlChtTitle
                .Left = 50
                .Top = 10
            End With
    '   Set the Plot area dimensions
            With .PlotArea
                .Top = 5
                .Height = 380
                .Width = 680
            End With
    '   set the X-axis format
            With .Axes(xlCategory)
    '   Set X-axis to number format
                .TickLabels.NumberFormat = xlChtXaxisCategoryFormat
    '   Set X-axis text orientation
                .TickLabels.Orientation = xlUpward
            End With
    '   Set x-axis text characteristics
            With .Axes(xlValue).TickLabels.Font
                .Name = "Arial"
                .Bold = True
                .Size = 14
            End With
    '   Create required Data2 series
            With .SeriesCollection.NewSeries
                .Name = xlChtYSeriesName2
                .Values = xlChtData2
                .XValues = xlChtXVal
            End With
        End With
    '   Update the status bar
        varReturn = SysCmd(acSysCmdSetStatus, "Chart created in Excel...")
    '   Update the status bar
        varReturn = SysCmd(acSysCmdSetStatus, "Saving Excel Workbook and Quitting Excel...")
    '   Close the Excel Workbook and Quit Excel
        Set xlApp = Nothing
    '   Update the status bar
        varReturn = SysCmd(acSysCmdSetStatus, "All done, pausing before activating Report display...")
    '   Wait for Excel to save and Quit
        WaitFor (2)
    '   Clear StatusBar
        varReturn = SysCmd(acSysCmdSetStatus, " ")
    '   Locate Report on screen
    '   .MoveSize(Right, Down, Width, Height)
    '   Dimensions in Twips (=1/1440th inch = 1/567 cm)
        DoCmd.MoveSize 1100, 1100, 14750, 500
    Exit Sub
        DoCmd.SetWarnings True
        MsgBox Err.Description, vbExclamation, Err.Number
        Exit Sub
    End Sub
    This all proceeds perfectly and displays the required chart:

    20150215 Report Chart.JPG

    It is only when I click the Close 20150215 Close Report Button.JPG Report Button that the following nuisance dialogue appears:

    20150215 Object is Locked dialogue.JPG

    I can see no reason for saving the "offending" object, be it the Report Selection Form, the Report or the Query, as I have already successfully created the Report to view on screen, viewed it, and now only wish to remove it from my screen (without saving or printing it).

    Hopefully with this additional detail you may be able to suggest a solution for suppressing the offending dialogue.

    Thanks in anticipation.


  4. #4
    New Lounger
    Join Date
    Dec 2009
    Milwaukee, WI USA
    Thanked 0 Times in 0 Posts
    I believe Access thinks you have changed the report by deleting the old content and adding new content. You probably get the message about changes because others have the database open and therefore you don't have exclusive control of it and can't update things. A possible way around this is to provide a button somewhere that does a close without a save.

  5. #5
    2 Star Lounger
    Join Date
    Dec 2009
    Bogangar NSW Australia
    Thanked 2 Times in 2 Posts
    Hi Royce,

    Thanks for your suggestion, unfortunately I don't see how it can apply as the database is not on a network and is not shared. It is single user on a single laptop.



  6. #6
    2 Star Lounger
    Join Date
    Dec 2009
    Bogangar NSW Australia
    Thanked 2 Times in 2 Posts

    I found a solution to my issue.

    Well I thought I had by using the following:

    I've inserted the following Macro in the Report "On Close" event:

    20150227 Report Close Macro.JPG

    Only now the issue has resurfaced and I can't work out what I've changed to make it resurface


    Last edited by BygAuldByrd; 2015-02-26 at 19:30.

Posting Permissions

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