Problem updating one side of one-to-many relationship

[Guest]

I have a join field called "Descriptor" in table "Descriptor" that is the
"one" side of a relationship with a table called Notes, where Descriptor is a
lookup field.

I have a subform that's based on a query where the Notes table and
Descriptor table are joined based on the "Descriptor" field (a "regular"
join). I have a control, txtNewInfoSource, that is bound to Netes.Descriptor.

I tried putting both Before Update and After Update code on the
Notes.Descriptor control event to update the Descriptor.Descriptor field if
any changes are made. In both cases, I get run-time error 3164, field can't
be updated:

Private Sub txtNewInfoSource_AfterUpdate()

Me.[Descriptor.Descriptor].Value = Me.txtNewInfoSource

End Sub

I also tried binding txtNewInfoSource to Descriptor.Descriptor instead, but
in that case, I can not make any updates to the field at all -- Access makes
a beeping sound and will not let me type into the control.

How to fix?

 

[Graham Mandeno]

Hi Anita

I'm not quite certain I understand your problem.

If you have a standard one-to-many relationship between
Descriptor.Descriptor and Notes.Descriptor, then you can set the "Cascade
Updates" property on the relationship, so that changes on the "one" side
will automatically change the "many" side.

However, you seem to be wanting the change to occur in the other direction.
This doesn't make sense. If you change one "child" record so as to change
the identity of its "parent", then modifying the parent record will cause
all of its "sibling" records (other children of that parent) to become
orphans.

Maybe you could give a few more details of what you want to do and why.

 

[Guest]

I wasn't initially trying to change the child instead of the parent -- but
when I initially bound my txtbox to the parent join, Access would not let me
change the value at all. When I would try to type in that particular control,
I would just get a beeping noise and wasn't allowed to make changes. When I
changed it to the child, I had the opposite problem -- I could type all I
wanted, but couldn't save the record.

I followed your tip about the "Cascades Update" property -- which I wasn't
aware of, being new to Access programming. Once I set Cascades on the join --
and cleaned up an orphaned child record -- all the problems disappeared. I
was able to bind the txtbox to the parent, and everything's working fine.

Hi Anita

I'm not quite certain I understand your problem.

If you have a standard one-to-many relationship between
Descriptor.Descriptor and Notes.Descriptor, then you can set the "Cascade
Updates" property on the relationship, so that changes on the "one" side
will automatically change the "many" side.

However, you seem to be wanting the change to occur in the other direction.
This doesn't make sense. If you change one "child" record so as to change
the identity of its "parent", then modifying the parent record will cause
all of its "sibling" records (other children of that parent) to become
orphans.

Maybe you could give a few more details of what you want to do and why.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a join field called "Descriptor" in table "Descriptor" that is the
"one" side of a relationship with a table called Notes, where Descriptor
is a
lookup field.

I have a subform that's based on a query where the Notes table and
Descriptor table are joined based on the "Descriptor" field (a "regular"
join). I have a control, txtNewInfoSource, that is bound to
Netes.Descriptor.

I tried putting both Before Update and After Update code on the
Notes.Descriptor control event to update the Descriptor.Descriptor field
if
any changes are made. In both cases, I get run-time error 3164, field
can't
be updated:

Private Sub txtNewInfoSource_AfterUpdate()

Me.[Descriptor.Descriptor].Value = Me.txtNewInfoSource

End Sub

I also tried binding txtNewInfoSource to Descriptor.Descriptor instead,
but
in that case, I can not make any updates to the field at all -- Access
makes
a beeping sound and will not let me type into the control.

How to fix?