ACCESS FEED: Re: [MS_AccessPros] Design Question

Kamis, 29 Oktober 2015

Re: [MS_AccessPros] Design Question

John,

This is what I am looking for.

Please point in the right direction to find the Relationships window between the two tables.

Thanks

Sarah

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Sarah-

If you define Referential Integrity using the Relationships window between the two tables, you can force any change in Product Category to be "cascaded" to all related Product rows. If you don't turn on Cascade Update, then any attempt to change Product Category that would leave "orphan" rows in the Product table will be disallowed by Access. Note that all Product rows must have a valid Product Category value for you to be able to define the relationship.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Oct 29, 2015, at 4:42 PM, sarahk@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I have 2 tables:
Product Category
Product

The Product Categories table primary key is the product category. The Product table has a field Product Category that refers to the Product Category table.
If a user changes or deletes the primary key in the Product Categories table, this would affect the Product table, because the product category would no longer be valid.

My question is:
is there a way in Access in the design of the table to not allow or to warn the user not do this?. Or must I write VBA code to check all the tables that are using Product Category?.

This is just one small example, I have State and Country tables that are used in many other tables. If the user changes the primary key in the table, I would have to remember all the tables that refer to the State and Country tables and check all of them to see if the code is any of the entries in those tables and then warn the user no to change/delete the entry.

Is there a way to do this 'automatically'?

Sarah