Parent table restricted: refetching

[davidv43]

I must not understand sets and there use on forms which contain the entire database. Please see the attached database. After I enter a record on the first page and second page, I then want to enter a second record in the "Injury" page for the same patient. I select a new record with the navigation buttons at the top of the Injury tab. However, I get an error message "Parent table restricted: refetching". I've checked all the obvious things (I think) like restrictions on the forms, set restrictions, etc.

I know my set structure is convuluted and maybe not right. My goal was to make sure that when I enter a record in the Injury table, it remains linked to the Patient Master table, and so on.

I know this is a complicated database. I am open to other ways to present data to the user. The tabbed form seems a very concise way to do it.

Thank you for taking your time to look at it. Happy Thanksgiving!

Dave

[MikeC]

Re: Parent table restricted: refetching

Hi David,
I took a look but did not get far....seems to be a bit of corruption and so only default forms open (possibly due to your reducing your database down for the messageboard). So I could not reproduce your problem--perhaps also the reason why no one responded.

I did notice you have referential integrity on for every linkage--I have noticed odd problems when trying to use this even on a few linkages. I recommend taking this aspect off at least for diagnostics sake. You also have two date fields required that the main form apparently has no access to mfa->date and smfa->date....the non-access possibly due to default forms??? no clue.

[davidv43]

Re: Parent table restricted: refetching

Mike,

thanks! I changed the set definition to include all parent records instead of include only records which have a child and that made it work.

As a concept, I thought referential integrity was in part to make sure that records from different tables didn't get disconnected (broken or missing key). How do I make sure that the key in the child form is the same as the key in the parent form if, for instance, someone changes the parent key without referential integrity selected.

Is the referential integrity problem a bug? If so, I'll report it to Alpha.

Again, thank you for your time in looking at my database.

Dave

[davidv43]

Re: Parent table restricted: refetching

I'll try sending the files again ...

[MikeC]

Re: Parent table restricted: refetching

David,

was in part to make sure that records from different tables didn't get disconnected (broken or missing key).

From what I understand, not exactly. It is to make sure that if a parent has one or more children, then when the parent is deleted the children are also....IOW if there is no parent, there cannot be orphans of that parent. As soon as I had odd problems I took off referential integrity which fixed the problems and never went back to using it---simply created routines to make sure if any children existed, that they were deleted prior to the parent deletion.

I have not checked the new file you attached yet---didn't you say that it is working now?

[davidv43]

Re: Parent table restricted: refetching

Thanks again, Mike. I included the file again so our friends at Alpha could look at it and see if this is a bug or a misunderstanding on my part. Referential integrity is a big deal for me.

On the other hand, I think probably what I should do is change my primary key to something users cant modify. Otherwise, if they change the value of the primary key, I will have to programatically change the foreign keys in the child records, if i turn off RI.

dave

[MikeC]

Re: Parent table restricted: refetching

David,
You should never (IMO) use any key that can be changed by a user!!! For what purpose? A key is a linkage field and should never (IMO) be changed as that then negates its purpose. Ref. Integrity does nothing I am aware of in doing any changes to a key---it just makes certain that if a parent is deleted, then all children of that parent (which is determined by the key) will also be deleted. A primary key is one that should have one purpose and can think of no good reason to use it for another. Add a field to do what is wanted instead.

[Stan Mathews]

Re: Parent table restricted: refetching

Ref. Integrity does nothing I am aware of in doing any changes to a key---

The cascade changes does...

"Cascade changes/deletes" - You are not permitted to change the child table�s linking field value. If you make changes to the value of the linking field in the parent table, matching records in the child table are updated to reflect the change.

[MikeC]

Re: Parent table restricted: refetching

Thanks Stan....but

You are not permitted to change the child table’s linking field value. If you make changes to the value of the linking field

Isn't the first unbolded statement the opposite of the one in bold??

OOPS! I now see the difference!! But then does this make Ref Integrity not possible for when child records do actually change parents in some real life scenarios??

[davidv43]

Re: Parent table restricted: refetching

I have now changed my database to have only a primary key as the link between parent and child and it is an auto-incremented number in the parent. It will never be changed once assigned. The patient ID number (medical record number) remains in the database, but is no longer the primary key.

My original question remains ... why does linking to a child using the option to include parent record "only if child record exists" cause a "Parent table restricted: refetching" error?

Dave

[davidv43]

Re: Parent table restricted: refetching

Maybe I have figured it out ...

I have already retreived a record, say primary key = "00001". When I try to add a new child record when the set defines the relationship as "include parent record only when child exists", the new record has a foreign key value which is null until the record is created. Alpha tries to enforce the set definition, but there is no parent with a null primary key so it triggers the error.

If the set definition were not enforced until after the primary key were duplicated in the new child record, this would not occur.

Thoughts?

dave

[Mike Wilson]

Re: Parent table restricted: refetching

David,
I would recommend something different. I would recommend that you remove referential integrity and place a script in the field rules for the MRN field which is your primary and foriegn key that checks to see if the form is in 'enter' mode and allow the field value entry, but if in 'change' mode to disallow the field to be changed. And then have a password protected administrative function that allows an administrator to change a MRN from all tables with MRN as a field (listed in a table). Attached is an example.

[davidv43]

Re: Parent table restricted: refetching

Thanks, Mike. Havent had time to understand your programming, but the concept makes sense.

If I don't use referential integrity on a set, does the program automatically set the value of a foreign key to the key value in the parent if I use a subform? That was really the biggest reason I was using referential integrity in the first place.

Thanks,
Dave

[Mike Wilson]

Re: Parent table restricted: refetching

David,
I don't use subforms, but the parent-child integrity should always be active in a set to insert the link field value in the child when adding new records. My understanding of referential integrity relates to what happens in the downfield children when you delete records. I just tried it and it seems to work with the limited trials I made. Maybe others who use subforms might chime in on this question.

[davidv43]

Re: Parent table restricted: refetching

Mike, I have a database with more than 500 fields from 7 different tables. It's obviously impractical to design a single form which could handle all that data and the tabbed interface seems to cause more problems than it solves. What design would you use to accommodate all this data entry?

You said you don't use subforms. Is there a better alternative?
Thanks,

dave