Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2010
    Posts
    12

    Unanswered: Two text boxes determine backcolor of one

    I have worked on this for about 5 hours and hit brick wall after brick wall.

    I need to have the fill color of a text box change to red if it is empty AND another text box has a specific choice from the drop down list. I have the same situation repeated on another pair of text boxes except that instead of a specific choice from a list it only matters if there is any data at all. The backcolor property is there to remind users that the field needs to be filled in.

    I tried two approaches

    First, I tried simply reading the values from the 2 text boxes and then changing the backcolor appropriately. Unfortunately reading the value or text from a textbox, combobox, or listbox control requires the control to have focus. Since this event fires based upon the AfterUpdate trigger the user's next control selection is overridden and they find themselves in whatever control had the last setfocus command. This is irritating and a deal breaker. I couldn't figure out a way using PreviousControl or any other vba code to get around this problem.

    Next I tried to get a recordset based upon the same data my form is based on. Since changing the backcolor property does not require focus I figured if I could get the value from the table I could base my logic on those values, change the backcolor, and the user's next control selection would not be affected. Unfortunately, I am unfamiliar with recordsets and cannot see to get a record set based on a table and multiple criteria. I can, in VBA, get a select SQL string made based upon one WHERE criteria. If I try to add a second one the SQL string returns absolutely nothing. If I use the same criteria in the Access Query building interface it runs as expected. Something in my VBA I guess.

    Feel free to ask any questions. I have class tonight until 9:30 pm EST but will check back when I get home. Thank you in advance.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Well, the statement that reading the value of a control requires focus is not accurate. It only requires focus if you use the .Text property; it does not if you use the default .Value property.

    That said, presuming you're working with 2000 or later, the simplest way to affect the back color of the textbox is probably Conditional Formatting (Format/Conditional Formatting or on the ribbon starting in 2007).
    Paul

  3. #3
    Join Date
    Oct 2010
    Posts
    12

    Okay, I will try that

    Hmmm, I will try that with the .value property. Somehow I missed that. Does conditional formatting allow one to set the backcolor of a control based upon another control's contents and the contents of the control being changed?

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Yes it does, using Expression Is.
    Paul

  5. #5
    Join Date
    Oct 2010
    Posts
    12

    Thank you

    Thank you very much - feel silly having not known that but then again, learning this stuff as I go. I will give these ideas and try.

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Nothing to feel silly about. None of us knows everything about the program. I still learn new things all the time. Post back if you get stuck. One thing I'll warn you about. When you enter an expression, Access loves to take field names and surround them with quotes, which will make it fail. You can avoid that by surrounding them with brackets:

    [FieldName] = Whatever
    Paul

  7. #7
    Join Date
    Oct 2010
    Posts
    12

    good looking out

    thank you for the hint - those kind of things can drive one crazy. I will post back with my progress sometime today or tomorrow.

  8. #8
    Join Date
    Oct 2010
    Posts
    12
    Well, your clarification of focus for .Text and .Value helped. I was able to implement a solution that worked. I will have to study how to access the underlying tables in Access to understand why I couldn't construct a SQL string that retrieved records based on 2 WHERE conditions. 1 WHERE condition worked fine, but adding a second goofed everything up. Anyhow, thank you very much, I learned something and made something work all at the same time.

    Eric

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Happy to help. Hard to say why the SQL failed without seeing it.
    Paul

Posting Permissions

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