Results 1 to 6 of 6
  1. #1
    gopherking is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2011
    Posts
    71

    Conditional Formatting vs. VBA Formatting

    I have been researching this for some time now but haven't received any sound advice on whether it is faster to perform formatting on text boxes in a report via VBA or the built in Conditional Formatting. Does anyone have any suggestions as to whether one is faster than the other?



    The reason I ask this is that I have a report with quite a few text boxes that currently all have some sort of Conditional Formatting on them (Not all have the same Conditional Formatting). I know that when you only have a few controls that require Conditional Formatting, the built in solution in Access 2007 should be sufficient, however when the number of controls grows and when the report has multiple records that require each control to be handled on each record, the Conditional Formatting becomes painfully slow (much like receiving 1000 paper cuts) and renders the report as pointless considering a user must run it for 10's of minutes to an hour to complete.

    So, with that said, I am really just looking for anyone's suggestion as to whether I should invest some time in the VBA coding to do what the Conditional Formatting does, or do I just go back to the requestor and let them know that formatting all these fields is too much for the system to handle. Any response will be greatly appreciated, thanks!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The issue with VBA formatting is that code changes property settings. The change would reflect in all instances of the control from the first one that meets the condition. Do a test of this on your report of just one control to verify. I just did. Code would be in the Format event of the section the control is in.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    gopherking is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2011
    Posts
    71
    Thanks for the response June. You are correct that the formatting is applied to all fields rather than on a record by record basis (unless I can think up some code to handle this), but there is also another problem which is that the formatting only occurs either when a report is printed or if it is opened in Print Preview. In my instance, I have users who view the reports in Access in the Report View state, which doesn't apply the OnFormat event. However, if you go to Print Preview then back to Report View, the formatting remains visible (just in case someone wants to use this as a work around) ...

  4. #4
    gopherking is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2011
    Posts
    71
    Just to note something, I have (HAD!!!) a Group within my report, which seems to have contributed heavily to the slowness in the creation of the report. Once removed from a group and placed into the report as a field then sorted by that field, the report runs in seconds as opposed to minutes to 10's of minutes. This seems to have cleared up my issue, but I would still be interested if anyone has any suggestions as to whether or not they would format a report in VBA.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I do some report formatting with VBA but the reports display a single record.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    hertfordkc is offline 18 year novice
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    481
    Just a note: The automatic pagination feature in Access can effectively cause the report to be recreated a number of times and this can become extremely time consuming when complicated by multiple group levels and calculations within each group (aside from the conditional formatting considerations). There may be some ways to short circuit this, but it has been too long since I was faced with the problem. If you suspect this is going on, you should probably look into repagination.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Conditional formatting
    By avarusbrightfyre in forum Forms
    Replies: 3
    Last Post: 07-01-2011, 11:18 AM
  2. Conditional Formatting
    By Desstro in forum Programming
    Replies: 3
    Last Post: 12-01-2010, 09:52 PM
  3. Conditional Formatting
    By pkstormy in forum Code Repository
    Replies: 0
    Last Post: 08-30-2010, 09:31 PM
  4. Conditional Formatting
    By cevatyildiz in forum Forms
    Replies: 8
    Last Post: 04-30-2010, 12:01 PM
  5. Conditional formatting
    By ylivne in forum Reports
    Replies: 1
    Last Post: 07-12-2009, 06:18 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums