check if Reports are open?? 2

[tEkHEd]

Hi there,

I have a small problem that hopefully someone can help me out with...

Basically I am running several dynamic reports, and I need to be able to check if the report is currently open, if it is, close it, then re-open the selected report type with the new datasource..

I have tried to do something like:

Dim rs As DAO.Recordset

Set rs = dbs.OpenRecordset("tblTable")


If IsLoaded(rs!ReportName) Then
    DoCmd.Close rs!ReportName
    DoCmd.OpenReport rs!ReportName, acViewPreview
    Else
    DoCmd.OpenReport rs!ReportName, acViewPreview
End If

Unfortunately this doesn't work

I am not 100% that the IsLoaded syntax is a: correct or b: valid for a report??

I also have a module loaded with the following code (which is taken from a sample DB that is designed to check if a Form is loaded or not..)

Function IsLoaded(ByVal stRptName As String) As Boolean
'New version from Northwind for Access 2002
'Returns True if the specified form is open in Form view or Datasheet view.

    Dim oAccessObject As AccessObject

    Set oAccessObject = CurrentProject.AllReports(stRptName)
    If oAccessObject.IsLoaded Then
        If oAccessObject.CurrentView <> acCurViewDesign Then
            IsLoaded = True
        End If
    End If
    
End Function

Can anyone give me any pointers on this please??

Thanks in advance.

 

[tonyireland]

I check for err.number 3211 (report is already open ) in the error handling routine
e.g.

[\code]

on error goto error_handler
DoCmd.OpenReport rs!ReportName, acViewPreview
error_handler:
If Err.Number = 3211 Then
DoCmd.Close rs!ReportName
DoCmd.OpenReport rs!ReportName, acViewPreview
resume next
Else
MsgBox Err.Description, vbCritical, STRSYSTEM
exit sub
end if

 

[danvlas]

tonyireland: this is the error 3211:
The database engine couldn't lock table '|' because it's already in use by another person or process.

Trying to open a report that's already open throws no error...

The function below will return True if report is open and false if not:

Function TestReport(ReportName) As Boolean
On Error GoTo ErrHandler
Dim rpt As Report
Set rpt = Reports(ReportName)
TestReport = True
Set rpt = Nothing
Exit Function
ErrHandler:
TestReport = False
Exit Function
End Function

You can use it to close a report:

If TestReport("YourReport&quot Then
docmd.Close acReport, "YourReport"
End If

Good luck



Good luck
Daniel Vlas
Systems Consultant

 

[tonyireland]

Well spotted there Danvlas
my routine only works because the underlying table of the report is locked

I think I might use your solution in future

thanks

 

[tEkHEd]

mmmm this is almost there...

it seems as though this function does work if the report is open, if the report is not open it doesn't..
I have the following code in my module:

Public Function ChkRpt(ReportName) As Boolean
On Error GoTo ErrHandler

Dim rpt As Report

Set rpt = Reports(ReportName)

ChkRpt = True

Set rpt = Nothing

Exit Function

ErrHandler:
ChkRpt = False
Exit Function

End Function

and this is the "on click" code for my button:

    If ChkRpt(rs!RptName) Then
        DoCmd.Close acReport, rs!RptName
        DoCmd.OpenReport rs!RptName, acViewPreview
    Else
        DoCmd.OpenReport rs!RptName, acViewPreview
    End If

rs!RptName is a valid recordset, and finds the correct report.

If I click the button and the form is not open, nothing happens. If I manuall open the required report, then press my button, the function kicks in and closes, re-opens the report..

maybe I am having a dumb day or something, but I cannot see why this is..

 

[PaulBricker]

You can use this to test if the repor is open.

If SysCmd(acSysCmdGetObjectState, acReport, "ReportName&quot Then
Docmd.Close acReport, "ReportName"
Else
End If

You may be able to set your Report names to a variable and loop thru this routine and test for each one or you can just hardcode the routine for each report you want to check.

Paul

 

[tEkHEd]

Now that is working... many thanks for ur help

 

[randysmid]

Paul,
Excellent post - it deserves a star !!!
HTH,
Randy Smith
California Teachers Association

 

[PaulBricker]

Thanks Randy. Appreciate it.

Paul

 

[danvlas]

Paul's suggestion is perfect, it's another (maybe better) way to skin the same cat.

But you don't need any function...All you need is closing the report (if it is not open, nothing will happn, no error will occur), then open it...

You have it in your code:

DoCmd.Close acReport, rs!RptName
DoCmd.OpenReport rs!RptName, acViewPreview

Delete everything else and see what happens... As for not working, I don't know what to say...It works fine here...


Good luck

Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html