child field not updating

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello;

I have a form with Client info, a subform with lab info, and a subform
within the lab subform for sample info.
Each sample info record is linked to the ID in the lab subform as a
child-parent relationship; however, the child field does not consistently
update with the lab ID, so all the sample info entered into the sample
subform is lost. Sometimes it updates and sometimes not, I can't figure out
what is going on.

Help me before I throw a hammer into my monitor.

gryphon
 
Presumably you have 3 tables behind these forms.
Something like this:
a) Client table, with ClientID primary key.
b) ClientLabtest table, with a ClientLabtestID primary key, and a ClientID
foreign key.
c) ClientLabtestDetail table, with a ClientLabtestID foreign key.

And presumably you have already created relationships between these tables
(Tools | Relationships), and checked the box for Referential Integrity.

You also need to block the possibility of null foreign keys:
1. Open table (b) in design view.
2. Select the ClientID field.
3. In the lower pane, set the Required property to Yes.
4. Repeat with the ClientLabtestDetail table, so ClientLabtestID is
required.

This will prevent the entry of a record into the subform where the foreign
key is null.

What happens is that the subform automatically inherits the value from the
parent form's primary key, and assigns it to the foreign key field (based on
the subform control's LinkMasterFields/LinkChildFields settings.) However,
it the parent form is at a new record when you enter a value in the subform,
the subform entry inherits the null value form the parent form. You have to
explicitly block that, and setting the Required property of the field in the
table is the best way to do that.

Now when the user tries to enter a record in the subform while the parent
form is at a new record, they fill in all the fields, and then get a message
that it can't be saved. You might like to save them some time by giving them
that message when the *start* to fill in the subform entry:
Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.Parent.NewRecord Then
Cancel = True
MsgBox "Fill in the parent form first."
End If
End Sub
 
Back
Top