Share via

MS Access Conditional Formatting

Anonymous
2023-03-03T16:47:19+00:00

Hi,

I have multiple forms with multiple combo boxes. Each combo box has a drop-down list either from a table SQL selection or cascading from a query.

I would like the background color of the box to change if the user enters a value other than one contained in the drop-down list.

I've made some attempts using conditional formatting and On Change events VBA, but haven't gotten it to work.

Alternatively, is this issue better approached from a validation perspective?

Any suggestions/help would be most appreciated!

Thanks!

Kevin W.

Microsoft 365 and Office | Access | Other | Other

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2023-03-03T22:50:34+00:00

    The DLookup function without any criterion will always return a value from the first row in the query's sort order, so comparing it with the selected item, as you have done, will return False for all but one selection in the combo box.  Study my solution again:

    1.  The DLookup function's domain should, in your case be exactly the same query as the combo box's RowSource.  If this is an SQL statement save it as a query and reference it by name in the expression.

    2.  For the expression to return True, and the conditional formatting to be applied, the DLookup function should return a Null, i.e. there should be no match to its criterion.  The IsNull function is used to determine if it returns Null.

    3.  The criterion for the DLookup function should be that a value in the Column which returns the control's list is equal to the value of the control, [cboCity] in my example.  If a match is found the function will return that value, and consequently a Null will not be returned.  The IsNull function will thus return False, and the conditional formatting will not be applied.

    1 person found this answer helpful.
    0 comments

6 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-03-06T21:01:09+00:00

    Thanks for the additional explanation. I have a better understanding, and it works!

    Your help is much appreciated!

    0 comments
  2. ScottGem 68,810 Reputation points Volunteer Moderator
    2023-03-03T22:58:02+00:00

    I'm a little confused here. If you set the Limit To List property of a combo to Yes, the combo will NOT accept an entry that is not in the List. The user gets an error message to select an item in the list. You can then use the NotInList event to either add the entered value to the list or display a message that the item isn't in the list.

    I really don't see a value in allowing the user to enter an item not in the list and highlighting the control. Either you want to allow the user to enter another value and, therefore, add it to the list or reject the entry.

    0 comments
  3. Anonymous
    2023-03-03T18:30:51+00:00

    Hi Ken,

    Thanks for your reply. I attempted some of your suggestion, and the situation has improved. However, it's still not the whole way there.

    I'll give a more concrete example of my test case.

    This form is used to catalog attributes of ceramic vessels. There's combo box for Vessel Shape, the drop-down list comes from this SQL:

    SELECT Master_List.Dropdown_Text
    FROM Master_List
    WHERE (((Master_List.Dropdown_Type)="Ceramic Vessel Shape") AND ((Master_List.Is_Active)=True))
    ORDER BY Master_List.Dropdown_Text;

    To simplify your suggested approach, I created a query (CVS_Q) that returns those same values.

    In the Conditional Formatting window, I entered this expression:

    [Vessel Shape]<>(DLookUp("Dropdown_Text","CVS_Q"))

    Again, the intention is to have alert formatting if there is an entry that is different from a dropdown option.

    Back in form view, if the first item in the dropdown is chosen, everything is fine. However, if any of the other dropdown options are chosen, the alert formatting kicks in.

    Is this a limitation of DLookUp? Does it return only a single value rather than compare against all entries? Is there another function that could accomplish that?

    Thanks again!

    Kevin

    0 comments
  4. Anonymous
    2023-03-03T17:33:54+00:00

    Let's assume you have a combo box cboCity with a RowSource property of:

    SELECT City

    FROM Cities

    ORDER BY City;

    The conditional formatting expression would be:

    IsNull(DLookUp("City","Cities","City =""" & [cboCity] & """"))

    If the control's NotInList property is False (No) this will allow the user to enter an unlisted value, but highlight it.  If you want to prevent a user entering a value not in the list set the control's NotInList property to True (Yes).

    0 comments