Unanswered: Access 2003 value of two text boxes determines back color or one text box
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.