Question about deleting Microsoft Access rows

[juanmejia10]

We use forms in Microsoft Access which sometimes use dropdown options. These dropdown options are often pulling their options from other tables. We have one specific table called “Sources” which contains a list of sources that then populates some of these dropdowns. My question is whether or not our forms would be affected if we delete one of the rows in our “Sources” table. For example, if in the past we used a form that selected the “Source 1” dropdown option which pulled that option from the “Sources” table, but today we deleted that “Source 1” row in the “Sources” table, would anything happen to those records that had previously used “Source 1”? Would those records retain “Source 1” in that field, would they now become blank, or would there be something else in its place?

[craigrrr]

This actually depends on how the data was set up and used. It might be storing the full text of the dropdown, in which case you would be fine to delete the source entry, the record will still have the full text of ‘source1’ in the appropriate field and that won’t be effected by the deletion. If, however, the field is recording the row id of the source field table and it does a lookup of the id to the source text value to populate the record, then deleting the source row would break that lookup.
Easy way to tell;
Open the table that has the data from that form’s entries. Look at the column that contains the source selections field, and see whether it has the full text of the source or is just a number that refers back to the correct row of the source table. As long as full text is being stored in the record, you will be fine to delete the row from sources table and the historic data will remain intact.

[craigrrr]

If you find that it is storing an ID number and not the full text, in order to maintain historic lookups but still remove a selection from a dropdown list, just add an ‘active’ column to the source table. Set active = 1 for all of the ‘live’ rows, and set active = 0 for the ones you want to remove from the dropdown. Then adjust the dropdown to only select values from source table where active = 1.

[juanmejia10]

Thanks for the response! I went to check how the data was being stored and it was storing the full text of the dropdown, not a row id. I went ahead and deleted the source row and confirmed that none of the already existing forms were affected. Thanks again!