Best way to 'Hide' unused Option?

[NewShoes]

Hey all,

I'm trying to figure out the best way to hide an unused option in a combobox. Currently I have a tick box that the user can tick if the option is no longer needed. I then have a query that filters out the ticked boxes as the coboboxes record source. However, when this is done, the records that have previously used that option appear blank. This obviously makes sense but I was wondering if there was a way to keep the previous records intact?

I might be going about this the wrong way, so any advice would be great!
Thanks,
-NS

 

[missinglinq]

Apparently your Combobox is bound to a Field in your underlying Table. The simplest way to handle this would be to not have the Combobox bound to such a Field. Instead, use the AfterUpdate event of the Combobox to assign the selection to a Textbox on the Form that is bound to this underlying Field.

Private Sub ComboboxName_AfterUpdate()
 Me.TextboxName = Me.ComboboxName
End Sub

Then continue using your checkbox to remove the option from the Combobox. The bound Textbox will retain its value and the record will be included in your record set.

Linq ;0)>

 

[NewShoes]

Thanks Linq. I should have said that the combobox is used in a datasheet view (in a subform). Does this make a difference to your solution working?

Thanks,
-NS

 

[missinglinq]

Datasheet View makes many things more difficult and is much less flexible! The problem is that the Value of the Unbound Combobox in the Current Record will be displayed in the Combobox on all Records. Not very appealing!

You can circumvent this by returning the cbo's selection to Null after assigning the Value to the new, bound Textbox.

Private Sub ComboboxName_AfterUpdate()
 Me.TextboxName = Me.ComboboxName
 Me.ComboboxName = Null
End Sub

The only other solution I can think of is very convoluted and not one I'd use, but you could

1. Add a 'Removed Options' Table to your db
2. When the Checkbox is ticked, use an Append Query to add the selection to that Table
3. In the BeforeUpdate event of the Combobox, use Dcount() to see if the selection matches a record in the 'Removed Options' Table.
4. If the selection does match a record in that Table, pop a Messagebox informing the user that that selection is no longer available and using Cancel = True to cancels the update of the Field and return the focus to the Combobox

As I said, not an approach I'd use. The selections would still appear in the Combobox and could be frustrating to users attempting to use a 'removed' selection.

Linq ;0)>