Problem : Update or Cancel Update without AddNew or Edit

Les Simpson

Registered User.
Local time
Today, 09:42
Joined
Jul 7, 2010
Messages
17
Hi all

My membership database has worked fine until recently. Now I cannot save inserted data. On attempted saving "Update or CancelUpdate without AddNew or Edit" appears.

The problem. relates to 2 tables Member and Addresses. PK in the parent table Member is ID. In the Addresses Table the FK is ID. There is a One to One relation between the tables and Referential Integrity is set. I know 1 to 1 is not good but it worked fine in this small database

Browsing the all of the existing records is fine.Any help appreciated.

Les
 
How about posting the code where you get the error so we can see it?
 
Many thanks for your kind reply RuralGuy. I do not know how to show you the underlying code. Perhaps you could give a steer. I forgot to tell that this is Access 2010 and that the addresses, held in an Addresses table, of all existing members can be read.

The problem only occurs when I try to save a new member record after inputing the data. As well as the warning I mentioned I also get " You cannot add or change a record because a related record is required in table "Addresses" " . This warning comes before "Update or cancel update without Add New or Edit".

I have images of the relationships.

Les
 
It sounds like to enter a record in the child table before a record exists in the main table.

For instance: if you have a main table named Customers and a sub table table named Contacts you cannot enter a depending record in the Contacts table before a record in the Customers table exists.

Not sure if that is your problem, but you can check it out.

Catalina
 
Does your Form use Queries of the Tables or the Tables directly? It should really be Queries.
 
My form inserts data directly into Tables directly Rural Guy
, as far as I know. That has worked fine until now. I use queries to extract data for reports.

Les
 
Hi Catalina

What you suggest must be happening. In the past I just typed data into my multi page form, saved and all was well. Now I get these warnings and " #Error " shown in each address control in the child table "Addresses". From what you say the a new record is not being created when I enter details of new members in the main table " Member".

Many thanks for your help.

Les
 
You're welcome. Hopefully you can fix the problem.

Catalina
 
The moment you say "1 to 1" my ears perk up. There are very few reasons why one would do this. Either you have so much data associated with the entity that you had to split the tables up by sub-topic into at least three parts, or you had one block of data with different security requirements than the rest of the data.

The message you are getting seems (at first blush) to be that since you have RI turned on in a 1/1 relationship, you have to be able to enter records in both tables simultaneously, which has to be done via an appropriate join query. I can't tell you why it did work but now doesn't work. I can only tell you that 1/1 relationships are always suspect when dealing with small, simple tables.

What I don't get is how you bound two tables to be directly updated from a single form when there is a bidirectional 1/1 relationship being enforced - without involving an underlying JOIN query.
 
Hi Doc Man
The reasons I made the relations"1 to 1" between the primary table "Member" and the secondary "Addresses" table were that: 1. I failed to make to an intermediate linking file work, ie to break the 1 to 1 relation ship . 2. The number of records in the life of the database was small; presently 100. 3. I felt that to imbed all the work, home and next of kin address data in the primary file would be over long. Again please accept my thanks for your kindness in reply. I appreciate all of the folks that have offered advice.
 

Users who are viewing this thread

Back
Top Bottom