Please help

Ok...I was persented this problem and i have it almost worked out (I think).
A customer of mine has asked for a database to store information based on a
written form he used in the past. The nature of the form is such that there
are several areas of info but the tables would NOT form any kind of
one-to-many relationship. I know it is rare, but I believe this to be a
one-to-one relationship?

So, based on the fact the (written) form has several areas of info, I made
several tables - one for each area. I made a primary key, called it cardID
and have allowed no duplicates. This key I created in one table. In the
corresponding tables I made a field also called cardID, but did NOT set it
as a primary key, but rather, a number field. (these fields are indexed but
set to not allow duplicates). I then created a one-to-one
relationship...each table connecting to the first table (with the primary
key). I left the joins as "only including rows where the joined fields
from, both tables are equal".

I then created a query based on all the tables, omitting the "cardID" field
and from this query created a form to enter data.

All works well. I can enter data, go to another record and enter new
data..I can even create new queries filtering out what I want and I can
generate reports from these queries.

BUT...here is the problem. Lets say I have a record..let's say record 2. I
am in the fifth record and realize I missed something in record 2. I can
return to this record in my form, but when I try to enter new data and then
re-save the record, I get this 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 dupicate data, remove
the index or redefine the index to permit duplicate entries and try again."

Please...any suggestions would be most welcome. Thanks in advance to any
and all who respond :)

re: Please help

shawnews wrote:
Hi,

It sounds like you're overcomplicating this.

How many fields, total, do you have in all your tables? If it is 255 or
less, then just use one table. Then you just have the different forms
show different fields.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

re: Please help

sadly it is exactly 258 fields :(


"Tim Marshall" <TIMMY!@PurplePandaChasers.Moertheriumwrote in message
news:elkfqh$612$2@coranto.ucs.mun.ca...

re: Please help

"shawnews" <sales@kalendars.cawrote
If you are counting the "foreign key" fields in the one-to-one related
tables, you may have just made it under the wire.

If not, be sure you are using the form in edit mode, when you go back to
edit. It seems as if you may still be in data entry mode and trying to add
another copy of a record. Also, be sure to include the key field of each of
the tables you are trying to update in the query.

Larry Linson
Microsoft Access MVP

re: Please help

Thanks Larry....

However...when I add even one more field into the query, I get a "too many
fields defined" error...I guess i'm using all 255 fields already :(



"Larry Linson" <bouncer@localhost.notwrote in message
news:LGkfh.3028$Ft4.1409@trnddc02...

re: Please help

"shawnews" <sales@kalendars.cawrote in message
news:n4nfh.462619$1T2.251717@pd7urf2no...Then I can only suggest that you use separate Forms for [some | all] of the
related tables, and show them in a Subform Control.

You could have a Control on the Main Form that you used to select which Form
(thus, which Table) is shown in the Subform Control. You'll simplify the
Query that is RecordSource of the main Form, and the Query that is
RecordSource of the Form shown in the Subform Control.

Larry Linson
Microsoft Access MVP

re: Please help

Larry:

In an earlier message you said

"Also, be sure to include the key field of each of the tables you are trying
to update in the query."

Remember..I have one table with a primary key (autonumber) called CardID.
Each other table has a CardID field as well, but not set as a primary key.
Instead of autonumber in these tables, i just set the value of these fields
as number. So...did you mean I need to only include the (primary)key field
from the one table...or do I need to include all the fields named CardID ?

I will try your suggestion below of a form and subforms and will let you
know...thanks again :)


"Larry Linson" <bouncer@localhost.notwrote in message
news:g2sfh.542$yZ4.219@trnddc05...

re: Please help

You need a Primary Key Field in each of your Tables... a Primary Key does
not have to be an AutoNumber, if that is what you are thinking. The Long
Integer Field that you are using as a foreign key to the main Table can be
set as Primary Key, but if that table is to be updated, its key must be in
the Form's Record Source.

Larry Linson
Microsoft Access MVP

"shawnews" <sales@kalendars.cawrote in message
news:9Cufh.463811$1T2.423318@pd7urf2no...

re: Please help

thanks again Larry...I'll try that and let you know :)

appreciate your help on this.

Abe


"Larry Linson" <bouncer@localhost.notwrote in message
news:oeGfh.8251$Q36.4016@trnddc08...