Paramater Report based on Combo Box and dealing with No Data

  • Thread starter Thread starter Sareny
  • Start date Start date
S

Sareny

I have a paramater report that is based on a combo box (on unbound
form that is called from Report's on open event, form value is used in
Report's Record Source Query).
This works great and I have no complaints about that.
But what I want to do is the following:
Have a MsgBox appear if no records are returned from the Record Source
query and to close the report.
I tried doing this in the No Data Event, but since I use a paramater
form it doesn't work.

Can someone help me determine the best place to run my code?
Dim Msg, Style, Title, Response
Msg = "There are no Active Projects for " & [Forms]![frmParamForm]!
[cmbFindProgrammer] & "." ' Define message.
Style = vbInformation ' Define buttons.
Title = "No Active Projects for Programmer" ' Define title.
Response = MsgBox(Msg, Style, Title)
DoCmd.Close

I'd really appreciate a prompt response as this is an urgent project
request from my boss.

Thank you!
 
Okay, now my msgbox is appearing when I use the No Data Event.

But I open the un-bound form from a another form and no message box is
appearing. It will appear when I open the unbound form directly. How
can I get the msgbox to appear over my original form?

Is there some property that I need to set?

Thank you.
 
sorry about this, but I've been testing things.

I now got it to completely work, except that after my msgbox opens and
the user clicks OK a second msgbox appears that says "The OpenReport
Action was Cancelled".

code changed to:
Private Sub Report_NoData(Cancel As Integer)
Dim Msg, Style, Title, Response
Msg = "There are no Active Projects for " & [Forms]![frmParamForm]!
[cmbFindProgrammer] & "." ' Define message.
Style = vbInformation ' Define buttons.
Title = "No Active Projects for Programmer" ' Define title.
Response = MsgBox(Msg, Style, Title)
Cancel = True
End Sub

How can I get this second message box to not appear, but still have
the OpenReport Action be cancelled.

Thank you.
 
You need to trap for the error that's raised in the code that opens the
report.

Sub ....
On Error GoTo ErrHand

DoCmd.OpenReport "MyReport", ....

GetOut:
Exit Sub

ErrHand:
Select Case Err.Number
Case 2501' "The OpenReport action was canceled."
Resume Next
Case Else
MsgBox Err.Number & ": " & Err.Description
End Select
Resume GetOut

End Sub
 
The second msgbox still appears. I have this in my code:
Private Sub btnCompletedEmail_Click()
On Error GoTo Err_btnCompletedEmail_Click

Dim stDocName As String

stDocName = "SendEmailforCompProject"
DoCmd.RunMacro stDocName

Exit_btnCompletedEmail_Click:
Exit Sub

Err_btnCompletedEmail_Click:
Select Case Err.Number
Case 2501 ' "The OpenReport action was canceled."
Resume Next
Case Else
MsgBox Err.Number & ": " & Err.Description
End Select
Resume Exit_btnCompletedEmail_Click

End Sub
 
You would appear to have put the code in the wrong routine.

Your code is using DoCmd.RunMacro. The error arises in conjunction with a
DoCmd.OpenReport.

If your macro is what opens the report, I'd recommend converting the macros
to VBA.
 
How dumb of me. Sorry about that.

I accidentally copied my error message handling into the wrong button
click event.
I moved it to the correct button click event and it works great!

Thank you for all your help and for putting up with my Friday morning
craziness. :)

Thanks
 
Back
Top