Data disappears in Report View

[carrollrl]

I have two related issues with reports. That is the data shows in report view and then disappears. I apologize for the lengthy post, but I am confused and need help. I am a fairly experienced Access VBA user, but this has me stumped. :banghead:

The first is the simplest. I have a report that has a query recordsource and I calculate totals and populate the report in the OnLoad event using VBA code. This displays perfectly in "report view" and prints or exports fine. My issue is that after the print or export, the report has no data in my fields in "report view". It is like it is reloading the report without invoking the OnLoad event. When I close the report and re-open in report view, everything is fine again until I print.

The second issue is related. I have another report that is a bit more complicated. Due to the number of controls (I use many text boxes to display my calculations), I am using 3 subreports to get around the limitations in the number of controls. In report view, the report looks fine, but when I scroll down to the bottom and then back up, the text boxes in the subreport sections are empty. The parts of the report not in subreports is fine. By using "Msgboxes" in events, I discovered that when I scroll down, the subreports close, then re-open when I scroll back up. Again, due to an Access limitation of 22 inches per section, the I placed the subreports in the "report Header" section. It appears that my problem occurs when I scroll down to the "detail" section of the main report.

Can someone please help me figure out how to keep my data visible?

Thanks

Rick

 

[GinaWhipp]

Part 1 - That would happen because On_Load only occurs once so it does bypass the On_Load event when *requerying*. You may want to Call the On-Load event in the On_Current event of your report. That should solve the problem.

Part 2 - Not sure about that unless you are doing a requery on the subreports and have an On_Load event? It *might* be similar to what is happening in Part 1. Again, the On_Load event takes place when the Report opens and fires once so perhaps the On_Current event is what you want.

 

[carrollrl]

Gina,

Thanks for your quick response. I have put "Msgboxes" in every event of the report so that I could watch the progression of what is triggered as the report is processed. It all looks fine until I export the report. After the export completes, Access shows a "Save Export Steps" screen. When I peek behind that screen, my report is still fine. As soon as I close that screen, the data disappears. The On_Current event does not fire. As a matter of fact, no event fires. I expected that some event would fire, so I put the Msgboxes in all of the events so that I could figure out what was firing.

Any ideas?

Thanks

Rick

 

[GinaWhipp]

Can you post the code? Perhaps there is a clue in it...

 

[carrollrl]

Here is a zip file with the code.

There is a word document with the screen shots of the report in design view, and in report view before and after the export.

The other word document is the VBA code. I am not the most efficient of programmers, so it tends to be a bit long. It also contains all of my debugging Msgbox routines.

I appreciate the help.

 

[The_Doc_Man]

I have seen this before. If you see the report in REPORT VIEW it doesn't work but if you switch to PRINT PREVIEW it does. Before beating your head against a wall, see if the chosen view makes a difference.

If it does then don't worry about your code quite so much. It might be a "quirk" of REPORT view.

 

[GinaWhipp]

Hmm, pretty much got me stumped as I see nothing there that is requerying but... You might want to consider putting your code in the On_Open event because I see you are setting the RecordSource as well.

EDIT: See The Doc Man's reply!

 

[GinaWhipp]

Doc... I didn't catch that but you are right! <getting old>

 

[The_Doc_Man]

I have searched various articles online. There is a dearth of help in the Access Help Files on this subject. (No DUH there, I'm afraid.)

It appears from what I could find on the web that in Report View the OnFormat event will not fire but the OnPaint event WILL fire. This kind of behavior is sporadically reported over many different forums for differences in OnFormat vs. OnPaint and a couple of other form-based events that don't fire consistently depending on mode of viewing. This occurs even when the section's "Display When" property is set to ALWAYS. I happened to have a case of that anomaly myself and was able to verify by experiment that the setting makes no difference in this problem.

Dontcha just love consistent behavior?

EDIT: But actually, it IS consistent. Consistently confusing - across more than one version of Office. It was in Ac97 based on some of the older articles, and it shows up in Ac2K, Ac2003, and Ac2007. Haven't got an Ac2010 to play with yet - still waiting for the office to upgrade me there, but I'm not going to hold my breath on this one.

 

[GinaWhipp]

Hmm, probably why I don't use Report View... useless!

 

[The_Doc_Man]

I have now verified by experiment for one of my simpler reports that this strategy works:

Option Compare Database

Dim lAORSize As Long

Option Explicit

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)

    Cancel = 0
    
    If FormatCount = 1 Then
        lAORSize = DCount("[SRVID]", "qtSRVADM", "[SAUID]=" & Trim$([SAUID]))
        [UserAOR] = CStr(lAORSize)
    End If

End Sub

Private Sub GroupHeader0_Paint()

    lAORSize = DCount("[SRVID]", "qtSRVADM", "[SAUID]=" & Trim$([SAUID]))
    [UserAOR] = CStr(lAORSize)

End Sub

The field I call AORSize / [UserAOR] displays correctly in both views. Only one of the two fires when I change views.

NOTE: For the OnPaint case, you cannot cancel and don't need to worry about the FormatCount parameter because of the difference in the way they are created.

Report View is not overly useful but it has its moments for short reports. BTW, I don't any problem with anything that is statically bound on the report in question. Those work in both views. It is only the ones that have either Format or Paint events that will have issues.

 

[GinaWhipp]

Great detective work!

 

[carrollrl]

Thanks to both of you guys. I am off today but will continue experiments on Monday and let you know how I make out.

 

[carrollrl]

Print Preview instead of report view seems to solve my problem with the first report. A bit of a pain, but obviously an anomaly with report view.

I am still having similar problems with the second report. I think I see what it is doing, but I am not sure how to handle it. When I open the report in either report view or print preview, it looks fine. When I go to print or export it from either view, the subreport portions show as blank. I have attached a zip file with 4 files. The PDF file is the result of the export. You will see that 3 of the "sections" are empty. I show partial screen shots in print preview before and after the export, and the report looks fine.

I have also included the screen shots of the application in design view. It took 7 shots to show all of it. You will see that there are 7 "sections" of my report.

Section 1 is not a subreport and in the Access "report Header" section
Sections 2-4 are subreports in the "report header" section
Sections 5-7 are not subreports and they are in the Access "Detail" section

I used this technique to avoid the maximum control limit and sections size limit that I encountered when constructing this report.

Based upon my Msgbox "debugging" trick, I see that during print or export that the subreports open again, and then close. Without the "load" event triggering, the reports will be blank.

Everything should be in the attached ZIP file.

Thanks again for your help.

 

[GinaWhipp]

I would expect the On_Load event not to fire again. The Report only Loads once. I'm wondering if the On_Format will work... but not sure as I have never had a report that had that much going on. Normally, one would have the calculations in the query and the Report is just to show the data.

As a side note, having this many seperate controls on a report has me wondering if you table is set up more like a Spreadsheet. Adding across makes for alot of calculations, especially when you have to add each row as opposed to one row on a continuous report.