no Report avaiable message box

[habbabub]

hi,
i have a report which is running a query,so when i click on the report it ask for waht to search...

but if there is no records found it willl still come up with a report page with empty feilds. how do i create somthing that will automatically close this, and give a message box saying that no records are avaiable?

 

[boblarson]

Put the message box in the Report's NO DATA event. You will also need to put Cancel=True afterwards in the same event and you might need to handle the error 2501 which can occur if you cancel the report opening and it will usually (but not in Access 2007) generate an error 2501 and you have to handle it in the event where you opened the report.

 

[habbabub]

sorry im not the best at access, but i dont seem to know where i can get to the "Report's NO DATA event"

 

[boblarson]

Open the report, look for the properties dialog and click on the Events tab. Select the tab and then it should be in the list.

 

[habbabub]

hmm. iv foound the properties menu but my events tab has no options

 

[boblarson]

You need to select the form, not a control. To do so, on the properties dialog there is a drop down where you can choose the option FORM. Then you click in the NO DATA event and select the drop down that appears and select [EVENT PROCEDURE] and then click the ellipsis (...) that appears.

See here for more on that:
http://www.btabdevelopment.com/main/QuickTutorials/Wheretoputcodeforevents/tabid/56/Default.aspx

 

[habbabub]

hey i got the mesage box to appear, if no matches are found but what code is needed to close the form?

i currently have in VB

Private Sub Report_NoData(Cancel As Integer)
MsgBox ("asd")
DoCmd.Close
End Sub

 

[boblarson]

hey i got the mesage box to appear, if no matches are found but what code is needed to close the form?

i currently have in VB

Private Sub Report_NoData(Cancel As Integer)
MsgBox ("asd")
DoCmd.Close
End Sub

What form? Are you wanting a form to close when the report doesn't have anything? If so, you would just change

DoCmd.Close

To

DoCmd.Close acForm, "YourFormNameHere", acSaveNo

 

[habbabub]

dang . sory i meant i wanted the report to close.. otherwise after the msg box appears the report will appear but be empty.

so basically i want nothing to appear if no results are found

 

[boblarson]

Change it to this:

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

That's what I mentioned before about Cancel=True.

 

[habbabub]

B E A utiful.. ur a gun. thanks heaps..

 

[boblarson]

Glad we got that going for you

 

[aian]

Similar case here...

Hi Bob, hi habbabub

I have a very similar case here. I have a button in a form (let's call it frm_SEARCH_FORM) that takes parameters from the form, passes them to a query (qry_SEARCH), so that a report (rpt_SEARCH_RESULTS) opens accordingly. Currently the report opens even if there aren't any data to show.

Instead of having a message box though, I would like to open a new form to pass the message. So, when the user will click on the "SEARCH" button of the frm_SEARCH_FORM, in case there are no results, then:

1. frm_SEARCH_FORM will close completely
2. the rpt_SEARCH_RESULTS doesn't open at all
3. a new form opens (let's name it frm_NO_RESULTS) saying that no records satisfy the criteria (and other blablabla included in a simple text label), and include a button that would allow the user to either re-open the search form, or return to the main menu, or even exit the DB completely.

I created a macro that opens the NO_RESULTS form, named it macro_NO_RESULTS and assigned it to the NoData event of my report. All works fine, except that the rpt_SEARCH_RESULTS still opens (and actually it opens in front of frm_NO_RESULTS, but I can fix this), even when there's no data in it to be shown.

So what sould I do for this? Should I change this...

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

...to this?

Private Sub Report_NoData(Cancel As Integer)
frm_NO_RESULTS
Cancel=True
End Sub

Well, I'm sure that's a wrong syntax (my VBA knowledge is sub-zero)

What exaclty should I type and where?

Thanks in advance,

Alexander

 

[boblarson]

This would be the way:

Private Sub Report_NoData(Cancel As Integer)
  DoCmd.Open frm_NO_RESULTS,acNormal,,,,acDialog
  Cancel=True
End Sub

 

[aian]

Thank you Bob.

I insert it in the NoData event of my report but it gives me a Compile Error. It says "Variable not defined", and when I click on help, it explains...

"You use the Option Explicit statement to protect your modules from having undeclared variables and to eliminate the possibility of inadvertently creating new variables when typographical errors occur. This error has the following cause and solution:

You used an Option Explicit statement to require the explicit declaration of variables, but you used a variable without declaring it.
Explicitly declare the variable, or change the spelling of the variable to match that of the intended variable."


I'm sure I haven't named my form incorrectly. What am I doing wrong, Bob?

 

[boblarson]

I goofed - you have to pass the form name as a string:

Private Sub Report_NoData(Cancel As Integer)
  DoCmd.Open [color=red]"[/color]frm_NO_RESULTS[color=red]"[/color],acNormal,,,,acDialog
  Cancel=True
End Sub

 

[aian]

Well, another error now...

See the attachment

 

[boblarson]

Boy, I just am not seeing things right am I.

There is no .OPEN

it should be Docmd.OpenForm

 

[aian]

Thanks Bob, it's working GREAT now!!!

But there's still an error (just as you said in your 1st post), the 2501.

Is there a way to make this vanish? I've looked around and searched but it isn't at all clear to me where I have to put the code (and what code).

Everything I found was for the case of habbabub with a message box. How do I "kill" it in my case?

 

[boblarson]

Thanks Bob, it's working GREAT now!!!

But there's still an error (just as you said in your 1st post), the 2501.

Is there a way to make this vanish? I've looked around and searched but it isn't at all clear to me where I have to put the code (and what code).

Everything I found was for the case of habbabub with a message box. How do I "kill" it in my case?

In the event that tries to open the report (so if it is a button that you click to try to open the report) put in an error handler and use:

If Err.Number <> 2501 Then
   MsgBox Err.Description, vbExclamation, "Error: " & Err.Number
   Resume Next
End If