Redefine Index??

[Guest]

I split a large table (which is the "one") into two related, the child (which
is the "many") has an autogenerated primary key. My main data entry form is
now based on a query and the subform utilizing the child table is based on
the same query. I am trying to add records to the child table but am getting
the error message "duplicate entry" re-define your index etc. Can anyone
offer an answer? Thank you.

 

[Lynn Trapp]

Jeff,
Why are you using the same query for both your main form and the subform? If
the query is updatable, then every time you try to enter a record in the
subform it will attempt to create a new record in the main table, if I
understand you correctly.

 

[Guest]

Since originally posted I left the main form using the query produced by
Access when I split the table. I have tried pointing the subform to the
child table and I get the same error. Each record in the child table has a
date field and three descriptors. When editing a Master record..I intended
the subform (based on the afore described child table) to allow me to add
additional "TEST" records for each master record. Thanks (Iowan at heart in
Texas)

 

[Lynn Trapp]

Hmmmmmmmmm... could you post the table structures of the 2 tables?

(Iowan at heart in Texas)

LOL.. .I'm a Texan who is temporarily misplaced in Iowa.

 

[Guest]

Not sure what all I should give you for the structure but:

Child = Test Parent = Master
Test to Master = one to many
Primary key Master = ID (autonumber)
Primary Key Child = Tests_ID (autogenerated)

Enforced, Cascade updates

One record on Master Table currently has only one "Test" record in the child
table. Every six months I want to add new test data to the child records
keeping track of multiple tests for each record on the Master Table.

Hmmmmmmmmm... could you post the table structures of the 2 tables?

The relationship was built using the Wizard to split the table and create
the combining query. I selected the fields I wanted in the child table.
Thanks....

 

[Lynn Trapp]

When you say the Primary Key for the child table is "autogenerated" how are
you doing that?

 

[Guest]

I feel like I am really close to what I want...the subform based on the child
is set to display as a data sheet in hopes that each row or test (a date and
three descriptors), will show on the main form and I can just add new results
and a date every six months.

 

[Guest]

Using the analyze wizard I selected the four fields in the parent table, as I
dragged the first field, the wizard created the new table structure
automatically generating a unique identifier.

 

[Lynn Trapp]

That may be where your trouble is. Was the first field the primary key from
your original parent table?

 

[Guest]

Child Table

Test Date - Date/Time
Size - Text
Neg - Yes/No
Pos - Yes/No
ID - Primary Key - AutoNumber

The first field I dragged out of the Parent was the Test Date....as soon as
that was done, the wizard added the ID field. I have indexed all the fields
so as to allow duplicates and I still get the error message.

 

[Lynn Trapp]

You don't have a foreign key in your child table that links to your parent
table? Are you showing me all the field names in your child table. Your
structure should look something like this:

ParentTable
ParentTable_ID (Primary Key, AutoNumber)
Field1
Field2
Field3

ChildTable
ChildTable_ID (Primary Key, AutoNumber)
ParentTable_ID (LongInteger)
Field1
Field2
Field3

 

[Guest]

Yes...those were all the fields.....I will try again (might as well 8 times
the charm), Thanks Lynn

(70 degrees in the Rio Grande Valley)

 

[Guest]

Well...there is something I am just not getting... M. Braindead

 

[Lynn Trapp]

You need to create your child table by doing a Make Table query from the
original Parent Table. Do it by selecting the 4 fields you need for the
child table AND the Primary Key of the Parent Table into a new table. Open
the new table and, if the field for the primary key is set to AutoNumber,
change it to Number/Long Integer. This will now be the Foreign Key that will
link back to your Parent Table. Then, Add a NEW AutoNumber field to the
table and make it the Primary Key of the new Child Table.

 

[Guest]

Beginning from scratch I made my child table as instructed and modified the
desing to reflect the IDs and Primary keys you gave me. I then created a
correct one-to-many relationship between the two. I made a form based on the
child and inserted it into my main data entry form as a subform. I added a
"add record" command button to the sub form.

I get the following error:

The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key, or relationship.
Change the data in the field or fields that contain duplicate data, remove
the index, or redefine the index to permit duplicate entries and try again.

I have double checked the design of the child. The only field indexed is
the ChildTable_ID which is the primary key. The autonumber primary key of
the Parent is linked to the ParentTable_ID I inserted into the child table.

I don't know if I am losing the battle or not. Thanks

 

[Lynn Trapp]

What does your 'add record' button do? Have you tried simply using Access's
built-in add record button? What is the record source for your subform and
what are the link fields between the 2 forms?

 

[Guest]

Main form uses record source Parent Table, Subform uses record source Child
Table. I have tried linking from the PKey Parent to both the Pkey Child and
the Fkey Child. Both give me the sam error. The "Add" button I pulled from
the command button menu. I also reposted more info earlier this morning
under "Fresh Start" in this forum. When I win this battle I am ordering ice
cream. Thank you.

 

[Lynn Trapp]

Main form uses record source Parent Table, Subform uses record source

Child
Table. I have tried linking from the PKey Parent to both the Pkey Child
and
the Fkey Child. Both give me the sam error.

The link fields should be the Primary Key of the parent table and the
Foreign Key in the Child table.

Try removing your custom "Add" button and just using the built in add record
button on your subform.

When I win this battle I am ordering ice cream. Thank you.

Blue Bunny Ice Cream, of course!!!!! ;-)

 

[Guest]

Now...before I get the Error Message, when I try adding a new date (set to
short date) I get an error reading "You can't assign a value to this object,
the obhect may be a control on a read-only form...etc.

I grew up on "Wells" Blue Bunny, the only product down here are a few
flavors of low fat yogurt. No Ice Cream like "Van and Bon Bon"

Sorry about the other post..I am still learning about this forum. Thank you.

 

[Guest]

When I built the child table with the table building query I included the
primary key from the Parent table, an autonumber field and named it
ParentTable_ID. I then changed it's property from autonumber to number and
it is used as the foreign key in the Child. There will only be entries in
this field for those records exisiting when I built the Child Table. Maybe
this is the problem? When I add records to the Child, I am only adding
entries to Field 1-4, I am not adding anything to the ParentTable_ID.