Referential Integrity ? 1

[sagamw]

I have a form (data from table1) and a subform (data from table2) in it. The tables have a one-to-many relationship.

To my surprise, MS Access lets you enter data into a sub-form before the master record is created.

So I searched and I found that the proper "solution" to my problem is to check "Enforce Referential Integrity"

Here 's my main source of reading

http://grok.lsu.edu/article.aspx?articleid=4604

Unfortunately :-( It doesn't work. i.e. although I have a checkmark in "Enforce Referential Integrity" I can still create records to table2 without creating a "master" or "container" record.

Any ideas?

If I mark as "required" the common field between the tables and try to write data into subform, I get an error (3314 I think) and I can't ...get out (click outside the subform) to my main/container form!

P.S. I have found a (not so elegant) solution that hides the subform until the master record has created but I wonder why Referential Integrity doesn't working for me...

 

[sagamw]

...and before you ask me. The common field between parent and child (I think these are the correct terms) is the Primary Key of my parent table and it's a auto-number.

 

[MajP]

Unfortunately sad It doesn't work. i.e. although I have a checkmark in "Enforce Referential Integrity" I can still create records to table2 without creating a "master" or "container" record

You have done something wrong or you are describing something unclearly. I have seen and built hundred if not thousands of databases. I have never the enforcing of ref integrity fail, or heard of anyone claim that it fails. I would double check.

 

[sagamw]

Hi MajP.

Here's an image showing what I wrote

I can still create records in my subform that showing in table2 and the common field (primary key in table1) is EMPTY.
*sigh*
:-(

P.S. I have found so many bugs :-( in MS Access that Microsoft should hire me for beta tester.

 

[MajP]

Never seen this. Is there a record in table1 with a null primary key? Any chance you can post a sample of this database to a file sharing site? I use 4shared.com, but there are others.

 

[sagamw]

Hi MajP

In about 2 minutes I created a new db (Access 2007) that has EXACTLY the same behavior.

I think the problem is in the "close form" button.
If I press it (with data in the child form ONLY) it saves the data even if the parent form has no data at all.

Here's my sample...

http://www.4shared.com/file/197197169/6ca96d8e/REFERENTIAL_SUCKS.html

(Sorry about the ...title of my db, but as far as I can tell is true!)

 

[MajP]

I aplogize, I misread your original post. This behavior is as designed

Referential integrity is a system of rules that Microsoft Access uses to ensure that relationships between records in related tables are valid, and that you don't accidentally delete or change related data. You can set referential integrity when all of the following conditions are met:

The matching field from the primary table is a primary key or has a unique index.
The related fields have the same data type. There are two exceptions. An AutoNumber field can be related to a Number field with a FieldSize property setting of Long Integer, and an AutoNumber field with a FieldSize property setting of Replication ID can be related to a Number field with a FieldSize property setting of Replication ID.
Both tables belong to the same Microsoft Access database. If the tables are linked tables, they must be tables in Microsoft Access format, and you must open the database in which they are stored to set referential integrity. Referential integrity can't be enforced for linked tables from databases in other formats.
The following rules apply when you use referential integrity:

You can't enter a value in the foreign key field of the related table that doesn't exist in the primary key of the primary table.

********************************************************
However, you can enter a Null value in the foreign key, specifying that the records are unrelated. For example, you can't have an order that is assigned to a customer that doesn't exist, but you can have an order that is assigned to no one by entering a Null value in the CustomerID field.
********************************************************

You can't delete a record from a primary table if matching records exist in a related table. For example, you can't delete an employee record from the Employees table if there are orders assigned to the employee in the Orders table.
You can't change a primary key value in the primary table, if that record has related records. For example, you can't change an employee's ID in the Employees table if there are orders assigned to that employee in the Orders table.

However if you make the PatientID required in table 2 you should get a message saying that you need a related patient ID not a runtime error. I have no idea why you are getting an error instead of a message. This may be unique to your country version.

http://i45.tinypic.com/15qx2lj.jpg

 

[sagamw]

Hi Majp.

When I make the PATIENTID required I don't get an error BUT I can't get out of my subform. If I click outside (in my main form) I get the message saying PATIENTID is required but the cursor returns into the subform.

As I said earlier I found a -not so elegant- solution by hiding the subform until the parent record is created. I don't like this.

Anyway, my friend, Have you got an idea for a snippet of code to delete (or to ignore the creation of) the orphan entry when the user press the "CLOSE FORM" button?

It would suit me fine for now...

 

[sagamw]

*sigh*

I tried to use the _enter() event of my subform but with no luck. I did something like this

Private sub mysubform_enter()
If surname.value = null then surname.setfocus
end sub

'//surname is in the parent form

The code doesn;t work and I can't STILL create orphan records.

Any ideas why this isn't working?
Any other ideas for my problem?

 

[sagamw]

Hm. I guess I must referenced the surname field of my subform better, but all my tries are in vain..
I tried [forms]![mymainform]![surname].value = null but still no luck...

 

[PHV]

Have a look at the IsNull function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sagamw]

Thanks PHV, it worked (here's a star for you).
(I still wondering why my original syntax isn't working also)
Anyway...

 

[PHV]

why my original syntax isn't working
Consider Null as unknown: nothing can be equal to something unknown ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sagamw]

OK. Thanks