By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,859 Members | 1,338 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,859 IT Pros & Developers. It's quick & easy.

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

P: 21
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.

Mar 16 '07 #1
Share this Question
Share on Google+
5 Replies

Expert 100+
P: 1,356
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)
Mar 16 '07 #2

P: 21
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?
Mar 16 '07 #3

Expert Mod 10K+
P: 14,534
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

Expand|Select|Wrap|Line Numbers
  1. 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.

Mar 16 '07 #4

P: 21
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.
Mar 19 '07 #5

Expert Mod 10K+
P: 14,534
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.
Mar 20 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.