SetValue question on a Control Field

[Dan H.]

I have a database with a Main Form which is called simply "main form" this
has a subform called "Confirmation Subform" and the confirmation subform has
a subform call "Detail Purchase Subform". Confused Yet. On the Confirmation
subform I have a control called "ControlNumber" it is a key field which will
be populated by the user. I would like this "ControlNumber" to populate the
same field in the Detail Purchase subform. It would be a one-to-many
relationship. The problem is i've tried setvalue function with no help.
This controlnumber is not used as the master/child link it is seprate from
that.

Thanks

 

[Allen Browne]

You could use the BeforeInsert event procedure of the inner subform to
assign the value from its parent form, but I am not sure this is a good
idea.

It would look something like this (aircode):

Private Sub Form_BeforeInsert(Cancel As Integer)
With Me.Parent
If .NewRecord Then
Cancel =True
MsgBox "Enter a record in the middle-level subform first."
Elseif Not IsNull(!ControlNumber) Then
Me.ControlNumber = !ControlNumber
End If
End With
End Sub

What bothers me is that the inner subform's table seems to be repeating this
field from the middle subform's table, even though it is not a foreign key
field. If the values in the 2 tables did not agree, would that represent an
error in your data? If so, your design really needs to be changed. If not
(i.e. it's just the most likely value, but could be different), then my
concerns are unfounded.