Subform Data entry problem

[JJ]

I have a MainForm/subform1/subform2 setup that I thought
was working but maybe I just hadn't tested it enough or
broke it as I added more features. The database has 2 main
tables: Companies and AgreementHistory. On my MainForm I
get the CompanyName from the Companies table. For subform1
I get the AId, and 3 date fields plus a calculated field from
a query based on the AgreementHistory table linking it to MainForm
using CompanyName. For subform2, I get the remainder of the
fields(12 total) also from the AgreementHistory table.
Subform1 and 2 are linked by the AId. Subform1 is
a continuous view form and the idea is that for the
current record you will see the details in subform2. The
OnCurrent code for subform1 is Me.Parent!
[subform2].Requery. When I add a new record in subform1 I
have a hidden control that sets focus to subform2 but when
I try to save details in subform2 I get a ".changes would
create duplicate values." error. If I click on another
record in subform1 or close the form I can come back and
enter the details into subform2.
Is it the Requery action that's causing me problems and
should it maybe not be activated for a new record or maybe
not on the OnCurrent event. Or worse, are my tables set up
wrong.I really need some assistance and my VBA skills are limited!
Thanks in advance.

[PC Datasheet]

Since both subform1 and subform2 are based on the AgreementHistory table,
subform2 should not be nested in subform1. Rather subform1 and subform2
should be set up the same; both as subforms of the main form. Subform1
already is. Change the LinkMaster/Linkchild properties of Subform2 to
CompanyName same as Subform1. Delete the code you have in the current event
of Subform2.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"JJ" <ch*****@hotmail.com> wrote in message
news:d9**************************@posting.google.c om...

I have a MainForm/subform1/subform2 setup that I thought
was working but maybe I just hadn't tested it enough or
broke it as I added more features. The database has 2 main
tables: Companies and AgreementHistory. On my MainForm I
get the CompanyName from the Companies table. For subform1
I get the AId, and 3 date fields plus a calculated field from
a query based on the AgreementHistory table linking it to MainForm
using CompanyName. For subform2, I get the remainder of the
fields(12 total) also from the AgreementHistory table.
Subform1 and 2 are linked by the AId. Subform1 is
a continuous view form and the idea is that for the
current record you will see the details in subform2. The
OnCurrent code for subform1 is Me.Parent!
[subform2].Requery. When I add a new record in subform1 I
have a hidden control that sets focus to subform2 but when
I try to save details in subform2 I get a ".changes would
create duplicate values." error. If I click on another
record in subform1 or close the form I can come back and
enter the details into subform2.
Is it the Requery action that's causing me problems and
should it maybe not be activated for a new record or maybe
not on the OnCurrent event. Or worse, are my tables set up
wrong.I really need some assistance and my VBA skills are limited!
Thanks in advance.

[JJ]

"PC Datasheet" <no****@nospam.spam> wrote in message news:<qK*****************@newsread2.news.atl.earth link.net>...

Since both subform1 and subform2 are based on the AgreementHistory table,
subform2 should not be nested in subform1. Rather subform1 and subform2
should be set up the same; both as subforms of the main form. Subform1
already is. Change the LinkMaster/Linkchild properties of Subform2 to
CompanyName same as Subform1. Delete the code you have in the current event
of Subform2.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

Thanks, I made the 2 changes you suggested but now the data in
subform2 does not change to reflect the current record in subform1. My
whole purpose here is to present a useable form that allows users to
see the summarized history(subform1) then see details in subform2 and
also allow them to enter a new record for a company . !###&^!!*****

[PC Datasheet]

Yes, I was wrong! Your tables need to look like:
TblCompanies
CompanyName - Primary Key
etc

TblAgreementHistory
AID
CompanyName
etc

Subform1 control on the main form needs to have LinkMaster/LinkChild
properties = CompanyName

Subform2 control on main form needs to have:
LinkMaster = NameOfSubform1Control.Form!AID
LinkChild = [AID]

The following code is needed in the Current event of Subform1:
Me.Parent!NameOfSubform1Control.Requery

"JJ" <ch*****@hotmail.com> wrote in message
news:d9************************@posting.google.com ...

"PC Datasheet" <no****@nospam.spam> wrote in message

news:<qK*****************@newsread2.news.atl.earth link.net>...

Since both subform1 and subform2 are based on the AgreementHistory table, subform2 should not be nested in subform1. Rather subform1 and subform2
should be set up the same; both as subforms of the main form. Subform1
already is. Change the LinkMaster/Linkchild properties of Subform2 to
CompanyName same as Subform1. Delete the code you have in the current event of Subform2.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

Thanks, I made the 2 changes you suggested but now the data in
subform2 does not change to reflect the current record in subform1. My
whole purpose here is to present a useable form that allows users to
see the summarized history(subform1) then see details in subform2 and
also allow them to enter a new record for a company . !###&^!!*****

[JJ]

Tried that but a new problem arose. When I start typing an entry in
Subform2 I get the error, "You can't assign a value to this object.
*the object might be a control on a read-onlyfor. *The object may be
on a form that is open in Design view. *The value may be too large for
this field. But when I click ok, I can continue my data entry and save
the record.

[JJ]

ch*****@hotmail.com (JJ) wrote in message news:<d9**************************@posting.google. com>...

Tried that but a new problem arose. When I start typing an entry in
Subform2 I get the error, "You can't assign a value to this object.
*the object might be a control on a read-onlyfor. *The object may be
on a form that is open in Design view. *The value may be too large for
this field. But when I click ok, I can continue my data entry and save
the record.

--------------------------------------------------------------------------
Update to this error. Did more searching and found the following
suggestion:
Add these two lines to the Before Insert event of the subform:
Me.Recordset.AddNew
Me.Recordset.Update

Appears to work but someone in the message thread did say that it
didn't
help them. If you have any info why this is not a good approach please
let me know. Thanks much