Retiring values from Combo Boxes

[Guest]

I am currently using combo boxes based on a query that displays a different
column than the one that is bound to the table column.

I want to allow the users to “retire†a value in the look-up table, so I
added a column named Active with a data type of Yes/No. I then added
criteria to the combo recordset to like “WHERE Active = YESâ€

This approach prevents using the retired values, but has an un-intended
consequence of displaying all existing records with a retired value as null.

Any suggestions on how to “retire†values from combo boxes while allowing
the users to still see the retired values in existing records?

Thanks

 

[Eric Schittlipz]

I am currently using combo boxes based on a query that displays a different
column than the one that is bound to the table column.

I want to allow the users to "retire" a value in the look-up table, so I
added a column named Active with a data type of Yes/No. I then added
criteria to the combo recordset to like "WHERE Active = YES"

This approach prevents using the retired values, but has an un-intended
consequence of displaying all existing records with a retired value as
null.

Any suggestions on how to "retire" values from combo boxes while allowing
the users to still see the retired values in existing records?

Thanks

Use a second part of the where clause, eg tblOrders has a field SalesPerson
which is an ID (long integer) from the field tblStaff.StaffID, to show who
got the order.

.... & " WHERE tblStaff.Active=True Or tblStaff.ID=" & Me!SalesPerson

But remember you will have to re-query the combobox each time you move to
another record (in the form's OnCurrent event). Another option would be to
not allow edits directly - have a change button which pops up a list of
available values.