Losing associated records - relationships?

[nedstar1]

Hi friends,

I have a new and extensive database to track orders for my company. The main table is called tblJobs, and I have two other main tables - tblItems and tblJobVarious - that contain info about each job - ie. manufacturer, model number, option number, etc.

The tables are related in this way:

tblItems contains a JobNumber field that is tied to tblJobs.JobID, the primary key. The relationship is descrtibed as one to many from tblJobs.JobID to tblItems.JobNumber.

tblJobVarious is related in a one to one from tblJobVariousd.JobID to tblJobs.JobID. (keep your flames to yourself - too many fields for one table)

I used these tables to build a form/subform for easy dsata viewing/entry - works fine with one exception - on occasion, the record in tblJobs will lose its associated record in tblJobVarious.

At present, out of about 108 orders, 13 now have 'orphaned' records in tblJobVarious. That is to say, I can open up the tblJobVarious and see that the data is still in the table, but it is no longer associated with the correct record in TblJobs.

So, a couple of questions for the sage wizards who lurk these forums:
Why is this happening? Is there a way I can prevent users from creating a new record in tblJobVarious while doing Data entry on the form?
How do I re-associate these orphaned records?
How do I prevent this from re-occuring?

I beleive that I have done something worng, either in my form design or in my relationships, but I don't know what.

Any help is greatly appreciated.

TIA
Regards,
Nedstar1

 

[willir]

NedStar1

When you create your relationships, check / enable "Enforce Referential Integrity". You can edit this current relationship by...
- Open Relationship tool, From menu, ""Tools" -> "Relationships"
- Right click on relationship link, and select "Edit Relationship"

Note: Access will not allow you to "Enforce Referential Integrity" until the data is compliant.

Enforcing referential integrity will...
- insist on the foreign key to be present, specifically, you have to have a parent before you can create a child
- you can not delete a parent record until you deleted all the children.

What happened?
Hard to say -- parent record was deleted is a possibility, especially during testing. (I say this since you use a Form + Subform which means that primary key / foreign key should work when linking fields.)

If you want to re-associate the records, you need to find the parent record, get the primary key, and add it to the foreign key in the orphan record.

Richard

 

[nedstar1]

Hi willir,

Thanks for the speedy reply.

I have a few specific questions: My tblItems contains JobNumber which matches up to tblJobs.JobNumber - is this sufficient to create the foreign key, or do I need to actively do this while in the Relationships window? (think I know how, but not sure)

My tblJobVarious contains JobID, which is supposed to tie back to tblJobs.JobID, which is the tblJobs PK. The same question applies.

For the repairs needed - I basically need to go into tblJobVarious and what, delete the extra records? Do I need to set the FK to be a lookup from tblJobs?

If you are amenable, my DB when zipped is only a few 100 K. Any interest in looking it over?

Thanks, and TIA,

Regards,
Nedstar1