Report crushes on open (1 Viewer)

[Hello1]

Some strange thing is happening. The report crushes on opening, but not always. As it seems only if the query which the report uses is "big". I did the debugging but it doesnt crush at any of my code lines but at the very end, when the "End Sub" is executed. This is all happening in Office Access 2007 using a .mdb. Running the same .mdb with office 2010 the same report runs without problems, no matter the size of the query. Its crushing on other PCs, not just one.

So far I tried creating a new report copying the controls from the old one and the VBA code behind.
I tried converting the .mdb to .accdb. I also tried importing the whole .mdb into a new blank database with the access import/export feature. I also did a decompile and compact, no help.

Any tips? I think I had this problem once but cant remember how did I solve it, or did I solve it

Thanks!

 

[theDBguy]

Hi. Are you saying there is no consistent situation you can pinpoint when the report crashes? Are you able to post a sample copy for us to test?

 

[Hello1]

I forgot to mention that I also tried this (found it on stackoverflow):
Remove Binary Data from Form

Sometimes the crashes occur in a single form or report. If it is corrupt binary data, then the crashes should be occurring on different computers, with different users. If this is the case then follow these steps. (Advanced Users Only)

In the immediate window save the object as text.

Application.SaveAsText acForm, "MyForm", CurrentProject.Path & "\MyForm.txt"

Rename the original form item (e.g. rename to MyForm_Bak)

Open the exported file in notepad
Delete the "Checksum=" line (should be on line 3)
Clear out binary data
Look through the file.
There will be lines that start with "Parameter = Begin" and have lines of encoded binary data, ending with a line consisting of "End"
When you locate one of these lines, you will need to (inclusively) delete all lines from the Begin to the End.
The parameters you should delete are: NameMap, PrtMip, PrtDevMode, PrtDevNames, PrtDevModeW, PrtDevNamesW
All of these blocks should appear BEFORE your form control definitions
While you have the file open, scroll through the rest of the file and look for anything that catches your eye, especially in the VBA module code at the bottom.
Save the file
In Access, on the immediate window, load the form back in

Application.LoadFromText acForm, "MyForm", CurrentProject.Path & "\MyForm.txt"

Decompile / Compact Repair / Recompile

Open the form and hopefully everything is working better

Hi. Are you saying there is no consistent situation you can pinpoint when the report crashes? Are you able to post a sample copy for us to test?

I can recreate the crush, its happening with a certain criteria which I assign to the query. On a date field, its between first and second date. The bigger the gap (bigger query results) more likely it is for the report to crush. Right now I know on which criteria it will crush for sure.
I cant share the copy now, too much to remove and the data used is stored in another database. Maybe tomorrow if I dont figure out what the solution is.

 

[theDBguy]

Hi. If it crashes based on the size of the query result, what is the current file size of the db when it crashes?

 

[Hello1]

The .mdb which contains forms, reports n queries is about 3MB, the backend .mdb with tables is about 72MB

 

[theDBguy]

The .mdb which contains forms, reports n queries is about 3MB, the backend .mdb with tables is about 72MB

Hi. Thanks for the additional information. I guess the problem isn't caused by lack of space then. Hmm...

 

[Hello1]

By removing the text filed in page footer which shows the page number. Its control source being ="Page " & [Page] & " of " & [Pages] the report doesnt crush anymore...

 

[theDBguy]

By removing the text filed in page footer which shows the page number. Its control source being ="Page " & [Page] & " of " & [Pages] the report doesnt crush anymore...

Hmm, That is interesting to hear. How many pages is the report usually when it crashes?

 

[Hello1]

Just one page

 

[theDBguy]

Just one page

Okay, that's even more interesting then. It would be nice to see this in action, but no pressure. Cheers!

 

[Hello1]

Here some more details...
Using only this

="Page " & [Page]

the report works. However, when I add only

=[Pages]

or I use this

="Page " & [Page] & " of " & [Pages]

the report/access crushes.

 

[theDBguy]

Here some more details...
Using only this

="Page " & [Page]

the report works. However, when I add only

=[Pages]

or I use this

="Page " & [Page] & " of " & [Pages]

the report/access crushes.

Hi. Thanks for the additional information. Does this also happen if you create a new report from scratch?

 

[Hello1]

So far I tried creating a new report copying the controls from the old one and the VBA code behind.

Completely from scratch I didnt try, too time consuming. However, I did make a new report and was coping control by control from the old one and thats how I found the cause. The report uses Group & Sort and Im using Group on on one field and sort on another one, Im suspecting that this grouping might be confusing the Pages control..not sure tho

 

[theDBguy]

Completely from scratch I didnt try, too time consuming. However, I did make a new report and was coping control by control from the old one and thats how I found the cause. The report uses Group & Sort and Im using Group on on one field and sort on another one, Im suspecting that this grouping might be confusing the Pages control..not sure tho

Hi. What I was thinking, really, all you had to do to test and try to find out the cause of the problem is create a blank report, bind it to the same query as the original, add your grouping and sorting, and then simply add the [Pages] bit. You don't have to add all the other controls, since we're just trying to verify what you think is causing the issue is really what it was. Just my 2 cents...

 

[Hello1]

It works fine until I add the Me.GroupLevel(0).ControlSource = "Something" in VBA on Report open. Then it crushes. If the query is smaller then it doesnt, or if I remove the [Pages]

 

[theDBguy]

It works fine until I add the Me.GroupLevel(0).ControlSource = "Something" in VBA on Report open. Then it crushes. If the query is smaller then it doesnt, or if I remove the [Pages]

Is "somevalue" a dynamic value? Let's say you know what it is going to be, just for testing's sake, try putting it in using design view rather than via code, just to see if it makes any difference.

 

[Hello1]

Yes its dynamic. I did as you said, disabled it in the code and have put it through design view and the report/access doesnt crush until I set the control source in two of the text boxes through code. One of the text boxes is located in the custom Group on footer and the other one in its header.

 

[theDBguy]

Yes its dynamic. I did as you said, disabled it in the code and have put it through design view and the report/access doesnt crush until I set the control source in two of the text boxes through code. One of the text boxes is located in the custom Group on footer and the other one in its header.

Well, at least, I think, you're getting closer to figuring out where the problem is coming from, right? Keep digging and good luck!

 

[Hello1]

I guess
Probably the solution would be to make separate reports instead just one like now, so I dont have to set through code the control source. Now is only one report but its controls are changed on report open