Access 2010 VBA error message when closing report

Topic

New Reply

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:

[INDENT=2]”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.”[/INDENT]

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.

Cheers

Trevor

Reply | Quote

Replies

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?

Reply | Quote

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:

39432-20150215-Trade-Report-Selector

The saved Query SQL is:

Code:

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]))[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:

Code:

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:UsersTrevor R BirdDocumentsTrading the MarketTrading DatabaseTrading 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

    xlWkSht.Activate
    
‘   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
    xlWkSht.Range(“A:H”).ClearContents
    xlWkSht.Range(“A1”).Select
    
‘        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
    Next
    rst.MoveFirst
    
    ‘  Insert Query data below the headings
    xlWkSht.Range(“A2”).CopyFromRecordset rst
    xlWkSht.Range(“1:1”).Select
    
    ‘   Close the recordset
    rst.Close
    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
        xlWkSht.ChartObjects(1).Delete
    Loop
    
‘   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
            .SeriesCollection(1).Delete
        Loop
‘   Delete default Legend
        .Legend.Delete
‘   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
    xlWkBk.Save
    xlWkBk.Close
    xlApp.Quit
    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

err_handler:
    DoCmd.SetWarnings True
    MsgBox Err.Description, vbExclamation, Err.Number
    Exit Sub

End Sub

This all proceeds perfectly and displays the required chart:

39436-20150215-Report-Chart

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

39435-20150215-Object-is-Locked-dialogue

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.

Trevor

Reply | Quote

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.

Reply | Quote

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.

Cheers

Trevor

Reply | Quote

Yippee!!

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:

39601-20150227-Report-Close-Macro

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

Cheers

Trevor

Reply | Quote