none
Using SetProperty method to change Back Color of Control - Access 2010 - retry to give reply RRS feed

  • Question

  • This is a retry of a previous question - forum didn't allow a reply to be submitted.

    I'm trying to change colors of command buttons in an access 2010 form.  I can do so sucessfully in an embedded macro of the On Current event for a single button but would like to do so in VBA code for the form. I've tried 2 ways: 1) Forms![Maps]!Lot1A.BackColor = [value - tried string and integer); and 2) DoCmd.SetProperty, acPropertyBackColor, [value]. Neithr way works - get error about wrong data type in an argument or simply a data mismatch.  Doing it in VBA will let me do many buttons at one time when the form opens.

    would appreciate any suggestions or examples of the proper syntax.

    DB Guy replied with a suggestion - Me.Control,BackColor = vbRed

    This works fine with a form created in Access 2010. I have some legacy forms created in Access 2000 (.mdb) that were imported into the 2010 database. none of the solutions work on them - I think because the forms, buttons, etc. are in the old format. Can they be "converted" or updated to the newer format so they will have all the features of same?  This would be very helpfull as there are many buttons on the old forms.

    Monday, December 21, 2015 9:43 PM

Answers

  • I don't think it's a failure of the logic.  I suspect that you are experiencing the behaviour I described in the case of my Access 2007 files when I execute my code to change the forms' appearance in a later version of Access (2016 in my case), i.e. the value is being assigned to the property, but is not being reflected in the appearance of the button.  In my case I simply had to force it by clicking on the colour in question in the palette, where it was already selected as a result of my code.  In your case, as you are dynamically assigning a value to the property at runtime, this is ruled out of course, so, if this is a manifestation of the behaviour which I've observed, I don't see any way you can force it to reflect the value assigned, other than rebuilding the forms from scratch in Access 2010, which I'm sure is not a prospect you'd welcome.

    For what it's worth, to do what you describe I'd be inclined to adopt a different, code-free solution, using conditional formatting of a read-only text box to change the colours, and placing a transparent button over the text box.  With suitable formatting of its border the text box could be made to look like a button, so from the user's perspective they'd be clicking a button.  With a little imagination a lot can be achieved with these sort of 'hybrid' controls.  Their big advantage is in the context of a form in continuous forms view, where changing a property of a control would apply the change to all rows rather than differentially to each row.

    Ken Sheridan, Stafford, England

    • Marked as answer by PeteCV Tuesday, December 22, 2015 12:30 AM
    Tuesday, December 22, 2015 12:18 AM

All replies

  • I'm trying to change colors of command buttons in an access 2010 form.  I can do so sucessfully in an embedded macro of the On Current event for a single button but would like to do so in VBA code for the form. I've tried 2 ways: 1) Forms![Maps]!Lot1A.BackColor = [value - tried string and integer); and 2) DoCmd.SetProperty, acPropertyBackColor, [value]. Neithr way works - get error about wrong data type in an argument or simply a data mismatch.  Doing it in VBA will let me do many buttons at one time when the form opens.

    would appreciate any suggestions or examples of the proper syntax.

    Monday, December 21, 2015 6:40 PM
  • Hi. If you're doing it from the Current event, then something like this should work:

    Me.ControlName.BackColor = vbRed

    If the color you want is not one of the VBA constants, then you can use either the Long Integer equivalent of the color or use the RGB() function.

    Hope that helps...

    • Proposed as answer by André Santo Monday, December 21, 2015 7:49 PM
    Monday, December 21, 2015 6:57 PM
  • How exactly did you "import" the old forms into 2010?
    Monday, December 21, 2015 9:48 PM
  • I used the External Data, Access, Import option and selected them from the legacy database
    Monday, December 21, 2015 10:05 PM
  • I'm currently updating all my online demo files to a new 'house style', using a simple little VBA routine, and am assigning values to the BackColor property of command buttons.  The originals were created in Access 2007 or earlier when buttons had no BackColor property.  What I've found is that, while the correct value is assigned to the property in each button's properties sheet, this is not reflected in the form when opened.  I've found it necessary to open each form in design view, select all the buttons, and manually select the colour in the palette.  The buttons then immediately show the correct background colour.

    Strangely, other command button colour properties like the HoverColor and PressedColor properties, which were also not supported in Access 2007 or earlier, do correctly reflect the colours assigned to them without the need for any manual intervention.  All other objects on forms also show the assigned colours correctly.

    Ken Sheridan, Stafford, England

    Monday, December 21, 2015 10:16 PM
  • Strangely, other command button colour properties like the HoverColor and PressedColor properties, which were also not supported in Access 2007 or earlier, do correctly reflect the colours assigned to them without the need for any manual intervention.  All other objects on forms also show the assigned colours correctly.

    Hi Ken,

    This does not surprise me. HoverColor and PressedColor are "dynamical" colors, i,e, only used on hovering or pressing. Whereas the BackColor is a "static" color, already defined before opening a form.

    Even in A2003 it is simple to mimic the HoverColor and PressedColor properties. Unless you use generalized forms (and controls) it is a hell of a job to use that in any control.

    Imb.

    Monday, December 21, 2015 10:36 PM
  • Hi,

    Thanks for the info.  Let me describe what I’m doing. I want to “color-code” some lots shown on a form, based on checkbox values in each lot record for ‘Sold’ and ‘Used’ – a total of 4 possible combinations. Based on the choices for each lot, a different back color can be defined, and referenced in an If statement in an embedded macro in the Current Event property of the form. This works fine in an Access 2010 form. As I click through a set of forms with multiple lots on each, the lot colors change based on the checkbox values.  I want to do same on the legacy forms but the logic doesn’t work in them. The properties of the buttons do have back color, hover color, etc. but the back color is not changeable.  I need to have a way to make the back color changeable, if possible.

     Really appreciate your input

    Monday, December 21, 2015 11:28 PM
  • I don't think it's a failure of the logic.  I suspect that you are experiencing the behaviour I described in the case of my Access 2007 files when I execute my code to change the forms' appearance in a later version of Access (2016 in my case), i.e. the value is being assigned to the property, but is not being reflected in the appearance of the button.  In my case I simply had to force it by clicking on the colour in question in the palette, where it was already selected as a result of my code.  In your case, as you are dynamically assigning a value to the property at runtime, this is ruled out of course, so, if this is a manifestation of the behaviour which I've observed, I don't see any way you can force it to reflect the value assigned, other than rebuilding the forms from scratch in Access 2010, which I'm sure is not a prospect you'd welcome.

    For what it's worth, to do what you describe I'd be inclined to adopt a different, code-free solution, using conditional formatting of a read-only text box to change the colours, and placing a transparent button over the text box.  With suitable formatting of its border the text box could be made to look like a button, so from the user's perspective they'd be clicking a button.  With a little imagination a lot can be achieved with these sort of 'hybrid' controls.  Their big advantage is in the context of a form in continuous forms view, where changing a property of a control would apply the change to all rows rather than differentially to each row.

    Ken Sheridan, Stafford, England

    • Marked as answer by PeteCV Tuesday, December 22, 2015 12:30 AM
    Tuesday, December 22, 2015 12:18 AM
  • I thank you sir for a thorough analysis. I'll think about the hybrid control idea and see what I can come up with. Will mark your reply as the answer for now. May you and yours have a Merry Christmas (the old-school way of saying Happy Holidays).
    Tuesday, December 22, 2015 12:29 AM