Please help..

[shawnews]

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

 

[Joseph Meehan]

shawnews wrote:

Could that form be in data entry mode?

 

[shawnews]

the form has "data entry" set to "no".

 

[Joseph Meehan]

the form has "data entry" set to "no".

Well that blows out my only idea.

 

[User]

I tried to duplicate this but couldn't exactly. In my case when I enter
new records everything is OK, when I go back and edit records where I had
previously added values it works, but when I go back and try to put values
into fields of a table that was not filled in initially I get a dialog "You
cannot add or change a record because a related record is required in table
'<<name of master table>>'."

It seems that Access will create the related subrecords when the master
record is created if data is entered in the subrecord fields, but will not
create the sub-record if is didn't previously exist when you go back and
edit that master record. Curiously, if I create a record with values only
in the master table I can go back and add data to either or both of the sub
tables OK, but if I add to one subtable and save, then I can't add to other
tables.

One way around this might be to base your form on the first master table
only (or a query based on that table only), and add subforms for the fields
in the subtables. For me this allowed me to go back and change data for all
cases.

I guess another way to manage this is to combine all the fields in one
table. You didn't indicate why you split the data into different tables
apart from it belonging to different sections on the printed form.


Doug

 

[shawnews]

thanks alot Doug

I will try the form/subform thing. The reason why not one big table is
becuse i have over 255 fields.

I will advise

abe