Full Version: 0 Records return popup msg box
UtterAccess Discussion Forums > Microsoft® Access > Access Reports
Al3x
Mar 10 2005, 05:21 PM
is there a way to popup a box when there are no records returned from a query when you wish to create a report?

such as "there are no records in this report" so you dont see the preview
R. Hicks
Mar 10 2005, 05:25 PM
Use the On No Data event of the report to do this ...

RDH
Al3x
Mar 10 2005, 05:30 PM
cheers rick for pointing it out, i never knew there were this feature frown.gif
R. Hicks
Mar 10 2005, 05:35 PM
You are Welcome ... wink.gif

RDH
Al3x
Apr 3 2005, 02:37 PM
Sorry to bother you again but how do i code it to display a msg box and NOT display the report? i got it displaying the msg box but the report appears!
R. Hicks
Apr 3 2005, 02:40 PM
Add a line in the On No Data event ...
CODE
Cancel = True

RDH
Al3x
Apr 3 2005, 02:46 PM
that worked but i get an access error sayign the access report open was cancelled, is there any way of displaying a nicer error message after my msg box?
R. Hicks
Apr 3 2005, 02:47 PM
Post the code that you are using to open the report ...

RDH
Al3x
Apr 3 2005, 02:50 PM
just the basic ms access wizard:

Private Sub cmdCompReport_Click()
On Error GoTo Err_cmdCompReport_Click

Dim stDocName As String

stDocName = "rptCompReport"
DoCmd.OpenReport stDocName, acPreview

Exit_cmdCompReport_:
Exit Sub

Err_cmdCompReport_Click:
MsgBox Err.Description
Resume Exit_cmdCompReport_

End Sub

Then in my report i have:

Private Sub Report_NoData(Cancel As Integer)
MsgBox "No Data In Report."
Cancel = True
End Sub

Also i have an issue because i have the form on popup the preview report appears behind?
R. Hicks
Apr 3 2005, 02:55 PM
Try altering to this:
CODE
Private Sub cmdCompReport_Click()
On Error GoTo Err_cmdCompReport_Click

Dim stDocName As String

stDocName = "rptCompReport"
DoCmd.OpenReport stDocName, acPreview

Exit_cmdCompReport_Click:
  Exit Sub

Err_cmdCompReport_Click:
  If err.Number <> 2501 Then
    MsgBox Err.Description
  End if

  Resume Exit_cmdCompReport_Click

End Sub

RDH
Al3x
Apr 3 2005, 03:21 PM
that did the trick but the report is still at the back of the popup form box? is there anyway of bringing it forward?
R. Hicks
Apr 3 2005, 03:27 PM
Either close the form ... or set the form to be hidden when the report opens ...

RDH
Al3x
Apr 10 2005, 12:04 PM
the fix works fine ricky but what exactly does it do? i dont understand the code
R. Hicks
Apr 10 2005, 12:13 PM
I hope I understand what you are asking ....

If the form is set as popup and/or modal ...
Then the get the another object to appear in front of it .. you ether have to close the form or set it's visible property to be hidden ...

If this is not what you are talking about .. explain what code ??

RDH
Al3x
Apr 10 2005, 12:15 PM
sorry i said the wrong thing i meant this code:

CODE
    If err.Number <> 2501 Then    
MsgBox Err.Description  
End if
R. Hicks
Apr 10 2005, 12:28 PM
Oh ...

If the user or Access Cancel an operation ... Access will generate the error number 2501.

So the code basically says:
If the error number "is not" 2501 .. display a messagebox to the user to expolaining what may have happened.
Then exit the routine ..

But if the error "is" 2501 .. just exit the routine without any message to the user ...

I hoped I have explained it well enough for you ...

RDH
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.