"Retiring" data w/o losing relationships

[cranberryconsult]

Over time, users request that different options in drop-downs be added
and deleted.

When they ask for "old" data to be no longer available in a drop down,
what's the best way to handle that?

1. Just remove the data from the table that populates the drop-down?
But then what happens to all the old records that now have orphaned
fields?

OR

2. Change the query that populates the drop-down so that the "old" data
is queried out? But is that confusing for maintenance of the
application?

Is there a better way?

Thanks,

 

[Tony Toews]

Is there a better way?

Dunno about better but I do the following.

I add an inactive flag to the table in the dropdown. Then I sort the
combo box record source such that any inactive records are at the
bottom of the list. I also place a warning message in the After
Update event. I don't make it an error message as, possibly, they
might be legitimately changing some older data.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[cranberryconsult]

Thanks Tony,

I've done a variation of this (based on inactive dates), but the users
still want to not see the "old" data. They're afraid someone will
think that that data is still a valid event (not so much for picking it
in the app, just for doing it).

Thanks,
Laura

 

[Pat Hartman\(MVP\)]

You can't have it both ways. Perhaps the answer it to not even show the
records with the inactive dates.

BTW, I use the same method as Tony. In the application, I allow them to
change from an inactive code to an active one but not vice versa. Sorting
the values to the end of the list makes it a little harder for the user to
select an old code by accident. Programmatically removing the value from
the list has the unfortunate side effect of making the field empty. On the
other hand leaving the value in the list makes it show. I can't figure out
what you think might be an alternative solution. Deleting the old code
entirely doesn't solve the problem at all because the field still shows as
blank. It also violates RI so it is a non-option as far as I am concerned.

 

[Rick Brandt]

Thanks Tony,

I've done a variation of this (based on inactive dates), but the users
still want to not see the "old" data. They're afraid someone will
think that that data is still a valid event (not so much for picking
it in the app, just for doing it).

Thanks,
Laura

Have your RowSource criteria be...

WHERE Inactive = 0
OR FieldValue = Forms!FormName!ControlName.

The the list will show all active choices and ONE inactive choice but only if it
matches the value currently displayed in the form.

 

[Tony Toews]

Have your RowSource criteria be...

WHERE Inactive = 0
OR FieldValue = Forms!FormName!ControlName.

The the list will show all active choices and ONE inactive choice but only if it
matches the value currently displayed in the form.

Now that's an excellent idea. I never thought of that. Whooohoo, I'm
going to learn something new this month!

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Guest]

I have a bound ComboBox in a subform.

This works for the first record but I have to click on menu RECORDS -
Refresh when I change records.

I tried Requery On Current but that is no good as the screen flickers and I
run out of memory.

Any Suggestions?

 

[Rick Brandt]

I have a bound ComboBox in a subform.

This works for the first record but I have to click on menu RECORDS -
Refresh when I change records.

I tried Requery On Current but that is no good as the screen flickers
and I run out of memory.

Any Suggestions?

Me.ComboBoxName.Requery