Table with choices for subform/related records

[jk42]

Hi!

So, maybe this is bad design (please tell me if it is) but I have a table that has the list of "Project design" choices, and I enter in there the choices that a project can be. I then have that table related to a junction table that has the "Project design choices" linked to the "project code." Anyway, I THOUGHT that one of the bonuses to using Access was that if you see something spelled wrong, you could fix it in one table and it would fix it everywhere. However, when I see that I spelled something wrong in "TBLProjectDesignChoices" and I want to fix it, it tells me that I can't because it contains related records.... ok so, if this is bad design.. I may have to leave it because I have spent countless hours doing data entry for this.. but I would like to know going forward.

Thanks for any help!

 

[misscrf]

Ok, I got a little confused with your table descriptions. Let me give you an example how that benefit of Access works, and then you can see if your tables are set up in a desirable way, or if you might want to reorganize them a bit.

Each table should have an ID set as the primary key, and auto-incrementing. (in sql that is identity).

No value should be repeating information in a table. For instance if I have a database of my CD collection, I might have the following table:

tblCDCollection
ID
CDName
Artist
YearReleased
Genre

Now genre (and maybe even artist) will be the same for many of the records. To make sure I don't have those misspellings, I will make the table like this:

tblCDCollection
ID
CDName
FKArtist
YearReleased
FKGenre

The artist and genre are now foreign keys. They will link to the two following tables:

tblArtist
ID
ArtistNameGroup

tblGenre
ID
Genre

In the cd collection table, those FK fields will link to the ID in the lookup table. Not the text value. That way, you can update the text value for a genre or artist and it will link to all cd collection records.

On forms, you will use a combo box, with the bound column to 1, the row width set to 0";1" so you don't see the ID, but you see the name. The row/source of the combo will be a query of the ID and field in the lookup table and the control source of the combo will be the FK field you want to store that lookup ID into.

I suggest also limiting to values in the list and check out the NotInList event in the combo control. I always make a little popup form, so if someone needs to add a new genre or artist that isnt in the list, a form comes up and they can add it, or manage that list.

Hope this helps!

 

[jk42]

Oh ok. That does make sense... there are several tables where I used the Text as the foreign key, and if I'm understanding this correctly, this is where my problem is? Is it too late for me to change the design and have a PK that's auto number? I had read some articles and had decided that it would be easier for me to just have the PK be the text word, but if that's what's causing my problem, I don't want to do that! I have a few typo's

Thanks so much for your help!

 

[misscrf]

It won't be the easiest to clean up, but it can be done. In the table where you have the text key, is the field linked through the table design to that text value in the other table? If so, that field should have a row source. If so, you can adjust the row source to the id column. Then you can clean up the names and consolidate any that were really the same, to the right id.

If that is not how it is, then make a new column for the id, run an update query to update the ID of the right value based on the text value of the lookup table =ing the text value in the big table.

Before running any update queries, do yourself a favor and back up those tables! I've run update queries backwards on accident before, and it wipes everything out. lol not desired!

 

[JHB]

... However, when I see that I spelled something wrong in "TBLProjectDesignChoices" and I want to fix it, it tells me that I can't because it contains related records....

How are your relationship between tables set up?
With "Enforce Refenrential Integrity" and "Cascade Update Related Fields"?

 

[jk42]

-Yes, it is linked through both of the text keys. So maybe I can try that.


They're enforce referential integrity, but not cascade update related fields. I don't really know what that does. Would that help?

 

[JHB]

- Would that help?

Yes that could help.

Here is a description of it:

Cascade Updates and Cascade Deletes

For relationships in which referential integrity is enforced, you can specify whether you want Microsoft Access to automatically cascade update and cascade delete related records. If you set these options, delete and update operations that would normally be prevented by referential integrity rules are allowed. When you delete records or change primary key values in a primary table, Microsoft Access makes necessary changes to related tables to preserve referential integrity.

If you click the Cascade Update Related Fields check box when you are defining a relationship, any time you change the primary key of a record in the primary table, Microsoft Access automatically updates the primary key to the new value in all related records. For example, if you change a customer's ID in the Customers table, the CustomerID field in the Orders table is automatically updated for every one of that customer's orders so that the relationship is not broken. Microsoft Access cascades updates without displaying any message.

 

[jk42]

Thanks, everyone. So if I go change all the relationships to cascade update, that will help?

Thanks!

 

[jk42]

Just changed it, and it worked. Thanks, again!