Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 22nd, 2005, 04:05 PM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default Runtime error #2501

Hi All,

I've got a form that generates a list of all availabe reports within my database. When they select one, it opens the report. Pretty simple huh? Well, if there is no data for the report, I've put code to cancel the event, along with a message box to tell them there has been no data entered to produce the report. The problem is, that I get the runtime event cancel message which has the debug button on it. How do I get this to work without getting the error message?

Can anyone help?

Thanks in advance!

Kevin

dartcoach
__________________
dartcoach
 
Old March 22nd, 2005, 04:08 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I'd like to know this too since I have the same problem, and turning off errors as suggested on this site doesn't work. I even tried to trap it, but the problem is that the error runs outside of the OpenReport code, so I haven't figured out how to catch it. Perhaps a module?


mmcdonal
 
Old March 22nd, 2005, 09:02 PM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Well all! Here's what I came up with (after pulling out my hair all afternoon!)

in the on click event I put this code:

   Dim stdocname As String
   Dim obj As Report, dbs As Object
   Set dbs = Application.Reports
   stdocname = Me.List0
   DoCmd.OpenReport stdocname, acViewPreview, , , acWindowNormal
   DoCmd.Maximize
   For Each obj In dbs
      If obj.HasData = 0 Then
         MsgBox "There Are No Records For This Report - Yet.", vbOKOnly, "No Records"
         DoCmd.Close acReport, stdocname
         Exit For
      End If
   Next obj

It works! It opens the report, maximizes it, produces a message box that once they click ok, the report goes away.

Thanks to anyone who was looking into this.

mmcdonal - does this help you?

Kevin

dartcoach
 
Old March 23rd, 2005, 09:38 AM
Friend of Wrox
 
Join Date: Nov 2004
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post
Default

Kevin,

That solution seems a little overboard. Every time someone uses your form to view a report, it looks at every report in your database!!! If you had hundreds of reports...

If I recall correctly, you generally like to let Access build the Command button code for you. That includes the "On Error Goto ..."

The trouble you're having is that Docmd.OpenReport still generates an error even if you have code in the report to say "No Data".

So, change your error trapping in the command button code:

Exit_Sub:
    On Error Resume Next
    Exit Sub

Report_Error:
    If Err.Number = 2501 then Resume Exit_Sub
    msgbox blah, blah
    Resume Exit_Sub
End Sub

Just add the "If Err.Number..." line to your error trap.

You should try single-stepping through your code once in a while. Learn where there errors are happening. Then write code to handle that error.

BTW, you don't need to do the "For each" for every report object. Get rid of the "for each" and just...

Set obj = Application.Reports(stDocName)
If obj.HasData = 0 then blah, blah ....

I'm not sure that "Set obj" syntax is correct. But it is something like that.

Actually, I'm surprised your solution worked... unless of course you removed the "On Error..." trap from the code. You're probably still getting an error. You just don't acknowledge it.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org
 
Old March 23rd, 2005, 11:25 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Randall,
1. The application.reports is only open reports, not all in the database.
2. The set obj code you have needs the index not the name.
3. My problem was that using a list box and when they choose a report, the report has to open before you can check for data.
4. Using my syntax, I check only the open report.
5. I do not have to have any code in each report to check no data.

Seems to work fine!

Thanks for your help.

Kevin

dartcoach
 
Old March 23rd, 2005, 11:35 PM
Friend of Wrox
 
Join Date: Nov 2004
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post
Default

Okay Kevin,

1) I stand corrected about Application.Reports. Sorry. However, your code will still step through [u]every</u> open report.

2) Did you try Application.Reports("report_name")? It works for me.

3) Technically, the report still has to open or you can't use Application.Reports. You said so in your own #1 statement.

4) As I stated in #1, using your syntax you check EVERY report that is open not just "the open report" -- users do not have to close a report after they've opened it. They can switch back to your report selector form and open another report. It's doubtful they'll have hundreds open. So it's a small cost.

5) Because of the fact that you don't have code in each report, I like your idea. It permits changing your mind about how to display the message. OTOH, how many lines of code would you put in a report to give a message box that says "No Data" or to call a routine that displays the message?

If you insist on your code, which I kind of like, I still recommend removing the "For Each" and using Application.Report("report_name"). Just because it says the parameter is "Index" doesn't mean it has to be a number!

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org
 
Old March 24th, 2005, 02:56 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Randall,
I stand corrected! You are right about eliminating the for..each..next! Your code works great! I will say this though, I do not allow my users to open a report without either printing it or closing it. There are no command buttons available to them, only the shortcut menu I have supplied, which only has "close" or "print", or sometimes "analyze it with ms excel". I do this with pretty much everything. That way, a user can't minimize something and have 50 things going on at one time. I force them to follow a logical, common sense path through the system. Which by the way - the users have supplied me with that logical, common sense path.

I really appreciate your help! You don't b*** s*** around when you have something to say and I LIKE THAT! I'd rather have someone tell me to do it right than come up with a work around just to be nice!

Thanks again,

Kevin

dartcoach
 
Old March 24th, 2005, 09:02 AM
Friend of Wrox
 
Join Date: Nov 2004
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post
Default

Kevin,

How about some more no BS.

Have you tried using Ctrl-F6 when one of your reports is opened? Have you set your Access options to not "View" "Windows in Taskbar"?

The point is, you have some control over what the user does by eliminating command buttons and menus. But unless all your users set up Access the way you do and/or they aren't sophisticated enough to know the Ctrl-F6 switches between windows, it is possible for them to leave your reports opened.

The real point is, don't fool yourself into believing that you can force your users to do what you think is best - even if they tell you how you're supposed to force them to do what is best. Damn users!!! Can't live with them, can't earn a living without them. :D

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org





Similar Threads
Thread Thread Starter Forum Replies Last Post
hi i got runtime error 13 Type Mismatch error sriharsha345 Access VBA 2 February 21st, 2008 09:30 AM
runtime error dhoward VB.NET 2002/2003 Basics 2 November 1st, 2007 03:30 PM
DoCmd.OpenForm error 2501 requeth Access 6 February 7th, 2007 04:25 PM
mysterious error runtime error '451' coyotworks Excel VBA 1 May 12th, 2006 03:57 PM
Runtime Error rwiethorn ASP.NET 1.0 and 1.1 Basics 1 January 27th, 2004 02:01 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.