Changing the appearance of a command button

[Petrol]

I want to temporarily change the appearance (caption, text colour) of a command button cmdExitButton when another command button is clicked. I have the following code in the On Click event of the second button:

DoCmd.Hourglass (True)
Me.cmdExitButton.Caption = "Please wait ..."
Me.cmdExitButton.ForeColor = RGB(255, 0, 0)
Me.Refresh
WantedDB = IIf(Currently = "test", "live", "test")    ' "Currently" was set to the current BE by function WhichBackEnd
For Each tbl In CurrentDB.TableDefs
    If InStr(tbl.Connect, Currently) > 0 Then         ' i.e. if it is a linked table
          tbl.Connect = Replace(tbl.Connect, Currently, WantedDB)  ' Update the connect string test-->live or live-->test
          tbl.RefreshLink                             ' Relink the table
    End If
Next
LogActivity ("switched to")
Me.Refresh
 
ExitSub:
    DoCmd.Hourglass (False)
    Me.cmdExitButton.Caption = "Exit Database"
    Me.cmdExitButton.ForeColor = RGB(255, 255, 255)
    Exit Sub

This works when I step through the code, but when I run it normally the hourglass appears but the button doesn't change appearance.
(The Me.Refresh in line 4 doesn't appear to be necessary - it doesn't make any difference).

Does anyone have any suggestion as to why?
Peter

[PhilOfWalton]

Hi Petrol

Had be foxed, so I simulated your code with this

    Dim Lngi As Long
 
    DoCmd.Hourglass (True)
    CmdExitButton.ForeColor = vbRed
    CmdExitButton.Caption = "Please Wait..."
 
    DoEvents
    For Lngi = 1 To 500000000                  ' Simulates a time delay
    Next Lngi
 
    DoCmd.Hourglass (False)
    CmdExitButton.ForeColor = vbWhite
    CmdExitButton.Caption = "Exit Database"
 

Until I put the DoEvents in line 7, I had the same problem as you, but that seems to fix it.

Phil

[Petrol]

How clever! I had never heard of DoEvents. Thank you.

(You were just sitting there, saying "I wonder when Petrol will run into his next problem", weren't you? :)

[PhilOfWalton]

Ah Petrol

You can't beat regular customers (even the non paying ones!!!)

Regards

Phil

[NeoPa]

Hi Petrol.

You'll notice, in Access particularly, that when code runs it tends to be quite dogged about keeping control of the processor. IE. When it's running then other stuff like screen displays can be left waiting. This is largely due to the nature of database work. For instance it's important that things don't change behind the scenes when half way through a block update query.

Thus VBA gives the opportunity for your code to be a little better behaved by making DoEvents available to programmers. This tells the managing code that your code can afford to allow other processes to run in the background before it takes its next step. It's always a good idea to bear in mind the behaviour of your projects. They should be good citizens where possible (IE Use DoEvents where it makes sense to).

In the other direction, it's also possible to delay updates on Forms, Reports etc using :

Me.Painting = False
...
Me.Painting = True

[Petrol]

Hmm, thanks, NeoPa. I wish I had known this a year ago when I first started blundering about in VBA coding!

Most of my many procedures are quite short (and it would be along slow process to even find them all now!)... but are there any rules of thumb to indicate where/when it's a good idea to throw a few DoEvents in?

[NeoPa]

Yes. I would say between running of action queries in a sequence. Also in any code that takes a long time to execute. Generally these fall into some sort of loop so add it within the loop.

[Petrol]

Thanks, NeoPa :-)
Peter