Form-subform duplicate error

[Marie-Lynn]

I have ParentForm and ChildForm, Form and subform respectively. The
relationship in the form is the EntryNumber, which is an autovalue.

I was getting the oft talked about '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 went back to the original ChildForm table and discovered that the root of
this error is there. Where there is always an entry for ParentForm, there is
only sometimes a corresponding entry for ChildForm. This would not be
problematic of the ONLY time I used ChildForm was when I also used ParentForm
first. However, sometimes ChildForm does not have a relative entry in
ParentForm. This is causing problems because if I hit 'new entry' for
ChildForm, the autonumber sets to the first *available* integer, where this
integer is definitely already related to a ParentForm that has an entry but
its ChildForm does not. You follwoing me?
(This last paragraph was mostly to demonstrate that I am trying to find the
cause of the problem )

How can I make it so that my Form-SubForm relationship will allow for the
use of Parentform independently and also the use of the ChildForm
independently but also allow for the relationships, where ParentForm and
Subform are correlated WITHOUT getting the icky error that doesn't allow you
to save and is aggravating in its repetetiveness?

What. Am. I. Doing. WRONG???

 

[Jae]

If I'm understanding your problem correctly, you are getting an error because
EntryNumber in your child form is a autonumber, an unique key. All you have
to do is change that field type to just regular number field and create
another field for your autonumber as PK.

 

[Marie-Lynn]

I think you are understanding me correctly, except for these two questions:

My autonumber is my unique key in the ChildForm, yes, and also in the
ParentForm. If I change that field to a regular number, how will Access
define the correlation between the two (find the corresdponding record). Is
there some way to make this field autofill (I can only have one AutoValue per
form) or do I have to maunually key in a number in the ChildForm?

 

[Jae]

The EntryNumber in your childForm is called foreign key. You could read about
the relation between PK and the foreign in below link. The easiest way to
automatically populate the foreign key in your childform is to add your child
form as a subform to your parent form. Then in the property of the subform,
put "EntryNumber" as your 'Link Child Fields' and 'Link Master Fields.'
After that when you enter new record into the subform, the foreign key will
automatically get generated.

http://www.databasedev.co.uk/primary_foreign_keys.html

 

[Marie-Lynn]

Uh oh. Just when I think I am starting to get wet around the ears with
Access, I see I am still as green as a weed. I did not know that
ParentForm/childForm is different then Form/SubForm. Damn. Nonetheless, you
have left me with enough information that I think I understand. I wll
impliment and try to post success. Thanks a lot!

 

[Jae]

I just used your term parent form and child form ease up your understanding.
Probably parentform/childform is same as Form and subform.

 

[Marie-Lynn]

Although still a little hiccup-y, your feedback has helped me resolve the
problem. Thank you.