Can't add new record

[Pete]

I have a database which has been in use for over 5 years. Today, I am
unable to add a new record to two of the 5 tables. I normally add records
via forms but I can't get the empty form - the navigation button to add a
new record (with right arrow and astrick) is greyed out.

If I look at the form in datasheet view there no empty line at the bottom to
add another record either.

I've run the Compact and Repair but this makes no difference. Is there
anything else I need to do to put this right? My business depends on the
database working correctly and I would be in real trouble if I can't add new
records. Any help will be much appreciated.

 

[Allen Browne]

Okay, this should be working, Pete, so lets try to recover it with you.

When you did the compact/repair, did Access create any new tables?
Perhaps something with a name such as "Compact errors"?

If you open the form in design view, is its Allow Additions property set to
Yes?

Is the form based on a table?
Or on a query that uses multiple tables?

Are these local tables (in this database)?
Or are they linked to another database?

Are you using Access security (mdw file)?
Any possiblity of user permissions?

Anything else changed on the computer?
Admin rights? Network permissions? ...

 

[Pete]

Hi Allen

I have put my answers after each question.

Many thanks for helping out.

Pete

Okay, this should be working, Pete, so lets try to recover it with you.

When you did the compact/repair, did Access create any new tables?
Perhaps something with a name such as "Compact errors"?

Yes, there is a table called Paste Errors which was created not the last
time I ran Compact & Repair but a couple of days ago when there was a
similar problem and I ran compact & repair. This table contains just one
record.
I have a copy of the database taken a few hours before the problem occured
and it seems to be working ok. However, there have been similar problems
over the last few days and Compact & Repair solved the problem each time.
Looking at the tables in the copy database it has the Paste Errors table as
well as MSysCompactError (created yesterday) which shows Error Code -1504
Could not find field 'Description', ErrorRecid is blank and ErrorTable is
Bookings.

If you open the form in design view, is its Allow Additions property set
to Yes?

The Allow Additions property is set to Yes

Is the form based on a table?
Or on a query that uses multiple tables?

The form is based on a query that uses multiple tables.

Are these local tables (in this database)?
Or are they linked to another database?

These are all local tables.

 

[Allen Browne]

Okay, the MSysCompactError indicates that there was a problem with an index
in one of your tables (e.g. a duplicate value in a unique index.)

When this happens, Access solves the problem by deleting the index (rather
than delete the data.) You may find this table no longer has any primary
key. If there was a relation to another table based on this unique index, it
will have deleted the relation as well.

Now, the source for your form is a query that uses these 2 tables. Since
there is no longer a unique index on the fields that are joined in the
query, the query is not editable, and hence you cannot add records.

The solution is to:
1. Open the table, and identify the record(s) that have duplicates in what
should be the primary key.

2. Delete the bad record(s) or assign a different (unique) value to those
records.

3. Open the table in design view, and mark the field as primary key again.

4. Open the Relationships window (Tools menu), and create the relation(s) to
other tables that depended on this unique index.

The query is now editable, and so the form will allow edits and additions.
You can also delete the MSysCompactErrors table and the Paste Errors.

 

[Pete]

I have looked all the records for duplicates in what was the primary key but
found none.

So I have marked the field as primary key again but didn't have to set up
the relations between the tables as they had not been deleted.

Hope the database work ok from now on. Thank you very much for your help.

 

[dale earnhardtjr]

yo want is going on with you night

I have looked all the records for duplicates in what was the primary key but
found none.

So I have marked the field as primary key again but didn't have to set up
the relations between the tables as they had not been deleted.

Hope the database work ok from now on. Thank you very much for your help.