Not letting me enter records due to "duplication" 1

[eques4]

Hi

I have a 150 record database with a form at it's front end. It lists the various offices managed by my company and often when I try to add a new record I get the message "This record may not be saved due to a duplication in the Primary Key/Index/Relationship etc etc."

The Primary Key is an auto number, and I have removed any indexing I could find from the original tables but am still getting the same message.

I do not really understand how the part about Relationships applies - the form is constituted from 4 tables, linked by their Primary Key Autonumber.

Some of the fields I enter do duplicate those on previous records (eg town or zipcode) but I do not see how this could cause a problem once the indexing was removed.

Does anyone know what is causing this and how it can be remedied?

Many thanks

Eques

 

[BNPMike]

Can you explain this:

linked by their Primary Key Autonumber.

. If these are autonumber then they are out of your control so that means Access would have to connect them logically together, and how does it know how to do that?

Maybe tell us the nature of the 4 tables and the main data items.

Also post the query (SQL) on which the form is based.

 

[ZmrAbdulla]

All the tables except the master table should not have AutoNumber Primary Key (if they are connected to the master by that field).
All child tables to have Number(Long Integar) as Primary key conected to the AutoNumber Master table. But Primary Keys of those tables should be a different field.

________________________________________
Zameer Abdulla
Visit Me
Minds are like parachutes. They only function when they are open. -Sir James Dewar (1877-1925)

 

[eques4]

The form is based directly on the tables without using queries.

There are 4 tables from which the form is drawn, linked by their autonumber in the relationhip box. The main table contains basic information such as office address and telephone number. Then there are 3 subsidiary tables adding information such as rent paid, lease end date, landlords etc. (admittedly now I have more experience I would not have split all this into 4 tables in the first place!).

When I originally set up the form I ensured that the information contained in the 4 tables was cross referenced correclty via the autonumber (for example if 10 Fairfax Street, London was autonumber 25 I made sure that 25 was input on the relevant supplier record in the landlord field)

Hope this clears things up and thanks for your assistance.

 

[eques4]

Zameer:

Thanks for the reply. I have now removed the primary key designation from the child tables so that the relationship is now One to Many.

The trouble now is that I cannot make any changes at all to the database. Nothing happens when I type stuff in and it will not let me create a new record!

Do you know what has caused this?

 

[PHV]

Have a look here:

http://msdn.microsoft.com/archive/en-us/dnaraccess/html/msdn_harness.asp

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BNPMike]

PHV

Eques is not using queries. I guess he/she has dropped four forms onto one page and used a Wizard to connect them in some way.

Eques
I'm not sure why you've split the tables. If they are one-to-one then just merge them. If they are one-to-many eg many rent payments per property then use the form-subform wizard (check this out on help).

Altogether though, I suggest you buy a good book on Access.