Disappearing Data in Combo Boxes

[Fred Worthington]

Greetings:

I have a subform based on a table which uses a Combo Box as the "display
control" for the data field. The "row source" is another table which
contains a list of the items that users can store in the table linked to the
subform. The data entered must match one of the listed choices to prevent
discrepancies. My problem is this: the choices listed in the "source
table" must be edited several times a year (some items removed and others
added). This is easy enough to do, but when an item is removed from the
source table, all references to that item in the table to which the subform
is linked disappear. How can I get those items to remain (as data) after
they have been removed from the source table. Even though the item is no
longer an option for current records, I still need the old data for
historical evaluation and analysis.

Thanks . . . Fred

 

[Dorian]

Sounds like you have 'cascading deletes' set on in the
table relationships designer.
You can unset it but then you lose referential integrity.

 

[Fred Worthington]

Dorian,

Thanks for your response. Actually, I had not set up the table
relationships when I encountered my problem. Thinking somewhat in the
reverse of your idea, I tried setting up the relationships (one-to-many with
referential integrity to cascade updates and deletes), thinking that might
solve my problem. However, that had no effect, and now I have removed
referential integrity again to no avail. If the item is not listed in the
source table, it will not appear in the table linked to the subform. If you
have any other ideas, I would be most appreciative.

Thanks . . . Fred