Subform creating orphan records?

[arbasd]

I have a MainForm and a SubForm. I select the new record selector on the MainForm to begin creating a new record. If I then enter data ONLY in the SubForm and navigate away from the MainForm an orphan record is being created from the data entered into the subform.

I do not see how this can be since I have referential integrity set in the relationship field. Can someone please help here?

thank you in advance.

 

[arbasd]

I was able to correct this by making the linked field in the subform REQUIRED so that it does not allow a subrecord to be created without FIRST having data on the main form. Although I still don't know how Access permitted a record to be created before since referential integrity was enabled?

In any event, it now works but I have a different question. If the user attempts to enter data into the subform and navigate away, they now get a cryptic error about having to enter data into blah blah blah. How can I trap that error and provide them a more meaningful message about entering data into the main form first and then simulate the esc key on the subform to remove what they have entered and then put the user back on the first field of the main form?

 

[RuralGuy]

1) If what you say is correct then I feel certain you had a MainForm record created before you were able to create a subform record. The action of moving the focus to the SubForm will save a MainForm record.
2) The SubForm OnError event can trap the error of which you speak.

 

[spikepl]

1) If what you say is correct then I feel certain you had a MainForm record created before you were able to create a subform record. The action of moving the focus to the SubForm will save a MainForm record.

That ain't necessarily so. Without any defaults or user input in main form, you can create an orphaned record in the subform quite happily. Try it.

One can check on whether the foreign key in the subform record exists or is null in the subform's Before Insert event and cancel it if need be - Cancel=True, undo the input (Me.Undo) and set focus to main record

 

[arbasd]

Thanks for the reply but no. There was no recorded created at the main record level. The record indicator below still shows it to be a new form and the main record table does not contain a "blank" record. Yet, when I look at the table the subform is based upon, there is a record created and the linked field is blank. Strange for sure.

Thanks for the tip on the OnError event of the subform. I will take a look at that and see what it looks like.

 

[arbasd]

That ain't necessarily so. Without any defaults or user input in main form, you can create an orphaned record in the subform quite happily. Try it.

One can check on whether the foreign key in the subform record exists or is null in the subform's Before Insert event and cancel it if need be - Cancel=True, undo the input (Me.Undo) and set focus to main record

Thank you for your reply and you are exactly correct. It will create an orphaned record. Also your suggestion about checking the foreign key is perfect and much more elegant than what I came up with (simply making the foreign key a mandatory field) because it avoids the error code and allows me to pop in a useful message to the user.

Many thanks

 

[spikepl]

Remember to check what the thing does if user closes the form mid-way by clicking on the cross in the window corner - sometimes the system can be a bit bitchy So wait with throwing the Required away until that is verified.

 

[arbasd]

Ha ha..bitchy is a nice way to put it. Access seems to have all kinds of nuances to when it commits the record and updates the field. I am beginning to believe that unbound forms are simply easier to work with and control.

 

[RuralGuy]

Where did you define the relationship and turn on Referential Integrety? I still find it hard to believe you can create orphans.

 

[arbasd]

Access 2007...defined the relationship under Database Tools - Relationships. created the one to many relationship and clicked the referential integrity box. I then created the form/subform base on the same two tables.

Try it...it is pretty simple and as long as you don't dirty the main form and create a record on the sub form it can be done. I guess one thing I could also do is not enable the subform until the main form contains some data.

 

[RuralGuy]

Any chance you have a sample I could play with. I would think it would throw an error when you tried to leave the SubForm regardless of the method.

 

[arbasd]

Any chance you have a sample I could play with. I would think it would throw an error when you tried to leave the SubForm regardless of the method.

Sure no problem. Here you go. A simple two table one form/subform.

 

[gemma-the-husky]

OK - NULLS are funny things - i suspect that your orphan record is being created with a NULL value on the FK, which will probably not break the RI.


(note that you CAN have "duplicate" keys on a unique multi-field index, if the same field is set to NULL, which is probably a similar thing)


The easy way, is in the main form's lost focus event, make sure the record is saved. if you have an incomplete main form record, it will either be saved, or give you an update error - either way, you will not be able to add orphan records in the sub-form.

 

[missinglinq]

...i suspect that your orphan record is being created with a NULL value on the FK, which will probably not break the RI...

Exactly right! Access Help states

You can't enter a value in the foreign key...

However, you can enter a Null...

 

[arbasd]

Thank you. That is very informative. Wouldn't an easy way to prevent is to simply require a value in the Foreign Key? If I do that then I don't believe a null will suffice.

 

[arbasd]

It also appears to me that if the Primary Key cannot be null...why would Access allow the foreign key to be null when "enforce referential integrity" is turned on? That would seem to undermine the entire purpose of enforcing referential integrity no?

 

[gemma-the-husky]

that's NULLS for you.

The problem seems to be trying to create the subrecord BEFORE committing the parent record (ie, at a time when the PK of the parent IS still NULL). Fix the parent, and the hild will be sorted automatically, I expect.

 

[RuralGuy]

Another reason to use AutoNumbers as the PK, IMHO.

 

[arbasd]

Another reason to use AutoNumbers as the PK, IMHO.

The PK is an autonum field? This does not prevent a "null" from being created when a user happens to enter something in a subform without entering anything in the parent form an orphan record can still created.

 

[gemma-the-husky]

find a suitable subform event, and add this sort of thing.

if nz(parent!recordid,0) = 0 then
msgbox("sorry. Please complete the main record entry")
parent.setfocus
end if