Add new record to subform

H

Hank

I have a from with a subform. All works well until I want to add a new
record to the form. I can't save the new info - an error says I am would
create a duplicate in the primary key. Somewhere I have gone wrong...
How do I get the subform to also add a new record?

Thanks
Hank
 
R

Rick Brandt

Hank said:
I have a from with a subform. All works well until I want to add a
new record to the form. I can't save the new info - an error says I
am would create a duplicate in the primary key. Somewhere I have
gone wrong... How do I get the subform to also add a new record?
Click to expand...

Most common mistake that produces this...

You do not have the MasterLink and ChildLink properties set such that the
linking field values are "inherited" in new records created in the subform.

The fields that should be getting their values from the parent have default
values. Once you create one record then any other records will try to use
those same default values and you will get the duplicate key error.
 
H

Hank

Rick,
Thanks for the reply.
I have the parent child link properties set to the contactID. The contactID
is the primary key on the master form (enrollment info) . The subform
(pickuplist) is the child.
The forms sync fine until I start to add a new record. Maybe I am missing
something in your reply. Coding is not my strongest asset.
Thanks,
Hank
 
R

Rick Brandt

Hank said:
Rick,
Thanks for the reply.
I have the parent child link properties set to the contactID. The
contactID is the primary key on the master form (enrollment info) . The
subform (pickuplist) is the child.
The forms sync fine until I start to add a new record. Maybe I am
missing something in your reply. Coding is not my strongest asset.
Thanks,
Hank
Click to expand...

What is the PK of the records in the child form and how is that populated?
 
H

Hank

The PK of the child from is PickUpID (autonumber) in a query from the pickup
table. The contactID is also in in the pickup table (as a number). It is
populated by the subfrom (pickuplist) in the enrollment from.
Hope I am explaining what you ask.
 
R

Rick Brandt

Hank said:
The PK of the child from is PickUpID (autonumber) in a query from the
pickup table. The contactID is also in in the pickup table (as a
number). It is populated by the subfrom (pickuplist) in the
enrollment from. Hope I am explaining what you ask.
Click to expand...

Well...if the PK of the child table is an AutoNumber then it should be
impossible for you to be creating a duplicate key.

Two things come to mind. Early versions of Jet 4.0 could develop a problem in
that they would try to assign an AutoNumber value that was already used. What
version of Access are you using? Do you have all Jet updates installed?

Is the child form's RecordSource a query that contains any tables besides the
pickup table? Access might be thinking that you are inserting into another
table. Generally queries for bound forms should only include the one table that
you want to write to.
 
H

Hank

Rick,
I have tried a few things leaving me with the same problem. Still cannot add
a new record.

Each time I start to add a new record the auto number assigned (contactID) a
duplicate number. If I try again - I get the next duplicate number. i.e.
When I attempt to add a new record the form is blank. As soon as I enter
the first data onto the form contactID #6 is placed in the autonumber field
and the child form is populated by the contactID #6 pickup list. The next
attempts displays contactID #7, etc. As of now there are only 41 records.
If I keep trying to add new record until I reach the last contactID number
in use - then I can add the new record.

Some of the following may have changed since my last post:

1. My main form (Enrollment Info) is populated from query (Enroll Info
Query) which is based on only one table (Enroll Info)
2. My child from (pickuplist) is populated by the pickupquery which is only
base on one table (pickuptable).
3. The PK of the Enroll Info table is ContactID - which is an auto number.
The PK of the pickup table is the PickUpID which is also an auto number.
4. I am using Access 2003. I do not know how to tell which version of Jet I
am using, but I have applied all automatic updates that I am shown when I
"check for updates".

I hope I have explained this properly. Thanks form your help.
Hank
 
H

Hank

Rick,
I forgot to add in my post..

I am not able to add a new record directly into the Enroll Inof Query. As I
said this query has only one table (Enrollment Info). I get the same error
when adding a new record -- "....creates duplicate value"

Hank
 
H

Hank

I did two things and now I can add new records to the table, query, and
form.
1st - I had a Yes/No data type in the Enroll Info form. I changed this to
text data type.
2nd - I saved a copy of the table under another name, then renamed the
origional table, then renamed the copy to the origional table name (Enroll
Info).

Don't know which of these cause the problem to go away - but it has.
Thanks
 
Top