Does not update

[SteveI]

Hi,

I want the attached database to update automatically. I.e. when I change the description of the ingredient from Sugar to Brown Sugar In the Ingredient Table - I want all the recipes that contain sugar to change descriptions to Brown Sugar in the Recipe/RecipeIngredient Table.

Is this possible. Find and Replace is not an option. Update Query is not an Option - as it will be a user database and writing a query is too sophisticated. Find and replace is too risky and basic.

Any suggestions please..

Contains code to update description field as code is entered.

 

[Uncle Gizmo]

You need to put your ingredients in an "ingredient lookup table". Anytime you need to change an ingredient description change it in the lookup table and the change will be reflected throughout your database.

 

[SteveI]

thanks but does the subform in the main form not lookup the ingredients in the ingredients table already. or is the lookup table something totally new - can you be more specific.

 

[Uncle Gizmo]

I avoid looking at sample databases.

If you are already using a lookup table, then what is the problem? Why don't you just change the description in the lookup table?

 

[Uncle Gizmo]

I wonder if you have a single text field lookup table? Selecting items in your combo box would then put text in your other table. If that's the case then your problem is a design issue.

You should change your design. Use a lookup table with a long integer ID field, (usually an auto number field) and then a text description field.

Now in your main table, instead of storing the text you will store the ID number.

Now any changes made to the description in the lookup table will be reflected throughout your database.

 

[RainLover]

Steve

Before I got past your initial post I was thinking the same as the man from Uncle Gizmo.

 

[gemma-the-husky]

I agree with uncle gizmo - store a numeric code, not the text value

however - another alternative is to define all your relations (particularly those involving the ingredient description) with relational integrity, and set cascading updates. then when you change the ingredient name, all the matching items will change as well.

copy the dbs first, just in case