Slow speed of programmatically setting control properties

I have recently converted a system from AccessXP to Access 2007. One of my forms periodically displays a tab which contains 120 labels (or text boxes) for which properties (NOT values) may need to dynamically changed when other values on the form are changed. The properties most commonly affected are BackColour, BackStyle, BorderStyle and Tab. Using AccessXP, the properties of all 120 controls could be changed in less than a second. With Access 2007, the same process takes 10 to 13 seconds. Has anyone a suggestion as to why this should be or, better still, some technique for making the process MUCH quicker in Access 2007.
Thanks, Tony
TonyWoottonAsked:
Who is Participating?
 
TonyWoottonConnect With a Mentor Author Commented:
Hi Jim,
The form on which I had this problem also had another performance problem related to Access 2007. I had 5 tab controls on it which were moved at runtime. Fine with XP but diabolically slow with 2007. I re-worked the form using a sub-form to replace 3 of the tab controls and eliminate moving tab controls at run-time. This not only improved the performance of the form as a whole, but also further improved the problem which was the subject of this question so that the original 10-13 seconds went sub-1 second which is quite acceptable. I have no idea why the secondary effect should have been there, but I'll accept it as just one of life's mysteries. It tends to suggest that the Access 2007 engine requires significant performance tuning by Microsoft! Meanwhile, I'll accept your suggestion that prompted me to change direction; do please contact me if I can amplify anything that has been touched upon.
Regards, Tony
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<I have recently converted a system from AccessXP to Access 2007. >>
<<Using AccessXP, the properties of all 120 controls could be changed in less than a second. With Access 2007, the same process takes 10 to 13 seconds. Has anyone a suggestion as to why this should be or, better still, some technique for making the process MUCH quicker in Access 2007.>>
  Make sure the DB has been converted (not simply opened) and that it has been compiled.
JimD.
0
 
TonyWoottonAuthor Commented:
Hi Jim,
Thanks for your reply.
It has been converted (2002 to 2007) and compiled.
I attach an Accdb (renamed .txt) which gives an example of what I had though would work slowly, but it actually works faster than the the MDB. I'll prep a snippet of code that has got the problem asap.
Regards, Tony
Props.txt
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Tony,
  If I get time, I'll load A2007 to check this out, but so far I haven't had any reason to use A2007.
<<I attach an Accdb (renamed .txt) which gives an example of what I had though would work slowly, but it actually works faster than the the MDB. >>
  I'm a little confused by that statement...I thought it was working slower then the original MDB?
  only other thing I can offer at the moment is to make sure your up to date on service packs.  I know some have seen slowness in A2007 apps, but not to the degree you've indicated.
JimD.
0
 
TonyWoottonAuthor Commented:
Hi Jim
Thanks for your reply. My apologies for confusing you! I meant that I had thought the example would work much more slowly than it did. It wasn't practicable for me to use a subset of my app code as a test harness so I selected statements that I thought were causing the problem. Since your reply, I bracketed the offending code with debug.print statements and the following fragment takes 8 seconds to execute:
    Debug.Print Now
    For idx = 65 To 68
        For jdx = 1 To 30
            str = Chr$(idx) & jdx
            If Me(str).Tag <> "Unused" Then
                Select Case rstTreat(str)
                    Case 0
                        Me(str).BackStyle = vbTransparent
                        Me(str).BorderStyle = vbSolid
                    Case 2
                        Me(str).BackColor = Prob1Colour
                        Me(str).BackStyle = vbSolid
                        Me(str).BorderStyle = vbTransparent
                    Case 4
                        Me(str).BackColor = Prob2Colour
                        Me(str).BackStyle = vbSolid
                        Me(str).BorderStyle = vbTransparent
                    Case 8
                        Me(str).BackColor = Prob3Colour
                        Me(str).BackStyle = vbSolid
                        Me(str).BorderStyle = vbTransparent
                    Case 16
                        Me(str).BackColor = Prob4Colour
                        Me(str).BackStyle = vbSolid
                        Me(str).BorderStyle = vbTransparent
                    Case 32
                        Me(str).BackColor = Prob5Colour
                        Me(str).BackStyle = vbSolid
                        Me(str).BorderStyle = vbTransparent
                    Case Else
                        Me(str).BackColor = Prob6Colour
                        Me(str).BackStyle = vbSolid
                        Me(str).BorderStyle = vbTransparent
                End Select
            End If
        Next jdx
    Next idx
    Debug.Print Now
