Paramater Report based on Combo Box and dealing with No Data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sareny - 30 Mar 2007 14:54 GMT

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!

Reply to this Message

Sareny - 30 Mar 2007 14:58 GMT

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.

Reply to this Message

Sareny - 30 Mar 2007 15:07 GMT

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.

Reply to this Message

Douglas J. Steele - 30 Mar 2007 15:38 GMT

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> sorry about this, but I've been testing things.
>
[quoted text clipped - 17 lines]
>
> Thank you.

Reply to this Message

Sareny - 30 Mar 2007 15:47 GMT

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

Reply to this Message

Douglas J. Steele - 30 Mar 2007 16:31 GMT

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> The second msgbox still appears. I have this in my code:
> Private Sub btnCompletedEmail_Click()
[quoted text clipped - 18 lines]
>
> End Sub

Reply to this Message

Sareny - 30 Mar 2007 16:55 GMT

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

Reply to this Message