By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,641 Members | 2,101 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,641 IT Pros & Developers. It's quick & easy.

Subform Data entry problem

P: n/a
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.
Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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.

Nov 13 '05 #2

P: n/a
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 . !###&^!!*****
Nov 13 '05 #3

P: n/a
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 . !###&^!!*****

Nov 13 '05 #4

P: n/a
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.
Nov 13 '05 #5

P: n/a
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
Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.