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.
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).
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?
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:
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.