index error 3800 "fieldname" is not an index in this table

[Guest]

I really don't know what was wrong. Need help...
I added an autonumber field to an existing table. this fieldname is ID. I
set its property to index =yes (no duplicate). Now my problem is when I open
this table to find a specific record using "Index=ID" ,I got error 3800. I
run the same code for other tables (those table already had autonumber field
as index), it works. Here is the code...
dim lngId as long
dim rst as dao.recordset

set rst=currentdb.openrecordset ("tablename",dbopentable)
rst.index="ID"
rst.seek=lngId

Thanks.

--

 

[Allen Browne]

Open the table in design view.
Open the Index dialog (View menu.)
What is the Name of the index on the ID field?
It may be different to the field name.
For example, it might be called "PrimaryKey".

On a side note, I ever use the Seek method any more. Although it is very
fast, it is way to inflexible. For example, as soon as you split the
database, you cannot use dbOpenTable on your attached tables. In general, it
is much more flexible and often just as efficient if you use a SELECT query
statement to retrieve the fewest number of fields and the fewest number of
records, sorted in the desired order.

 

[Guest]

Thank you very much. It really helps me. I found that table having 3 index
fields . Is it normal? In fact, I never use 'seek' before. This is my first
time. It is because I have a form made up from 3 major back end tables. When
users make a delete record on the form, I save its key fields in a temp
table. Before I close the form, I go to update the 3 major tables according
to the records in the temp table. That is why I use "seek" to find the
specific record to delete. Is there any better way to do it?

 

[Allen Browne]

Unless you remove the entries under:
Tools | Options | Tables/Queries | AutoIndex on create/import
Access will automatically add an indext to any field name ending with ID,
Code, Num, etc.

Then when you make the field the primary key, it gives you another index. If
you set the Indexed property in the lower pane, you can get another one. And
if the field is used in a relation that has referential integrity (RI), you
can get another one.

To avoid this, I suggest you do delete the entries so Access doesn't
auto-index, don't manually index the field if it is a primary key, and don't
manually index the field if it is a foreign key (assuming you use RI.)

The question of logging the record before it is deleted is a bigger one.
Several things can trigger deletions, and users can delete several records
at once. The Delete event fires for each record, but BeforeDelConfirm and
AfterDelConfirm don't.

For details on how to use these events to track deletions, see:
Audit Trail - Log changes at the record level
at:
http://allenbrowne.com/AppAudit.html

 

[Guest]

Thank you again. I will read your suggestion regarding the delete event.

 

[Kedd123]

I had the same error message for a database and tried the "Dirk Goldgar"
solution. I received the following error: "Run-Time Error '3058' Index or
Primary key cannot contain a null value". Any suggestions?

What would be the disadvantage of simply not Compacting on Close?

 

[Allen Browne]

Presumably you tried this code by Dirk Goldgar:
http://allenbrowne.com/ser-47.html#AOIndexIsNotAnIndex

If that did not solve your problem, try this:
1. Create a new (blank) database.
2. Turn off the Name AutoCorrect options in the new database.
3. Import the *other* tables into your database.
4. Manually create this table again.
5. Link the bad table.
6. Use an Append query to add the data to the table you just created.
7. Create any relationships between the new table and your other tables.

BTW, if this is Access 2007, make sure you have applied Service Pack 1
before you do this. There is a bug with AutoNumbers that could mean the
problem will resurface at step 6 if you don't. You can get SP1 from:
http://office.microsoft.com/search/redir.aspx?AssetID=DC102486291033&Origin=HH102511011033&CTT=5

 

[Kedd123]

Thank you for your help. I did steps 1-3 below and discovered a defective
report (I could not export it, delete it, open it or edit it). So I simply
exported everything except the bad report to the new database and it works
great. FYI - I am using Access 2003 with SP3.