switching report to design mode kills Access RRS feed

  • Question

  • This is a new one on me.

    I've been working on a database and out of nowhere the following symptoms appeared today....

    I open a report in preview mode.  I right-click on the tab to switch to layout mode.  I right-click to design mode.  I right click to switch back to preview mode.  Everything is fine.  But if I try to switch from preview mode directly to design mode, Access just stops functioning and exits.

    When I reopen the .mdb file and try to run the repair utility, I get a message that Access can't delete the file after compacting it.  It says "If you can, remove the read-only status, delete the original database and then rename the new database using the original name."

    I tried creating a new database file in a different directory and importing all the database objects from my original file, and I get the same problem.

    Then I went to a different Access database I maintain which is located on a different physical drive on my laptop (I have two drives) and I get the same problem when I try to compact/repair that one.

    I suppose the next step for me is to try to repair Access or even reinstall it.  But before I take that step, I thought I'd check if anybody else has ever experienced this.

    Many thanks,


    Wednesday, August 30, 2017 7:35 PM


  • Will do.  

    Setting the report Record Source to different queries that sort the records differently worked.  

    • Marked as answer by Rich 30005 Thursday, August 31, 2017 8:20 PM
    Thursday, August 31, 2017 8:20 PM

All replies

  • If you're experiencing the same problem on multiple files, then the cause points to your install of Access. You didn't mention what version you're using, but I noticed you said you're using MDB files. Can you try opening the files using a different machine?
    Wednesday, August 30, 2017 7:52 PM
  • This gets stranger by the minute.

    OK, I tried to do what I described above on two different machines.  File was created with Access 2013.  One of the 2 machines I tried it on runs 2013, the other one runs 2016.  It killed both versions, pointing again to something about that file.  I do a daily backup of files so I restored to a version of the file from about a week ago and the problem stopped, so something about what I did is the problem.  I never thought of my code as toxic, but I could be wrong :-)

    I'll try adding stuff back in item by item (I did a lot in the last week, but I can restore to each daily version).  If I find anything definitive, I'll report back.  

    Thanks so much for the prompt reply.

    Wednesday, August 30, 2017 8:42 PM
  • Hi Rich,

    I thought you were having problems with two different database applications. If it's the same file, then yes, it's probably something in the latest changes. Good luck hunting...

    Wednesday, August 30, 2017 8:59 PM
  • Well, I got to the bottom of my problem, and in the process I think I may have found a minor bug in Access (I use 2013, but in one brief test I suspect this exists in 2016 as well).

    The difference between the file of mine that crashed from the one that worked is this....  I added a little DoCmd code to the On Open event of the report in question.  My intent was to use the SetOrderBy macro action to conditionally set the sort order of the records on the report.  It doesn't matter if I create a macro, or put it in code.  In either case, you invoke that macro, or run the code, in the ON OPEN event of the report that gets opened in preview mode.  Once open, if you right-click to switch from preview mode to design mode, Access crashes (that's the bug, or at least I think it is).  In the same scenario, if you switch from report preview mode to layout mode, and then switch to design mode, it works fine. And if you change from design mode to preview mode, the report runs fine and sorts the records in the intended order.

    I did prove this out by creating a test database in which I created one table with a text field and a number field.  I created a few records so I could observe the sort order in preview mode.  I then used the report wizard to create a report on that table, being careful to not create groupings or sort orders.  I created a single macro with the SetOrderBy action to one field, and then I call that macro in the report's On Open event.  The reports sorts correctly, but when I toggle directly from report preview mode to design mode, Access crashes.

    So I guess I'll have to try to conditionally sort my report by setting the report's source to different queries that sort the records differently.  Hope that works -- if so, I'll be happy to mark this problem complete.

    Thursday, August 31, 2017 7:30 PM
  • Hi,

    If it's a bug, it might be worth it to post the issue on Access Uservoice website.

    Just my 2 cents...

    Thursday, August 31, 2017 8:06 PM
  • Will do.  

    Setting the report Record Source to different queries that sort the records differently worked.  

    • Marked as answer by Rich 30005 Thursday, August 31, 2017 8:20 PM
    Thursday, August 31, 2017 8:20 PM
  • Congratulations! Good luck with your project.
    Thursday, August 31, 2017 8:30 PM