updating same record/recordset in both main form and subform

I have a bound main form and a bound subform based on the same
record/recordset. I need to update the underlying record from either form
and keep both forms in sync. I am getting an error 7787 when trying to save
a field update in the subform. Can anyone help with this? Code examples
welcome.

--
Message posted via http://www.accessmonster.com

In general, it is not a good idea to have a main form and subform bound to
the same record.

Typically, people do this with LinkMasterFields and LinkChildFields set (so
the main form shows the same record as the subform.) But then when you try
to create a new record in the subform, it inherits the primary key value
from the record in the main form, and so tries to create a duplicate. Even
worse, the failed attempt to create the duplicate autonumber has the
side-effect of resetting the Seed of the autonumber to the failed value, and
so any further new records you try to create in future (even in the main
form) don't work either.

It is possible to reset the seed with a compact/repair, or programmatically:
http://allenbrowne.com/ser-40.html
But this doesn't solve the design problem.

If you are using Access 2007, you can use the new split form, but this has
bugs too. It's the last item under:
http://allenbrowne.com/Access2007.html#Bugs

In addition to the problem with autonumbers, there is also a problem with
memo fields when you have 2 forms open at the same time, so your
main/subform-bound-to-one-table could give you those issues as well.
Subforms were really designed for data from a related table, not the same
table.

A possible workaround might be to use an unbound main form with a list box
showing the records from your table, and the bound subform, but nothing in
the LinkMasterFields/LinkChildFields. Use the AfterUpdate event of the list
box to set the RecordSource of the subform so it contains only the one
record chosen in the list box (or only the new record if the list box is
null.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"rdmacaulay via AccessMonster.com" u11609@uwe wrote in message
news:8c7e2a4e94d11@uwe...
I have a bound main form and a bound subform based on the same
record/recordset. I need to update the underlying record from either form
and keep both forms in sync. I am getting an error 7787 when trying to
save
a field update in the subform. Can anyone help with this? Code examples
welcome.