Remove item from list but not from previous records

[ashows]

I have a database that uses several combo boxes which allow the users to select an item from the combo box list. The list entries are stored in a separate "lookup" table. If a desired entry is not in the list, the Not In List event is used to allow the users to add the new entry to the list.

For example, one of the combo boxes contains Locations where workshops are conducted. If a particular Location will not be used any more, I would like to remove it from the drop-down list so the users won't select that option. However, I would like for that Location to remain in the records of workshops that have already been held at that location.

I created a form that displays the workshop Locations in the "lookup" table and allows the user to delete a record. Unfortunately it not only deleted the Location entry from the combo box list (table), but also left a blank field in the records that had previously contained that Location. Is there a way to remove items in the drop-down list and still have that Location appear in the records that were entered previously?

 

[kallak]

Hi ashows,

I'm not sure of your level of expertise, so I might be giving you more info than you need--if so, I apologize--just ignore things you already know--

Have you considered doing the following:

1) add another field (yes/no type) to your lookup table of locations; call it "Active" or something like that

2) set the default value in the field properties section to "yes" (the way I understand your post, if a user is adding a new location it's because it's already active or will be very soon)--the default of "yes" will ensure that a location won't be added by a user, who might actually forget to flag it as valid (a user failing to set a newly added location to "yes" would cause problems with step 4 below)

3) be sure to go back to your locations lookup table and either manually or via an update query set all valid locations to "yes" for the new "Active" field (being sure to leave the ones you wanted to delete as no); sometimes yes/no boxes can be null (they will be greyed out instead of white or checked)--if they look like they might be null, make sure to change them to "no" manually or via update query as well

4) change your combo box's row source to select the locations in your locations lookup table--BUT only the ones that are flagged "yes" in the new "Active" field you set up (unless the combo box is already based on a query, use the query builder on the row source of the property sheet for the combo box--answer "yes" if Access asks you if you want to base the row source on a query--then simply place the new "Active" field in the query grid, uncheck the show box and type "yes" in the criteria row underneath it); note that if your combo box is already based on a query, simply edit that query to include the new "Active" row, uncheck the show box and key "yes" into the criteria row underneath it, save, close and continue below

5) change all forms that have the location field on them to include a check box bound to the new "Active" field (be sure to put this check box next to every occurrence of the location field no matter what form it is in--just to keep users aware that there are invalid locations)

If you do the above, your current referential integrity settings (which caused all fields containing the no-longer-valid-so-it-was-deleted location to be left empty) can stay as they are (and as they probably should be)--BUT you will no longer have to deal with invalid locations showing up in your combo box (this, of course, assumes that you or the users will do regular house-keeping to make sure that the "Active" flag is set to "no" for locations as they become invalid.

I believe the only other alternative you would have is to go into the Relationships window and edit the relationship between that lookup table and your other tables to remove the "Cascade Delete" (and, if you wish, the "Cascade Update" options)--however, I don't recommend this approach as it could introduce other problems that would be much harder to compensate for than just doing steps 1 - 5 above as I've outlined them.

Let me know if this helps--and good luck!!!
Keith Lunsford
klunsford@brightdsl.net