50% of the time my report won't open

[Cero21]

Hi, sorry I'm not very well versed in Access but here goes.

I am running Access 2000 sp-3 and I made a whole bunch of reports using the wizard. 4 are made from single tables and 4 are made with queries that narrow down those single tables.

Now, when I go into access and try to click and open these reports from the database window(either preview or design) around 50% of the time nothing will happen. The small hourglass will appear for less than a second then nothing. I do not recieve an error msg or anything.

I also have a couple forms that will open these reports on a click and I get the same thing.

Anyone have any idea what is going on here? I would greatly appreciate any help. Thanks.

 

[brendadd]

Try opening in design mode to make sure you actually have fields in these reports, or check the query to make sure it didn't get accidentally erased. If all is ok, make sure the filters in your query actually show some data.

 

[Cero21]

Thanks for the quick reply brendadd. I checked the things you suggested and everything seems to be in order. The thing is I can't even open the report in design mode either. When preview button doesn't work, neither will the design button.

The really strange part about my problem is that I when the report does open and display everything I wanted it to. Sometimes when I close it and try to open it immediately after (without saving or changing anything) nothing happens like I described above.

 

[GingerR]

Might be some corruption. Has this always happened, or did everything function properly at some point in the past? I suggest you try Compact and Repair, or even create a new blank db and import in everything "good", then import in one "bad" report and see if the error still occurs. If all ok, import in another "bad" report, and so on until you find the problem.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[Cero21]

Thanks GingerR. I tried the Compact and Repair Option but it didn't improve the situation. Then, I imported everything into a new database except the reports (tables, queries and forms). I created new reports for everything as I did before and the problem remains.

Any other suggestions?

 

[GingerR]

As you created each report, did each one work successfully before you began to make the next one? Do the recordsources of the reports run successfully? Do you have any code in the Reports' OnOpen events? Or in any other events?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[Cero21]

Yes, each one seemed to work successfully as I was creating them. I can replicate the problem when I open-close-open-close a bunch of reports in succession (both in forms and the Database window in Access). Once one report does not open most of the other ones seem to not want to open as well. I usually have to open up something else (like a form) to "unstick" the reports.

There is no code in the Reports' OnOpen or any other events.

 

[GingerR]

hmmm....I'm stuck. If you want to send me your db (strip out any sensitive data if you want, but know that I will treat it properly) make sure to compact it and zip it first. send to mitzimeat ampersand hotmail dot com. I'll take a look.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[Cero21]

I'll email it to you right now.

 

[BoxHead]

Cero21,

If you have a screen-saver active for your desktop, try turning it off. Switch it to (none) on your screen settings. I don't know why, but I had a user whose app would hang when she ran some queries some of the time. While I was trouble-shooting (cursing, praying, updating reume, etc) her screen-saver popped up. When I moved the mouse to reactivate and logged in, the query results were there.

We removed the screen saver and the problem went away.

Like I say, I don't know why, but maybe the process of accessing the data got caught up in the OS asking itself "Is it time for the screen-saver yet?"

If this doesn't help, I'd ask if any of the tables are linked tables? If so, open MS Explorer to the folder that holds the source db and make sure that the .ldb file is deleted as you close the reports.

Good luck.

John

Use what you have,
Learn what you can,
Create what you need.

 

[GingerR]

oops i don't mean ampersand. Whatever the little "at" symbol is. Hope you caught that....

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[AccessUser22]

Are you doing any heavy coding behind the scenes for these reports? Is it possible that you initialized recordsets without closing them out properly?

Here's a question. When you experience the reports not opening, what happens if you close out of the db and reopen. Are you then able to run them for a short while again? It seems to me maybe you are initializing a recordset and not closing it out and after a while, you have too many handles open and nothing runs properly.

 

[Cero21]

Thanks everyone for all your suggestions and quick replies.

Boxhead - Tried turning off the screensaver but the problem still remains. Yes, the .ldb file deletes itself as I exit.

GingerR - Did you receive the database through email?

AccessUser22 - I don't believe I'm doing extremely heavy coding in my reports but it is possible aren't closing out properly. Yes, if I exit the db and go back in again everything seems to work again. Reports that seem to not want to open will also work again if another report opens successfully. Your idea makes sense. Any suggestions to how I might fix this?

 

[AccessUser22]

If you wrote code behind the reports that initializes a recordset you would need to make sure you include the closeouts.

rst.close
set rst = nothing

The only other thing I've noticed sometimes is if I attempt to run a report with a really long memo field in it (we are talking, over 1000 chars) that the report just hangs and doesn't run. I've gotten around this by truncating the field in a query using len and mid or left. So is it possible the field you are trying to run has a memo field in it?

 

[Cero21]

AccessUser22,

Actually, all the reports take memo fields from their respective tables. Not all the records have text in them and the max text is probably around 30 characters.

 

[AccessUser22]

Hmm, just curious, do you have those set to text or memo? If they are always going to have less than 255 chars in them you should set them to text. Memo should only be used in extreme cases. I would change those to text if possible.

Doubtful though that that is whats causing your problem though since you say there are only 30 chars in each of those fields.

The only other thing I can come up with is network traffic. Access is a network hog so if your network experiences lots of traffic at certain time of the day, that might explain the issue. Are you running it off of a network or local on your machine? If it's on the network, try bringing a copy local and see if those reports run any better. Just a thought to try and narrow down what the problem might be...

 

[GingerR]

No I have not recieved it. Mitzimeat at hotmail dot com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[Cero21]

I changed all the memo fields to text fields and also moved the file to my machine (it was driving off a network drive before). Unfortunately, the problem is still there. Thanks for the ideas though. I really appreciate it.

 

[crawblood]

I think I’m having the same problem. I can’t open a report on either one of my data bases, on the northwind database or if I create a new data base with one table a couple of fields and a couple of records.

I can’t open existing reports. I can’t make new reports either from scratch in design view or using the wizard. When I use the wizard, I go through all the steps setting up the report and when I press the finish button I get an error message saying “The wizard is unable to create your report”.

I have uninstalled and reinstalled access with no change.

 

[quickcel]

I just started having the same problem of not being able to open reports at all - no matter what view.

It happened right when I installed a new printer and set it as the default. As soon as I swtiched to a different default printer I could open the reports as normal. Don't know why this happens at all. Hope that helps.