Completed subform Question

  • Thread starter Thread starter edtollefsen
  • Start date Start date
E

edtollefsen

I have the following situation:
* form and subform tied together with a field called
invoiceDate.
* form has data from one table
* subform has data from another table. This data
has a 1 to many relationship with the table used in the
form.

If I update invoiceDate from the form the data in the
subform changes appropriately however as soon as I go to
the subform the system throws an error message indicating
data already exists (see below for actual message).
There are 3 circumstances that may happen:
1) I go into the subform without changing the
invoiceDate. Everything works fine
2) I add a new record. Everything works fine.
3) I change the invoice date and enter the suform.
As soon as I click in the subform the data in the form is
saved and since it already exists it throws the error
message. I know don't want to resave the data in the
form it it already exists. I can check for its existance
prior to this. How do I keep the form from saving or is
there a better way to handle this?

COMPLETE ERROR MESSAGE:
"The changes you requested to the table were not
successful because they wold create duplicate values in
the index, primary key, or relationship. Chang 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."
 
if you're linking two tables on a one-to-many relationship using a date
field, that means in the "one" table (parent table) the date has to be
unique - in other words, you can't enter two invoice records with the same
date. in the main form, when you're changing the invoice date and that date
is already being used in another record, the error you posted is telling you
that you have entered *a duplicate value*. it has nothing to do with the
subform.
suggest you re-evaluate your tables/relationships design. normally in a
one-to-many (parent/child) relationship, the primary key field of the parent
table is added to the child table as a foreign key. then the two tables are
linked on that common field. unless you'll be adding only one record per
date to the parent table, you need to pick another field - that will always
have unique values - as the primary key. if you don't have such a field, add
a field with DataType as Autonumber, and make that your primary key.

hth
 
I understand the relationship issue. The problem is that
I would like to be able to add more records the many side
of the relationship without updating the "1" side.
However, when you go from the form to the subform it
tries to save the data and then it tells informs me the
record already exists. How do I keep the form from
trying to automatically trying to save the data.
 
the error message is not telling you the record already exists. it is saying
that *another* record with the same date value already exists in the table,
and that is violating a primary key or unique-index rule. if you want to
avoid getting that message, then don't change the date value in the record
on the main form.
my only other suggestion is that you go back to the previous thread you
started this morning at 8:13 am, titled "Subform control", and read the
answer posted by MVP Larry Linson. perhaps you can continue a dialog with
him if you need further assistance, rather than starting another thread.

good luck.
 
Back
Top