Highlight selected records in dropdown box (1 Viewer)

[Gismo]

Hi all,

I have a form where I select the Supplier
In the product dropdown box, the product for this preferred supplier is listed on the top of the drop down box

When the product dropdown list is selected and the supplier selected is the preferred supplier, I would like to highlight all the products for the supplier

Is this possible?

 

[CJ_London]

Assuming you mean products listed in a form then I would think so using conditional formatting

 

[MajP]

That is not clear to me what you want to do. If I select "National Water", I assume you want to limit the selections in the list to products made by national water. I assume national water is not in the pepper and bacon business. That would be a "Cascading Combobox"

 

[June7]

AFAIK, cannot color rows of native Access listbox or combobox list.
Review https://www.access-programmers.co.u...box-color-row-based-on-value-in-field.226478/

Apply filter to combobox or listbox to show only selected supplier's products. That is called 'cascading' or 'conditional' or 'dependent' combobox/listbox. Very common topic. Review https://stackoverflow.com/questions...ous-form-the-second-combo-doesnt-show-its-val

 

[Gismo]

That is not clear to me what you want to do. If I select "National Water", I assume you want to limit the selections in the list to products made by national water. I assume national water is not in the pepper and bacon business. That would be a "Cascading Combobox"

No I dont want to limit the product list
If a product has a preferred supplier and the supplier is selected, the products for this supplier would be sorted to the top of the combo box
only the products with the selected supplier must be highlighted
The balance of the product available for any supplier will be below but not highlighted

 

[Josef P.]

Color highlighting is not possible with an Access.Combobox.
You could create an additional column and insert a text marker in it or edit the text itself.

Water (...         | *
Advertising - ...  | *
Advertising - ...  |

or

* Water (...
* Advertising - ...
Advertising - ...

 

[arnelgp]

You could create an additional column and insert a text marker in it or edit the text itself.

or the Supplier Name itself.

 

[CJ_London]

Or since the rowsource will be a union query include a third select that puts a line between the other two selects

 

[GPGeorge]

Hi all,

I have a form where I select the Supplier
In the product dropdown box, the product for this preferred supplier is listed on the top of the drop down box

When the product dropdown list is selected and the supplier selected is the preferred supplier, I would like to highlight all the products for the supplier

Is this possible?

View attachment 106553

After reading through the discussion, I think I have a suggested solution.

You can use the idea presented by Josef P, in conjunction with an Immediate If in the query providing records to the second combo box, the one listing all Supplier as called out in your screenshot..

This is air code since I can't see that query:

SELECT S.SupplierID, SupplierName: Iif(S.SupplierID = TempVars!lngSupplierID, "*", "") & S.SupplierName
FROM tblSupplier AS S

Note that the conditional either puts an asterisk in front of the supplier name or not, depending on the value of a tempvar.

The tempvar is set in the combo box, using its AfterUpdate event.

Private Sub cboSupplier()

    Tempvars.Add "lngSupplierID", Nz(Me.cboSupplier.Column(0),0)
    Me.cboYourOtherComboBoxNameGoesHere.Requery
End Sub

Each time you change a value in the combo box on the header, it updates the tempvar and then requeries the other combo box to highlight the selected supplier.

 

[Pat Hartman]

Here's a listbox and combo

sample I picked up somewhere. The zip includes a database and a word doc.

 

[CJ_London]

doubt that will work for the OP's requirements - the colour is based of the format properties. For numbers (which includes dates and booleans) they are set depending on whether the value is positive, negative, zero or null. For strings the options are only not null or null. Either way won't work in a combo anyway.

 

[Pat Hartman]

We are trying to do something that Access does NOT directly support.
The sample I posted also works in a combo.

A cascading combo seems to be the most rational solution given our lack of ability to easily control color in this context.