Incidentally, I should add that the If ...... "Unused" Then means that only 90 of the potantial 120 sets of core statements within the inner loop are executed. The controls are either text boxes or labels (it doesn't seem to matter which).
Regards, Tony
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Try this:
    Debug.Print Now

    Dim strBackColor as string
   Dim strBackStyle as string
    Dim strBorderStyle as string
 
           Select Case rstTreat(str)
                    Case 0
                        strBackColor  = vbWhite
                        strBackStyle = vbTransparent
                       strBorderStyle = vbSolid
                    Case 2
                        strBackColor  = Prob1Colour
                        strBackStyle  = vbSolid
                        strBorderStyle = vbTransparent
                    Case 4
                        strBackColor  = Prob2Colour
                        strBackStyle = vbSolid
                        strBorderStyle = vbTransparent
                    Case 8
                        strBackColor  = Prob3Colour
                        strBackStyle = vbSolid
                        strBorderStyle = vbTransparent
                    Case 16
                        strBackColor  = Prob4Colour
                        strBackStyle = vbSolid
                        strBorderStyle = vbTransparent
                    Case 32
                        strBackColor = Prob5Colour
                        strBackStyle = vbSolid
                        strBorderStyle = vbTransparent
                    Case Else
                        strBackColor = Prob6Colour
                        strBackStyle = vbSolid
                        strBorderStyle = vbTransparent
                End Select

    For idx = 65 To 68
        For jdx = 1 To 30
            str = Chr$(idx) & jdx
            with me(str)
              If .Tag <> "Unused" Then
                        .BackColor = strBackColor
                        .BackStyle = strBackStyle
                        .BorderStyle = strBorderStyle
              End If
            End with
        Next jdx
    Next idx
    Debug.Print Now
0
 
TonyWoottonAuthor Commented:
Hi Jim,
Although your select clause would need to follow the "str =" statement, I was prompted to think about what you've put inside the With construct. The value of rstTreat(str) will be the same as that of the .Tag value for the corresponding control for most of the me(str) controls. Assuming the assignment of new property values takes the time, I can adjust my routine using an "If rstTreat(str)<>me(str).tag Then ....".
I'm not sure if that will work, but it'll take a lot less time than reijigging the data structure and trying an altogether different technique. I'll get back to you.
Regards, Tony
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<rstTreat(str)>>
 Ugh, sorry.  Thought  it was looking at  one specific field in the record rather then the field that the control was based on, so yes it needs to be in the loop, which defeats what I was trying to do.
Sorry about that :(.
JimD.
0
 
TonyWoottonAuthor Commented:
Hi Jim,
No need to be sorry! Your suggestion gave me an idea, although it'll take me a while to check it out and come back to you.
Regards, Tony:)
0
 
TonyWoottonAuthor Commented:
Hi Jim,
I've just tested the rstTreat value against the corresponding .Tag value and cut down the time by a factor of 4. That's tolerable until I can re-visit to do a bit more tuning. Mind you, I have no idea why the problem arises in the first place. Incidentally, I have another similar problem. I developed a routine to programatically move tab controls and all that they contain at run-time (so I can create a spread-out form in design view that is maintainable, but puts tabs in the correct positions at run-time. Works fine in 2002, but runs like a dog in 2007. Haven't solved that one yet!
Regards, Tony
0
 
TonyWoottonAuthor Commented:
Hi Jim,
Although this wasn't a solution, as I said in my last comment, it prompted me to solve the problem. Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.