By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,835 Members | 1,909 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,835 IT Pros & Developers. It's quick & easy.

Error updating subforms

P: n/a
Hello all

My database has a main form linked to table1. It has several subforms
on the main form, all linked to table2. Table1 and Table2 are linked
by primary/foreign key, no duplicates allowed, one-to-one.

If I update field(s) on a single subform it works fine but when I
update fields on multiple subforms ie without closing the form between
updates, 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.

It looks to me that when I update the first subform, access creates a
row in table2 with the same id as in table1. That's what I want it to
do. If I then go and update another field in a different subform
without closing the form first, it tries to create another row in
table2 with the same id, which gives the error.

Is there any way to fix this eg making access do a commit after each
update to a field in a subform?

Hope that makes sense, thanks in advance for any suggestions.

Richard
Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Richard:

You should really have any fields that pertain to table 2 in just one
subform, not multiple subforms. Is there a reason you have it split
over so many subforms? Your thoughts on what Access is doing are
correct as the reason to do a subform is to allow the user to create
related records without having to manually input the foreign key.
Therefore, when you start typing in another subform that relates to
table 2, it creates a new record and inputs the foreign key value
which, in turn, causes the error.

Rather than having multiple subforms linked to table 2, could you use a
tab control? That would allow you to break apart the data, but still
only be working on one record at a time.

That's all I can think of, maybe someone else has some better
suggestions.

Good luck!
Jana

Nov 13 '05 #2

P: n/a
Hi Jana

thanks for your reply.

The reason for having to use multiple subforms is that originally the
database consisted of only one table, and over the years users have
asked for more and more fields, which I have added gradually, until
I've reached the maximum allowed. At that point I created a second
linked table and put the fields on subforms. I know that's not good
practice but I don't have the time (and if I'm being honest, the
expertise!) to rebuild it.

The form is already tabbed and I can't put the new fields onto a
single subform as they are dotted around on several pages.

Is it possible to avoid the use of subforms entirely and have the one
main form writing data into 2 tables?

thanks again

Richard

"Jana" <Ba********@gmail.com> wrote in message news:<11**********************@g14g2000cwa.googleg roups.com>...
Richard:

You should really have any fields that pertain to table 2 in just one
subform, not multiple subforms. Is there a reason you have it split
over so many subforms? Your thoughts on what Access is doing are
correct as the reason to do a subform is to allow the user to create
related records without having to manually input the foreign key.
Therefore, when you start typing in another subform that relates to
table 2, it creates a new record and inputs the foreign key value
which, in turn, causes the error.

Rather than having multiple subforms linked to table 2, could you use a
tab control? That would allow you to break apart the data, but still
only be working on one record at a time.

That's all I can think of, maybe someone else has some better
suggestions.

Good luck!
Jana

Nov 13 '05 #3

P: n/a
Richard:

Does this mean that you have a one-to-one relationship between the data
in table 1 and the data in table 2 just because of the sheer number of
fields? Yikes! That's a lot of fields! It would be tough to display
255 fields on a form, let alone more than that :)

What I would do is have the main form have data from table 1, then
create a tabbed subform for all of the table 2 data, if you can. Then
you'll end up with one main form and one subform which will eliminate
your duplicate index error. You could have a tab control on the main
form to allow you to organize the table 1 fields, and then have your
subform have a tab control so you can organize the table 2 fields.
Perhaps this would be less confusing for your users if you had the
table 1 tab control in the upper half of the screen, and your sub form
with a second tab control in the lower half of the screen?? You'd just
have to make sure that users always input something in the table 1 data
fields before they try to input anything on the subform (but only an
issue when creating a new record in table 1).

I don't envy you trying to display over 255 fields on a form...hurts my
brain just thinking about it!!

To answer your question about having the main form writing to two
tables, the short answer is no. If you do a query that pulls those two
tables together, it would be difficult to coordinate the indices
between the two tables. It's probably not impossible, but it's outside
of my skillsets...maybe someone else knows a good way to do that?

Good luck,
Jana

Nov 13 '05 #4

P: n/a
Hi Jana

In answer to your 'yikes', Yes it's a nightmare to maintain!

The main form is tabbed and the new fields from table 2 are dotted
around on several pages, so I can't organise them in the way you
suggest, unfortunately.

It looks as though if I want to add more fields I need one table per
subform?

Richard

"Jana" <Ba********@gmail.com> wrote in message news:<11**********************@o13g2000cwo.googleg roups.com>...
Richard:

Does this mean that you have a one-to-one relationship between the data
in table 1 and the data in table 2 just because of the sheer number of
fields? Yikes! That's a lot of fields! It would be tough to display
255 fields on a form, let alone more than that :)

What I would do is have the main form have data from table 1, then
create a tabbed subform for all of the table 2 data, if you can. Then
you'll end up with one main form and one subform which will eliminate
your duplicate index error. You could have a tab control on the main
form to allow you to organize the table 1 fields, and then have your
subform have a tab control so you can organize the table 2 fields.
Perhaps this would be less confusing for your users if you had the
table 1 tab control in the upper half of the screen, and your sub form
with a second tab control in the lower half of the screen?? You'd just
have to make sure that users always input something in the table 1 data
fields before they try to input anything on the subform (but only an
issue when creating a new record in table 1).

I don't envy you trying to display over 255 fields on a form...hurts my
brain just thinking about it!!

To answer your question about having the main form writing to two
tables, the short answer is no. If you do a query that pulls those two
tables together, it would be difficult to coordinate the indices
between the two tables. It's probably not impossible, but it's outside
of my skillsets...maybe someone else knows a good way to do that?

Good luck,
Jana

Nov 13 '05 #5

P: n/a
Richard:

Yes, that is correct...one table = one subform, just make sure you have
a field that links the two tables together so the form can pull the
data back together.

Good luck,
Jana

Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.