How to avoid the Parent ID not found Error? (1 Viewer)

[prabha_friend]

How to avoid/restrict users to not enter sub-form records before the Autonum(Primary Key) on the Parent Form generated?

 

[jdraw]

Access will give an error if/when you attempt to add a record on the Many side of a relationship before there is a valid entry on the 1 side. eg You must have a parent before adding related child.
Doesn't have to be an autonumber, but it must be PK.

 

[The_Doc_Man]

At the first point that you know you are going to enter records in the sub-form you must create the entry for the parent form. There is more to this comment than you might first realize, though. The trick is knowing that you are going to enter those records. SO... one possible suggestion is to not show the sub-form if on a new parent record, at least until the user does something to the parent that triggers creation of the record. Exactly how you approach this is up to you, but just remember... don't GIVE a user the chance to screw up by showing something that isn't ready for use.

 

[Pat Hartman]

A simple solution is to use the subform's BeforeInsert event. This event runs as soon as someone types the first character into a new record.

If Me.Parent.PrimaryKey & "" = "" Then
    Msgbox "you must create a parent record before creating a child record", OKOnly
    Me.Undo
    Cancel = True
    Me.Parent.cboClientID.Setfocus
    Exit Sub
End If

 

[arnelgp]

or you can use the Current Event of the main form, to dis Allow Addition to the subform:

private sub form_current()
Me!subformname.Form.AllowAdditions = (Nz(Me!ID, 0) <> 0)
end sub

private sub form_afterupdate()
call form_current
end sub

 

[Pat Hartman]

Access will give an error if/when you attempt to add a record on the Many side of a relationship before there is a valid entry on the 1 side. eg You must have a parent before adding related child.

True but only if your table is defined correctly and newbees rarely know enough to do this. If you leave the default as 0, Access will stop the insert because 0 is not valid. However, I would never rely on something like this so I change the default to Null (rather than an invalid value) and set the field to be required which is how you would do this if you were defining the table in SQL Server or some other RDBMS.

That way when you try to add the record in the subform before there is a record in the parent form, the FK will be null and RI will raise an error and the insert will be rejected.

Otherwise, Access doesn't care. You can insert all the orphan records you want if the FK default is null and the FK is not defined as required.

There are situations where you actually want to be able to insert "orphan" records. In that case, you set the default to Null but do not set it to be required. You might be managing a vehicle pool. Cars not assigned will have an FK that is null. It is only when the FK is filled in by the EmployeeID that the vehicle becomes a "child" of the Employee.