Editing data on subform

[Jenny]

Err Msg: The changes you requested to the table were not successful because
they would create duplicate values in the index, primary key or
relationship.

I have researched this on the web and there seems to be a bug in Access 2003
with this error msg but I am using Access 2002.

I have a main form with a combo box to choose client.

A subform displays all events relating to the chosen client.

For ONE of those clients, I am allowed to edit data in the subform. For the
rest I get the above error msg.

I have checked the indexes, relationships and Pkey's and they are all in
line with the Access rules.

The only thing that seperates the ONE client's events I can edit in the
subform form the others, is that is the first in the list. Otherwise it has
things in common with all the other clients


Please can you help? Is there any known bug here & if so please how do I
fix it?

Thanks a lot

Jenny

 

[Jenny]

Hi it's me again

Have I asked a very stupid or annoying question?!

Perhaps it is the bug that's listed for Access 2002?

Would appreciate a simple answer to either of above questions.

Thanks for your time

Jenny

 

[John Spencer]

More likely that no one has a real answer for you. You've insisted that all
your keys, relationships, and indexes are correctly set up - so that
eliminates that as a cause.

I've not experienced the problem myself as a bug. I have seen it when I
didn't have things set up correctly.

Open the table that you are using in the subform.
Are you using a multi-field key?
Or a single primary key?
Are all the appropriate fields filled in.

You say you can EDIT events for one client, but not others. By edit, I am
guessing that you mean you can add, change, and delete records for this
client and cannot do any of that for all other clients.

If you try to manually add a record for one of the other clients directly
into the table, can you? If not, then you can eliminate the form and
subform as the source of the error.

If you can, then the problem is related to the form and subform. Are the
links between the form and subform set up correctly? Are the linking fields
populated in both forms? Do you have a control(s) on the form with the Link
Master Fields value(s) and one(s) on the subform with the Link Child Fields
value(s)

Beyond that I have no further advice.

 

[Jenny]

Hi John

Thanks for your advice.

On the subforms table there is a multi field primary key. They are required
fields and so are filled in on all records.

You are right that I mean by edit that I can add, change and delete for only
the one client.

I can manually add onto the table for all clients, so the problem must be in
the form/subform.

The link child / master fields are identified in the properties of the
subform (Client). The linking field (client) is populated on both forms and
there is a control on the main form and the subform with the values held in
it.

So, bizarre that it doesn’t work… unless maybe my relationships/index/pkeys
are wrong.

RELATIONSHIPS
My Client table (mainform) has a 1 to many relationship with the event table
(subform).

INDEX
My client field in the client table is indexed: Yes (No Duplicates)
My client field in the events table is indexed : Yes (Duplicates OK)

PRIMARY KEYS
Client table: Client
Event table multifield Primary key (not including client)

Is there anything else that could be wrong that we haven’t covered here?

Perhaps it is because my Primary key in the Event Table is [ProposalNo] +
[EventNo] both indexed as duplicates OK, although obviously each combination
is unique. Do you think the subform objects to both being indexed as
duplicates OK?

Many thanks for your help

 

[John Spencer]

Something is wrong, but I'm not seeing it. Anyone else want to step in with
an idea?

Hi John

Thanks for your advice.

On the subforms table there is a multi field primary key. They are
required
fields and so are filled in on all records.

You are right that I mean by edit that I can add, change and delete for
only
the one client.

I can manually add onto the table for all clients, so the problem must be
in
the form/subform.

The link child / master fields are identified in the properties of the
subform (Client). The linking field (client) is populated on both forms
and
there is a control on the main form and the subform with the values held
in
it.

So, bizarre that it doesn't work. unless maybe my
relationships/index/pkeys
are wrong.

RELATIONSHIPS
My Client table (mainform) has a 1 to many relationship with the event
table
(subform).

INDEX
My client field in the client table is indexed: Yes (No Duplicates)
My client field in the events table is indexed : Yes (Duplicates OK)

PRIMARY KEYS
Client table: Client
Event table multifield Primary key (not including client)

Is there anything else that could be wrong that we haven't covered here?

Perhaps it is because my Primary key in the Event Table is [ProposalNo] +
[EventNo] both indexed as duplicates OK, although obviously each
combination
is unique. Do you think the subform objects to both being indexed as
duplicates OK?

Many thanks for your help

More likely that no one has a real answer for you. You've insisted that
all
your keys, relationships, and indexes are correctly set up - so that
eliminates that as a cause.

I've not experienced the problem myself as a bug. I have seen it when I
didn't have things set up correctly.

Open the table that you are using in the subform.
Are you using a multi-field key?
Or a single primary key?
Are all the appropriate fields filled in.

You say you can EDIT events for one client, but not others. By edit, I
am
guessing that you mean you can add, change, and delete records for this
client and cannot do any of that for all other clients.

If you try to manually add a record for one of the other clients directly
into the table, can you? If not, then you can eliminate the form and
subform as the source of the error.

If you can, then the problem is related to the form and subform. Are the
links between the form and subform set up correctly? Are the linking
fields
populated in both forms? Do you have a control(s) on the form with the
Link
Master Fields value(s) and one(s) on the subform with the Link Child
Fields
value(s)

Beyond that I have no further advice.