Entering Subform Records when the main forms PK is (new)

[MatthewGrace]

I have what I believe to be a very common issue, and I can solve it a few ways, but I'd like to know what practice people think is best. One and Many table are represented on the Form/Subform below. Since the subform's master/child fields are properly established, Access knows to automatically fill in the FK into the Many table. But in the picture below, you can see a potential stumbling block: if the User jumps to a new record, and starts to fill in the subform while skipping the main form, there is no way for Access to link these two. You see me entering many records here, and they're all orphaned by default:



What is the desirable way to turn off or disallow the User from adding records into a "many" subform like you see above, until the main "one" form to which it's master/child fields are linked, has it's Primary Key established?

PS: I've used the relationships pane to Enforce Referential Integrity, and shockingly, that doesn't prevent Access from orphaning all the records in the above picture!

[Bob Fitz]

With TableOneFK field, change the "Required" property to yes and remove the 0 from its' "Default Value" property.

[ranman256]

Save the main rec. then enter all sub recs.

[June7]

Could use VBA to disable the subform container until data entered into a field of the main form. Use the main form OnCurrent event as well as textbox AfterUpdate event.

[davegri]

I should think that referential integrity enforcement via the relationship diagram should take care of this and make it impossible to add the many unless the one exists.

[June7]

Did some testing. Referential integrity alone does not prevent creating orphans.

What it can do, if CascadeDelete is activated, is delete dependent records if a parent record is deleted. For example, delete an order from Orders table then dependent records in OrderDetails would be deleted. And CascadeUpdate would cascade an edit of PK value (when a PK is not an autonumber field) to the FK field in dependent table.

[davegri]

Attachment 29372 Attachment 29373

Here's how I do it. You cannot add a Book on the Many side if there is no Author on the One side; Access won't allow it.

[June7]

Ooops, okay, tested more and got that to work. I so seldom rely on the Relationships settings to manage data integrity, lost track of that functionality.

However, I would still recommend the code so users don't get annoyed by popups. Don't even let them get into the subform, absentmindedly or otherwise, until appropriate condition met.

[MatthewGrace]

@Davegri / @June

Here's how I do it. You cannot add a Book on the Many side if there is no Author on the One side; Access won't allow it.

Surprisingly Access does allow it. As I said in OP, I've used the relationships pane to Enforce Referential Integrity, and shockingly, that doesn't prevent Access from orphaning all the records:



Yet in Dave's example it appears to work beautifully. I think what I'm learning is that the ERI option in the relationships pane doesn't stop Null values from being placed into a foreign key field! My gut would have told me for sure Access wouldn't allow that. I think what Dave has going for him is that his FK control (textbox/combobox) is set to default to 0, and that somehow triggers Access's built-in ERI to say "you have to have a related record in the 'one' table". In other words, ERI doesn't trigger for Null values, it only works when a real integer is stored into the cell.

When I built this form - don't laugh - I used to Form Wizard. I guess the wizard did not set that up correctly to stop orphaned records.

I don't suppose either of you guys know how to modify the MessageBox that popped up in Davegri's example titled "Patti's Library"? As always, I appreciate all your efforts.

[MatthewGrace]

Yea, the more I toy with this, the more I realize because of the various popups that use language not friendly to end users, it's best in my situation to do it all VBA per June's suggestion. Unless someone knows how to override those popups with a friendlier message, which would be cool. Thank you all.

[davegri]

Add this to the subform code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.Parent.NewRecord Then
        MsgBox "Unable to add a record until a record exists in the main form", vbCritical + vbOKOnly, "  N E E D   I N P U T   "
        Cancel = True
        Me.Undo
    End If
End Sub

It checks to see if a record exists yet in the main form.
And yes, I'm amazed that Access allows orphan records in that manner!

[June7]

Yes, a friendly message but still a popup user has to respond to. I try to design and program to prevent popups that frustrate users and reduce productivity, hence preventing access to subform unless conditions are appropriate.

[andrejamih]

Hello! I have the same problem with my main form and few tabbed subforms. What would be the VBA code for main form OnCurrent event and on textbox AfterUpdate event for preventing adding data into subofrms' fields without filling Master field on main form which is also a PK?

[ArviLaanemets]

Don't use default values on tables! When you need a default value, define it for form control instead!

[June7]

@andrejamih, should start a new thread with your question instead of hijacking an old one. A question will get more attention as a new one with no replies.

Code to manage availability subform container could be like:

Me.ctrSubform.Enabled = Not Me.NewRecord

or

Me.ctrSubform.Enabled = Not IsNull(Me.Customer)