duplicate values in index, primary key or relationship

[Carrie]

I have been really struggling with this. I have several
tables:

Main Table: (RecNo is primary key) and includes other info
relative to all forms
Sched1: (Sched1ID is primary key)and includes RecNo
Sched2 etc.

Each form (Schedule 1, Schedule 2...) needs to include
info from the main table and info from Sched1 table. Each
form has 1 to 3 subforms (but they all come frm the same
table)

Master and child links are RecNo

Each form needs to print a copy of the current record as a
report.

I originally set this up so the forms came directly from
the tables but, I was having printing problems. I then
set the forms to come from one query (including the Main
Table and the appropriate Schedule Table) - this solved
the printing problems but, would not properly update the
tables (missing the RecNo) and generating error messages.
So, now I have set up 2 queries (MainQuery and
Sched1Query). Sched1Query is then connected to the
subforms. This is printing fine and when you add a new
record - it updates properly in both tables. My problem
is that when I move from the first subform to the next
subform I get an error message that "The changes will be
unsuccessful because they will create duplicate values in
the index, primary key or relationship".

I am not sure if the problem is my RecNo from the main
table or the ID number in the other table and am leery
about trying to remove them and possibly totally messing
things up. Thanks so much - this newsgroup has helped me
tremendously!

 

[John Viescas]

Carrie-

It sounds like you're including the "Main Table" in the query for the
subforms. Why? What were the "printing problems" you were having when you
didn't do this?

Which version of Access are you using? I seem to recall an old bug when
doing a 1-M join from a "main" table that has an Autonumber Primary Key and
the main table also has a Yes/No field with a default value defined. When
you "dirty" the row by typing a new value in a column from the many side,
Access was also generating a new Autonumber on the main side - and then the
keys wouldn't match anymore.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

 

[Carrie]

Hi,

The printing problems I was having was that the second
page of the report would duplicate the subforms on that
page 2-3 times. I'm not including the "Main Table" in the
query of the subforms - it has it's own query "Main
Query". The subforms have their own query, ie. "Sched2
Query". Here seems to be the problem - The "Sched2 Table"
includes all the information needed for the Sched2 Form
and Report (except the info from the Main Table). This
info is split up on the forms/reports into Sched2 Subform1
(on page 1) and Subform2 (on page 2) because at the top of
the second page I need to have some data from the Main
Table. Because the subforms come from one query I think
it is saying I will create duplicates because of the
SchedIDNo. The only thing I can think of is to split the
Sched2 Table up into two separate tables (one for page one
and one for page two) - I think this will work but, it
seems to me that it's not the best solution?

 

[John Viescas]

My head is spinning. What is the SQL of the query for one of the subforms?

Also, when you want to print this data in a report, you might not need
subreports at all. You're not trying to print the form, are you? Bad idea.

For your report, you can put the "main table" fields in the Page Header
section to be sure they print again at the top of each page. You can
probably get the results you want in the report by using a simple report
that has "main table" joined to the appropriate Subformn table.

What's the actual layout of your tables? Why do you have a separate table
for each form? What is the business application?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411