Report Formatting Code Issue: Working, but taking too long to open

[CarrieR]

I am working within a DB I have been devolping in Access 2003, and have encountered a report coding issue I was hoping someone could help me with:

I have 18 reports, each running off some smaller queried version of 2 main queries (apx. 6000 records each). The purposes of the DB and the reports require the formatting of these reports to change monthly as Actual Revenue is reported.

I can accomplish all of these monthly formatting changes based on visible true/false code. However, this code turns reports that were taking 5-7 seconds to open into reports that are taking around 2 minutes to open. (This is of course unacceptable!)

The details: There are 216 fields in the details, header, and footer, 18 of which need to be reset as visible true/false each month. I have it currently set up as 36 Private Subs (three per month, one for each details & header & footer). These are set up as 96, 96, and 24-line codes resembling the following:

Private Sub JanVisibilityDetails()
Me![JanActuals].Visible = True
Me![JanFinalProj].Visible = True
Me![JanDiffAct].Visible = True
Me![JanDiffPercAct].Visible = True
Me![JanProjC].Visible = False
.....

The Visible True/False setting are listed for each of the 216 fields in each Sub.

These are then called with Case "" statements, such as:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Select Case Me![Month]
Case "Jan"
Call JanVisibilityDetails
Case "Feb"
Call FebVisibilityDetails
.....


This is all done within the Report Code of each individual report.


I have tried various adjustments, like having the code under each Format Sub case, rather than having each case call a separate Private Sub. Same speed. I changed the report to be looking up a table, and no change to speed. Even just having only the current month's Visible True/False code (without any select case) is still ridiculously slow. So I'm pretty sure it's the 216 lines of formatting code that is slowing everything down, but I'm not sure how to fix this. I'm currently changing visibility by hand in the reports, but I'd really like some kind of automated solution.

I'm a trial-and-error, self-taught user who is still relatively new to VB programming code and DB development. Any help anyone can give me would be very much appreciated.

Thanks!
Carrie

[Denburt]

Try putting your code in the open event if you put it in the format event then it will call the code many many more times and this may not be necesary. From what it appears I think your code would only need to run once.

Private Sub Report_Open(Cancel As Integer)

[CarrieR]

Thank you, that certainly solved the problem of speed.

Unfortunately, I now have a different problem. I need to set each monthly set of Visibility True/False codes separately, or I get an error that the code is too long (I can give the exact wording, if necessary). So I'm using a Select Case to call each month's coding (named SetVisible[monthname]), based on the field in the report of "Month" (Control Source and Name):

Private Sub Report_Open(Cancel As Integer)
Select Case Me![Month]
Case "Jan"
Call SetVisibleJan
Case "Feb"
Call SetVisibleFeb
End Select
End Sub

The error I'm getting is "Run-time error '2427': You entered an expression that has no value." When Debug is hit, it takes me to the "Select Case Me![Month]", and from experimenting, I know that if I remove the Select Case language and just have one of the Call lines, that the report opens.
I know this is a relatively standard error, but I can't figure out what went wrong between this as a Report_Open Sub vs being a Format Sub, when the same code worked. Is this a certain setting of Report_Open Event Procedures I have to work around somehow? Or is something else wrong?

[MMcCarthy]

You can't refer to a control in the open event. Is the month always the current month or the previous month. If so you could use

Format(Month(Date()),"mmm") 

to get the current month. Otherwise you will need to set a global variable and pass the Month value through to the report.

Mary

[CarrieR]

Thanks everyone -- problem solved!

Took a while to figure out the exact coding for setting Global Variables (Microsoft Access Help Files don't even seem to mention them), but it all works perfectly now.

[MMcCarthy]

Thanks everyone -- problem solved!

Took a while to figure out the exact coding for setting Global Variables (Microsoft Access Help Files don't even seem to mention them), but it all works perfectly now.

Glad you got it working Carrie.