Relationship not working..but only sometimes

[GPM4663]

Hi everyone,
I have a relationship between two tables that doesn't work every now and again i.e. the primary key from the 1st table doesnt' get put in as the secondary key in the 2nd table automatically. The strange thing is that it only does it every now and again. I have check the relationships, the form/subform link but everything looks normal. I'd appreciate any help anyone could give me on this one.

Many thanks,

GPM

 

[Crowley16]

well, maybe you've not found the right one? Tables can be very tempromental sometimes...

Anyway, seriously...

how are these fields populated? With a form? With SQL? With Code?
can we have a look at the sql/code?
do you have referential integrity between the 2 fields?

--------------------
Procrastinate Now!

 

[GPM4663]

Hi Crowley16,
Thanks for that, I suppose we need to ask ourselves "What do tables really want out of a relationship?......" - we could do this all day I'm sure!!!!

The fields are populated using a simple form/subform combination. To give you a bit more detail there are actually 3 tables. The PK (primary key) of table 1 is the SK of table 2 and the PK of table 2 is the SK of table 3. So actually its a form/subform/subsubform situation. The really stange thing is that SK in table 3 always gets populated and the SK in table 2 only sometimes gets populated. So you could enter in all the details in the form, subform and subsubform and when you go back to it is has dissappeared because obviously no link key can be found between the form and subform. Referential integrity is switched on between all the fields.

It's driving me nuts so any help would really be appreciated.

thanks,

GPM

 

[Crowley16]

I dunno, maybe some chairs...

hmm, so basically you're letting the form itself create the new records?

then it's difficult to diagnose exactly what is going wrong, there could be any number of things causing the break. If there's a pattern to the errors you might be able to work out exactly what is causing it, but otherwise, maybe you could try to put in some sort of error checking on the sub forms or in the main form...

Access can be "tempromental" at times, so I try not to rely too heavily on thegenerated insert records stuff...

--------------------
Procrastinate Now!

 

[willir]

Have you enforced referential integrity??

From the menu, "Tools" -> "Relationships"
Add tables to the GUI interface.
Click and drag the primary key in one table to the foreign key in the other table.
A popup window will allow you to edit the relationship. Enable "Enforce referential integrity".

This will all the system to "insist" that the parent (one-side) record exists before creating the child (many-side).

Richard

 

[GPM4663]

Hi Willir,
Yes I have which is the strangest bit because it shouldn't let me work with the SubSubfrm if the records in the subform haven't been properly created but it does?????

Any other suggestions?

Does it have any impact that the database is split to a frontend/backend! If so where should the relationship be set up?

Thanks again,

GPM

 

[Crowley16]

erm, I'd have thought it'd be better to have the relationships set up in the backend, and then to import the relationships to the front end...

--------------------
Procrastinate Now!

 

[willir]

In my experience, the relationships only need to be created on the backend.

I have seen some posts where a tipster may recommend defining the relationships on the front end -- I am not sure I agree with this for several reasons -- a) you can not enforce referential integrity with the relationship defined on the front end; b) if relationships are defined on the front end, it means you have to ensure the relationship is defined for all deployed front ends.

Is this your problem??

Try the defining the relationships on the backend only, enforce referential integrity and then recreate your form / subform. Then try creating a child record without a parent -- I suspect you will not be able to save the record.

Richard