Changing Inherited Value in Dropdown List when List Changes

[Josh330]

Hi there. I'm an advanced beginner at Access, but if someone could provide direction to me on the following issue that would be great. I would like to change an inherited value in a combo box in a datasheet form when the data in the source table changes. For example, if the source table has an option of "ABC" and the user has already selected that in the datasheet form, then if the user changes the name of that option to "ABCD" then I would like the dropdown to automatically change from "ABC" to "ABCD". The way it works now is that the user needs to go back to dropdown and select "ABCD" manually to overrise the inherited value of "ABC". This is obviously time consuming when there are hundreds of records.

Any advice would be most appreciated?

Thanks.

Josh

[orange]

Please tell us more about user changing the values in some existing values in the table that supports your dropdown/combobox.
What is the process? Perhaps you could give us an example with a little more detail.

Is this a user whose role is to correct "misspellings" or similar?
Why is "ABC" not correct? Who knew it should be "ABCD"?

[Josh330]

The dropdown box is a list of business units. The user is responsible for inputting the names of each of the business units in a Form. The user is only one that knows the name of the business units. So the user may input three business units: "ABC", "Test Unit", "Good People". In a separate continuous Form the user then inputs more information and one of the fields is business unit, so the user selects from one of those three business units. The issue is when the user realizes that the business name is incorrect and for example wants to change "Good People" to "Very Good People". The user knows about the business units, not the developer. That change has to be done manually and which I would like to automate.

Thanks.

[Welshgasman]

If your DB was set up correctly, then you would be storing the ID of that record, not the value.
THEN when you change the name, that is reflected immediately.

I do it all the time for my Diabetes DB.

Now if you wanted to keep track of when the name changes for some strange reason, you would store the name, then you would see when it actually changed, so you would have records as ABC, and then records wih ABCD, but you wanted that change visible.

[Micron]

In a separate continuous Form

That seems to me that the edits are being done in a separate form then the user goes back to the original form and doesn't see the changed value. Then the event that closes the form should requery the first form?

[Josh330]

Thanks Welshgasman. I forgot that I needed to do this.

[ArviLaanemets]

Thanks Welshgasman. I forgot that I needed to do this.

It is never too late to correct this!

As start, make a copy of your database!

1. In design mode, rename your current combo source table. Like you had table tblUnits with field UnitName, and you change it to table tblUnitsOld;
2. Create a new table, with same name as your old one. Like tblUnits: UnitID, UnitName (UnitID is autonumeric field). Set UnitID as primary key;
3. Run an append query, which inserts all unit names from old units table into new one;
4. In all other tables, which have UnitName field, add a new field for UnitID (either UnitID or UnitIDpK, in Long Number format);
5. Update all those tables with UnitID values matching with unit names there, read from new units table. In case unit names there were used for indexing, edit those indexes, replacing unit name fields with unit ID fields;
6. In all tables where unit id is now a foreign key, delete the field, which contains unit names;
7. In all forms, where you had combos for selecting unit names, edit their rowsource, adding unit id field as 1st one into query string, and making only unit name field visible one;
8. Delete old units table.

[Josh330]

Thank you so much.