Results 1 to 15 of 15
  1. #1
    Access_Novice is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2013
    Posts
    265

    Setting background color of combo box

    On a command button on a form, I am setting an expression for the OnClick event property as follows:



    Code:
    =[Combo51].[BackColor]=#BFB2CF

    This should change the background of a combo box on the same form to a teal green, but I get an error about an "invalid date value." How did I get the #BFB2CF part? On the format tab, in the "Back color" property, I selected the teal green square from the color palette and #BFB2CF was automatically added. Knowing that #BFB2CF corresponds to teal green, I then went to the command button's OnClick even property to build the expression.

    Why didn't this work? I took out the # and it still didn't work.

    On a similar note, to the On Mouse Down event property of the command button, I used the expression builder to add =NOW() to display the current date and time. When I clicked the command button in Form View, nothing happened.
    Last edited by Access_Novice; 01-05-2015 at 11:35 PM. Reason: Additional comment.

  2. #2
    amrut is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2012
    Location
    India
    Posts
    616
    The color in VBA should be Long and not the HEX. With the Combo Box selected in design view, go to the VBA Editor window, Click F4 to bring up the properties window if it is not open. See the BackColor property and the color code associated with it.
    Ex.
    Code:
    Me.Combo0.BackColor = 13017476
    I used the expression builder to add =NOW() to display the current date and time
    Where do you want to display the date/time ?

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    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.

  4. #4
    Access_Novice is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2013
    Posts
    265
    Quote Originally Posted by amrut View Post
    The color in VBA should be Long and not the HEX. With the Combo Box selected in design view, go to the VBA Editor window, Click F4 to bring up the properties window if it is not open. See the BackColor property and the color code associated with it.
    Ex.
    Code:
    Me.Combo0.BackColor = 13017476

    Teal green corresponds to 9732411 in the VBA editor, property sheet. I tried the following line:
    Code:
    =[Me].[Combo53].[BackColor]=9732411
    and received an error message stating "The object doesn't contain the Automation object 'Me.'.

    Then I tried:
    Code:
    =[Combo53].[BackColor]=9732411
    and nothing happened. I also tried single and double quotes around 9732411 and still nothing.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Did you review the referenced link?

    What do you mean by "corresponds to 9732411 in the VBA editor, property sheet"? I see Hex codes or text description in BackColor property.
    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
    Access_Novice is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2013
    Posts
    265
    Quote Originally Posted by June7 View Post
    Thank you for your suggestion. I looked at your link. Nothing is working. I tried with and w/o double and single quotes, with and w/o "Me." I am wondering if this is even possible. Something seemingly simple. I must be overlooking something. I can't think of anything else to try.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    It's certainly possible. Works for me. But not using codes like 9732411. I use hex or RGB().

    If you want to provide db for analysis, follow instructions at bottom of my post.
    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.

  8. #8
    Access_Novice is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2013
    Posts
    265
    Quote Originally Posted by June7 View Post
    Did you review the referenced link?

    What do you mean by "corresponds to 9732411 in the VBA editor, property sheet"? I see Hex codes or text description in BackColor property.
    A previous poster suggested going into the VBA editor, then the property sheet. In the property sheet in Design View, in the Back Color property, I selected the 3 dots, and from the color palette I chose teal green. Then with the control selected (command button) I went into the VBA editor and saw that the Back Color was now defined as 9732411. That's how I got that number.

    But I also tried hexadecimal codes and those didn't work either.


    I just noticed that the form I am on doesn't have a name property. In the Navigation Pane, it is called "ContractSetup." But when I check the property sheet, I don't see any indication of a name for the form. Could this be the problem? That Access doesn't know which form I want to refer to even though it is the only form I have? I doubt if this matters but the form is not bound to anything. I'm just experimenting with expressions to see how they work.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    No, Properties sheet for form (or report) does not show the Name property because you can't set or change form/report name in Properties sheet.

    I've never worked with the Properties display in VBA editor. Interesting that it does show different value.
    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.

  10. #10
    Access_Novice is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2013
    Posts
    265
    Quote Originally Posted by June7 View Post
    No, Properties sheet for form (or report) does not show the Name property because you can't set or change form/report name in Properties sheet.

    I've never worked with the Properties display in VBA editor. Interesting that it does show different value.
    I have no idea why it would show a different value. Doesn't make too much sense to me.

    Ok, I'm uploading my DB with fake data. If you go to the Command button, then the On Click property, you will see the expression I am building. This is really driving me crazy.
    Attached Files Attached Files

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    You are putting the expression in the event property. It should be in the VBA event procedure.

    Select [Event Procedure] from the property and click the ellipsis (...) to open the VBA editor. Type code in the procedure.

    Private Sub Command67_Click()
    Me.Label52.BackColor = vbGreen
    End Sub

    Also, change the label's BackStyle property to Normal.
    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.

  12. #12
    Access_Novice is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2013
    Posts
    265
    Quote Originally Posted by June7 View Post
    You are putting the expression in the event property. It should be in the VBA event procedure.

    Select [Event Procedure] from the property and click the ellipsis (...) to open the VBA editor. Type code in the procedure.

    Private Sub Command67_Click()
    Me.Label52.BackColor = vbGreen
    End Sub

    Also, change the label's BackStyle property to Normal.
    Thank you so much. It worked. The only thing I don't understand is this. You can click in the On Click property box. Although you can select the drop down arrow at this point, you can also select the ellipsis, and if you do, you are presented with 3 choices (Macro, Expression or Code Builder). Because Expression is a choice (which is what I was selecting), and because you can construct an expression inside of the Expression Builder window, I figured it would be reasonable to believe that you can enter an expression in the On Click property box, i.e. assigning an Expression to the On Click event property.

    So if you can't use an expression in the property box, why would Access allow this to be possible?

  13. #13
    Access_Novice is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2013
    Posts
    265
    Quote Originally Posted by June7 View Post
    You are putting the expression in the event property. It should be in the VBA event procedure.

    Select [Event Procedure] from the property and click the ellipsis (...) to open the VBA editor. Type code in the procedure.

    Private Sub Command67_Click()
    Me.Label52.BackColor = vbGreen
    End Sub

    Also, change the label's BackStyle property to Normal.
    I wish I could give you many more favorites for this thread because this was driving me nuts. Also you took a lot of time to answer my questions, so I really appreciate that.

    Now I will be able to sleep better tonight.....LOL (seriously).

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Can use expression in event property - an expression that calls a custom VBA function or a macro. Your expression was to set a control property.

    Also, the Me alias is a VBA instrument, Access objects do not understand it.
    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.

  15. #15
    amrut is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2012
    Location
    India
    Posts
    616
    I've never worked with the Properties display in VBA editor. Interesting that it does show different value.
    The Properties Window in VBA Editor shows the Long value of the color selected from the Property Sheet of the control.
    This one works
    Code:
    Me.Text9.BackColor = 10856415

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

Similar Threads

  1. Replies: 2
    Last Post: 03-05-2013, 04:11 PM
  2. Background Color
    By Meccer in forum Access
    Replies: 4
    Last Post: 05-30-2011, 06:49 AM
  3. Background color for fields in a subform
    By eric.opperman1@gmail.com in forum Forms
    Replies: 3
    Last Post: 03-19-2011, 07:46 PM
  4. Change Row Background Color Programmatically
    By sales@4bco.com in forum Programming
    Replies: 2
    Last Post: 10-25-2009, 11:17 AM
  5. background color in a form
    By taggart in forum Forms
    Replies: 1
    Last Post: 05-07-2009, 10:37 AM

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