how can i close a report when there is no table. In a report i use
some VBA code. When i start the report i want to check if the data
table exists. When the table doesn't exits i want the report to be
closed.

i have use the following vba

---
Private Sub Report_Error(DataErr As Integer, Response As Integer)

    MsgBox "there is no data first execute the data run"
    DoCmd.Close

End Sub
---

when the vba is executed the message box will appear but the report
won't be closed. after klik on Ok. what can i do to close the report?

i have used

---
docmd.close acreport, rpt_reportname
---
 but that doesn't work

please your suggestions

Erik Meulen wrote:

> how can i close a report when there is no table. In a report i use
> some VBA code. When i start the report i want to check if the data
> table exists. When the table doesn't exits i want the report to be
> closed.
> 
> i have use the following vba
> 
> ---
> Private Sub Report_Error(DataErr As Integer, Response As Integer)
> 
>     MsgBox "there is no data first execute the data run"
>     DoCmd.Close
> 
> End Sub
> ---
> 
> when the vba is executed the message box will appear but the report
> won't be closed. after klik on Ok. what can i do to close the report?
> 
> i have used
> 
> ---
> docmd.close acreport, rpt_reportname
> ---
>  but that doesn't work
> 
> please your suggestions

Private Sub Report_NoData(Cancel As Integer)
     MsgBox "Nothing here"
     Cancel = True
End Sub

Salad wrote:


> Private Sub Report_NoData(Cancel As Integer)
>      MsgBox "Nothing here"
>      Cancel = True
> End Sub

This works, but you'll get a runtime-error because the OpenRecort action 
is stopped.

You'll have to catch this runtime-error to get around this.

Volker


-- 
Wenn es vom Himmel Zitronen regnet -- lerne, Limonade zu machen.

Volker Neurath <neanderix@expires-28-02-2010.news-group.org> wrote
in news:1267097944.16@neanderix.newsoffice.de: 

> Salad wrote:
> 
>> Private Sub Report_NoData(Cancel As Integer)
>>      MsgBox "Nothing here"
>>      Cancel = True
>> End Sub
> 
> This works, but you'll get a runtime-error because the OpenRecort
> action is stopped.
> 
> You'll have to catch this runtime-error to get around this.

It's quite easy to write a wrapper function with error handling for
No Data that you can use instead of the direct DoCmd.OpenReport
command. 

-- 
David W. Fenton                  http://www.dfenton.com/ 
usenet at dfenton dot com    http://www.dfenton.com/DFA/

On Feb 25, 4:21 am, Erik Meulen <erikvanmeu...@gmail.com> wrote:
> how can i close a report when there is no table. In a report i use
> some VBA code. When i start the report i want to check if the data
> table exists. When the table doesn't exits i want the report to be
> closed.
>
> i have use the following vba
>
> ---
> Private Sub Report_Error(DataErr As Integer, Response As Integer)
>
>     MsgBox "there is no data first execute the data run"
>     DoCmd.Close
>
> End Sub
> ---
>
> when the vba is executed the message box will appear but the report
> won't be closed. after klik on Ok. what can i do to close the report?
>
> i have used
>
> ---
> docmd.close acreport, rpt_reportname
> ---
>  but that doesn't work
>
> please your suggestions

I like to see if there's any data before opening the report.  If you
use a form to open the report, try:

'-----Begin Module Code-----
Public Function DHasRecords(strSQL As String) As Boolean
Dim MyDB As Database
Dim CountRS As Recordset

DHasRecords = False
Set MyDB = CurrentDb
Set CountRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
If CountRS.RecordCount > 0 Then
  DHasRecords = True
End If
CountRS.Close
Set CountRS = Nothing
Set MyDB = Nothing
End Function

Public Function DNoRecords(strSQL As String) As Boolean
Dim MyDB As Database
Dim CountRS As Recordset

DNoRecords = True
Set MyDB = CurrentDb
Set CountRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
If CountRS.RecordCount > 0 Then
  DNoRecords = False
End If
CountRS.Close
Set CountRS = Nothing
Set MyDB = Nothing
End Function
'-----End Module Code-----

Sample:

Suppose the RecordSource for the report is:

SELECT Field1 As A, Field2 As B, Field3 As C From Table1 WHERE Field1
< 20;

'-----Code behind Form-----
strSQL = "SELECT * FROM Table1 WHERE Field1 < 20;"
If DHasRecords(strSQL) Then
  DoCmd.OpenReport "rptTest", acViewPreview
Else
  MsgBox("No records satisfy the criteria/criterion.")
End
'-----End Code behind Form-----

If you don't use a form to open the report, the test can be made in
the report's Open event.  A Cancel there should work as expected.
Something like:

Private Sub Report_Open(Cancel As Integer)
Dim strSQL As String

strSQL = "SELECT * FROM Table1 WHERE Field1 < 20;"
If DNoRecords(strSQL) Then
  MsgBox ("No records satisfy the criteria/criterion.")
  Cancel = True
  Exit Sub
End If
'...
End Sub
End If

James A. Fortune
CDMAPoster@FortuneJames.com

Hi David,

David W. Fenton wrote:

> Volker Neurath <neanderix@expires-28-02-2010.news-group.org> wrote
> in news:1267097944.16@neanderix.newsoffice.de: 

>> Salad wrote:
>> 
>>> Private Sub Report_NoData(Cancel As Integer)
>>>      MsgBox "Nothing here"
>>>      Cancel = True
>>> End Sub
>> 
>> This works, but you'll get a runtime-error because the OpenRecort
>> action is stopped.
>> 
>> You'll have to catch this runtime-error to get around this.

> It's quite easy to write a wrapper function with error handling for
> No Data that you can use instead of the direct DoCmd.OpenReport
> command. 

My post was just meant to be an information - to prevent the 
threadstarter from wondering why getting a runtime-error.

Of course it is quite easy to get around it (I assume, the report is 
opened via button-click):

private sub cmdMyReport_click()

On Error GoTo ErrLeer

DoCmd.OpenReport "Berichtsname"

ErrLeer:
If Err.Number = 2501 Then Exit Sub

end sub

Private Sub Report_NoData(Cancel As Integer)
      MsgBox "Nothing here"
      Cancel = True
 End Sub








-- 
Wenn es vom Himmel Zitronen regnet -- lerne, Limonade zu machen.