Access Report Errors

[bhay]

Hello,
We have a SQL database setup using an Access frontend with various menus
setup. One of the menus is a Report menu that allows users to run reports
setup in the Access frontend so the reports are contained in the Report
section of the Access frontend.

There are 4 reports to run, three of the four gives an error of

“Microsoft Visual Basic – Run-time error ‘2501’: The OpenReport action was
cancelled†and the user can choose “End†or “Debugâ€.

The fourth report gives an error of

“Microsoft Office Access – The OpenReport Action was canceledâ€

After the errors nothing happens and the user must restart the Access
frontend. This does not happen with all of the users of this database it
just seems to become a problem for them after awhile.

We have been unable to figure out why this happens. We have went as far as
to completely formatting the user’s hard drive and reinstall every piece of
software and it still comes back within a few days or immediately. What
could cause this?

 

[scott04]

The error is usually generated by the call that opens the report, such as
DoCmd.OpenReport in the Click event of a button. One common occurrence for
this would be if you had code in the NoData event of the report to close the
report if there is nothing for it to do. The error can usually just be
trapped and ignored in your error handler if everything else is working
properly. However, you may want to step through your code first to make
sure.

The most common problem when going from Access 97 to 2000 or newer is that
2000 or newer uses ADO by default instead of DAO. Both of these object
models of components with the same names, such as Recordset. If your DIM
statements just say "Dim rst As Recordset", Access is probably trying to
create an ADO recordset for you and this may be causing problems with the
results being returned. You'll need to make sure that DAO is checked in the
References. (In the code editor go to Tools|References and make sure there
is a check next to "Microsoft DAO 3.6 Object Library". If there is an
earlier version checked, uncheck it and check the newer one.) Next,
explicitly define with object model to use in your DIM statements by
changing "As Recordset" to "As DAO.Recordset". Do this for all DAO objects
(querydefs, fields, database, etc). Edit|Replace (Ctrl+H) usually works well
for this.