Managing disables options in combo-box list: Format combo options?

[jgraav]

Hi there.

I have a form where users can fill options to entry an order. Also users can open entries to see form details. Options are in all cases related to subtables structured as {OptionID (#), OptionName (Text), OptionIsActive (Y/N)} so I can maintain masterdata very easily.

Back to form! Form combos are defined to only allow to see options currently active (Active=Y) and hidding Option IDs, so user is choosing names but DB is saving codes

Issue: When users open an old record in the form, if option was disables (Active=N) then those combos filled with non-current options appear empty (because there is nothing to show, but data is there in tables). This looks not very user friendly. I would like to show current/disables options, but How? Could users --when showing combo box options-- see current options in one format (i.e. standard) and historical options in another one (i.e. str̶i̶k̶e̶t̶h̶r̶o̶u̶g̶h, filled on red...) ??? I found many ways to colour combo when selected an incorrect option, but not to format each option when painting combo options.

Any alternative for solving the issue?

Thanks everyone for helping on this!

 

[isladogs]

Apologies if I'm missing the point here ....

This seems a very complicated way of overwriting the default option
Can't you just allow users to select all records as an option.

Failing that you could save all filters as they are created into a table and use that as your combo box values with the current value set as your default

As a side issue - how did you manage to 'strikethrough' that word in your post?
I've wanted to do that myself before now but can't see a way....

 

[jgraav]

Apologies if I'm missing the point here ....

This seems a very complicated way of overwriting the default option
Can't you just allow users to select all records as an option.

Failing that you could save all filters as they are created into a table and use that as your combo box values with the current value set as your default

As a side issue - how did you manage to 'strikethrough' that word in your post?
I've wanted to do that myself before now but can't see a way....

Thanks Ridders for quick replying.

Let me detail bit more: I want user be able to select any option when creating a new registry --> But allowing them to see for each option if it's current or unavailable. If I do not strikethrough / colour fill /... those disabled options, users could be confused when creating new ones, as they wouldn't know if each option is enabled or not. Filtering "only current" seems not to be good idea as users will not be able to check older entries.

Example: Let's imagine i'm talking about order entry. I want users to be able to create orders but just from current portfolio items, but also, I want users to see old orders of non-current products, all this using same form for entry/edit and for orders review.

Side issue: I just copied from google results for 'strikethrough'

 

[Minty]

You can't apply conditional formatting to the drop down list of a combo box.

You could create a small continuous subform, made to look like a scrollable list box that you could apply formatting to though. Might take a bit of effort to make it look good.

 

[isladogs]

Hmmm....

Perhaps it would be clearer if you could post a stripped down copy of your db removing anything confidential

SIDE ISSUE: I had tried writing strikethough in Word & crossed it through - when I pasted it, it lost the formatting...

EDIT:
Adding to Minty's comments, you can't apply formatting to parts of a standard listbox either.
It's a frequent request for MS to add that ....
There is an ActiveX version with additional functionality but I've never used it

 

[Uncle Gizmo]

Options are in all cases related to subtables structured as {OptionID (#), OptionName (Text), OptionIsActive (Y/N)} so I can maintain masterdata very easily.

How about creating an extra field in your combobox lookup table to identify the date that the option was disabled. Use the on current event of the form to run code to identify the date the record was created. Compare that to the date that you disabled the option. You will probably need to construct an SQL statement in VBA. I don't think a query would hack it. (Oh, and shove the SQL in to the rowsource of the combo)

{OptionID (#), OptionName (Text), OptionIsActive (Y/N), OptionDisabled (Date)}

 

[jgraav]

How about creating an extra field in your combobox lookup table to identify the date that the option was disabled. Use the on current event of the form to run code to identify the date the record was created. Compare that to the date that you disabled the option. You will probably need to construct an SQL statement in VBA. I don't think a query would hack it. (Oh, and shove the SQL in to the rowsource of the combo)

{OptionID (#), OptionName (Text), OptionIsActive (Y/N), OptionDisabled (Date)}

Hey everyone, thanks for ideas.

Yes, perhaps it is a big effort for a not mandatory function. I'll investigate on this last one to show users if option was disabled (and when), and doublecheck throught event if selected item has disabled timestamp.


I'll keep you up-to-date on this, thanks again for sharing your comments. All best.