Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Colour-coding all controls (XP)

    On a form, I want to use a function on the OnCurrent event to colour all controls that are blank yellow. I'm trying to use the 'For each ctl' structure, but Access tells me that I cannot refer to a property unless it has the focus? I don't believe this is necessarily the case?! Andy.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Colour-coding all controls (XP)

    There are some properties that can only be referred to if a control has the focus, but color is not one of them, as far as I know. Could you post the code you are using?

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Colour-coding all controls (XP)

    I haven't got the full code with me at the moment. In part it used 'For each ctl in Screen.ActiveForm.Controls, If TypeOf ctl is TextBox, If ctl.Text Is Null Then, ctl.BackColor = '. I tried using 'if ctl = Null', 'if ctl = ""' but no variation worked. I can recreate the entire function later if it helps.

  4. #4
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Colour-coding all controls (XP)

    You could adopt another approach and use conditional formatting to achieve this.

    Set the conditional format to:
    Expression is... IsNull([txtControlName]) and set the Fill/BackColor to whatever you want.

    Just a thought.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Colour-coding all controls (XP)

    Try something like this:

    Private Sub Form_Current()
    Dim ctl As Control
    For Each ctl In Me.Controls
    Select Case ctl.ControlType
    Case acTextBox, acComboBox
    If IsNull(ctl) Then
    ctl.BackColor = vbYellow
    Else
    ctl.BackColor = vbWhite
    End If
    End Select
    Next ctl
    Set ctl = Nothing
    End Sub

    but Steve's idea of using conditional formatting is better.

  6. #6
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Colour-coding all controls (XP)

    Conditional Formatting only allows for three Conditions, whereas I need about fifty. Andy.

  7. #7
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Colour-coding all controls (XP)

    Would this work as well for a stand-alone function, because it may be required on several forms?

    Do you know why the error message '..cannot use a control property unless the control has the focus..' occurs? Andy.

  8. #8
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Colour-coding all controls (XP)

    Replying to my own post.. But I think I was mistaken in my last comment. Conditional Formatting allows a condition (or three) for each control but I would have to create this for each of the fifty controls..

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Colour-coding all controls (XP)

    Fifty conditions? <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15>

    If you mean that you want to use fifty different colors, think again - users can't easily distinguish or interpret them.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Colour-coding all controls (XP)

    You could put the following in a standard module:

    Public Function HandleNulls()
    Dim ctl As Control
    For Each ctl In CodeContextObject.Controls
    Select Case ctl.ControlType
    Case acTextBox, acComboBox
    If IsNull(ctl) Then
    ctl.BackColor = vbYellow
    Else
    ctl.BackColor = vbWhite
    End If
    End Select
    Next ctl
    Set ctl = Nothing
    End Sub

    Note that Me has been replaced with CodeContextObject. You can't use Me in a standard module. You can call this function from the On Current event procedure of any form:

    Private Sub Form_Current()
    HandleNulls
    End Sub

    or you can enter <code>=HandleNulls()</code> directly into the On Current box in the Event tab of the Properties window for the form.

    Without knowing the code you tried it's impossible to say why you got the error.

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,613
    Thanks
    3
    Thanked 58 Times in 58 Posts

    Re: Colour-coding all controls (XP)

    It's quite straightforward - we do this with most forms we deploy, and we do it in code. We typically set things up so the control that has focus has a light yellow background, while other controls are either transparent or have a white background. Your situation would require a bit different Conditional Format, but the principal remains the same. We also allow users to set their own color schemes and fonts using a similar arrangement when the client requests that ability.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •