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

Changing the appearance of a command button

P: 91
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:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.Hourglass (True)
  2. Me.cmdExitButton.Caption = "Please wait ..."
  3. Me.cmdExitButton.ForeColor = RGB(255, 0, 0)
  4. Me.Refresh
  5. WantedDB = IIf(Currently = "test", "live", "test")    ' "Currently" was set to the current BE by function WhichBackEnd
  6. For Each tbl In CurrentDB.TableDefs
  7.     If InStr(tbl.Connect, Currently) > 0 Then         ' i.e. if it is a linked table
  8.           tbl.Connect = Replace(tbl.Connect, Currently, WantedDB)  ' Update the connect string test-->live or live-->test
  9.           tbl.RefreshLink                             ' Relink the table
  10.     End If
  11. Next
  12. LogActivity ("switched to")
  13. Me.Refresh
  14.  
  15. ExitSub:
  16.     DoCmd.Hourglass (False)
  17.     Me.cmdExitButton.Caption = "Exit Database"
  18.     Me.cmdExitButton.ForeColor = RGB(255, 255, 255)
  19.     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
Jun 21 '17 #1

✓ answered by PhilOfWalton

Hi Petrol

Had be foxed, so I simulated your code with this

Expand|Select|Wrap|Line Numbers
  1.     Dim Lngi As Long
  2.  
  3.     DoCmd.Hourglass (True)
  4.     CmdExitButton.ForeColor = vbRed
  5.     CmdExitButton.Caption = "Please Wait..."
  6.  
  7.     DoEvents
  8.     For Lngi = 1 To 500000000                  ' Simulates a time delay
  9.     Next Lngi
  10.  
  11.     DoCmd.Hourglass (False)
  12.     CmdExitButton.ForeColor = vbWhite
  13.     CmdExitButton.Caption = "Exit Database"
  14.  
Until I put the DoEvents in line 7, I had the same problem as you, but that seems to fix it.

Phil

Share this Question
Share on Google+
7 Replies


PhilOfWalton
Expert 100+
P: 1,430
Hi Petrol

Had be foxed, so I simulated your code with this

Expand|Select|Wrap|Line Numbers
  1.     Dim Lngi As Long
  2.  
  3.     DoCmd.Hourglass (True)
  4.     CmdExitButton.ForeColor = vbRed
  5.     CmdExitButton.Caption = "Please Wait..."
  6.  
  7.     DoEvents
  8.     For Lngi = 1 To 500000000                  ' Simulates a time delay
  9.     Next Lngi
  10.  
  11.     DoCmd.Hourglass (False)
  12.     CmdExitButton.ForeColor = vbWhite
  13.     CmdExitButton.Caption = "Exit Database"
  14.  
Until I put the DoEvents in line 7, I had the same problem as you, but that seems to fix it.

Phil
Jun 21 '17 #2

P: 91
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? :)
Jun 21 '17 #3

PhilOfWalton
Expert 100+
P: 1,430
Ah Petrol

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

Regards

Phil
Jun 21 '17 #4

NeoPa
Expert Mod 15k+
P: 31,418
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 :
Expand|Select|Wrap|Line Numbers
  1. Me.Painting = False
  2. ...
  3. Me.Painting = True
Jun 21 '17 #5

P: 91
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?
Jun 21 '17 #6

NeoPa
Expert Mod 15k+
P: 31,418
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.
Jun 22 '17 #7

P: 91
Thanks, NeoPa :-)
Peter
Jun 22 '17 #8

Post your reply

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