run-time error '3008' vba open table for add on report open

[mansej]

I started with the code from trusty ol' microsoft kb article 210269

http://support.microsoft.com/?kbid=210269

Looks pretty straight forward and very much needed in this case.

Put in the code with the appropriate changes.

Try to run it and get:

Run-time error '3800':

'IndexEntry' is not an index in this table.


Debug shows the culprit as the line:

TocTable.Index = "IndexEntry"


I check the table def and spelling properties and the original article as well
as searched for already posted solutions for other with the problem.


The concept is easy. When the report opens delete the records in the old index table.
As each record is printed, update the table with page numbers in the report to build
another report later.

So far I cannot get past the init function called by report open. If I comment out the
line, sure enough it complains that there is no index. The field is defined as an index
in the table.

I am certainly not as experienced as most here and I have this haunting feeling I am
overlooking really simple and basic.

All suggestions and assistance will be welcome and valued.

The code from my module is:


Declarations:

Option Explicit

Dim db As DAO.Database
Dim TocTable As DAO.Recordset
Dim intPageCounter As Integer



The function with the run-time error:


Function InitToc()
'Called from the OnOpen property of the report.
'Opens the database and the table for the report.
Dim qd As DAO.QueryDef

Set db = CurrentDb()

'Resets the page number back to 1
intPageCounter = 1
'Delete all previous entries in Table of Contents table.
Set qd = db.CreateQueryDef("", "Delete * From [Toc Table]")

qd.Execute
qd.Close

'Open the table.
Set TocTable = db.OpenRecordset("TOC Table", dbOpenTable)

'This line returns the error
TocTable.Index = "IndexEntry"

End Function



TOC Table has two fields
IndexEntry is a text field, indexed with no duplicates.


I do not no why a defined index in a table cannot be an index for a recordset from that table.


Thank you for you time and consideration.

Regards,

Manse

 

[dhookom]

Did you review the index dialog to confirm the name of the index is IndexEntry and not something else such as PrimaryKey?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mansej]

With experimentation, it has been both and consistantly
got the error.

It currently is not the primary key.
Indexed - Yes(no duplicates)

I would not think the index property of a recordset would object to being a primary key, but perhaps so.

Currently, it is just an indexed field in TocTable, the Recordset opened from "TOC Table".

Trying to set: TOCTable.Index = "IndexEntry"

is generating the error, even though the IndexEntry field
of the "TOC Table" is indexed.

....yeah, I'm baffled .......

Regards,

Manse

 

[dhookom]

Manse,
Every index has a name. You can't see the name unless you open the index dialog like I suggested. Nothing you have said thus far leads me to believe you have opened and looked at this dialog.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mansej]

You are correct. No I hadn't. In fact, at your prompting, I had to do a bit of poking around find it. Sure enough the index dialouge showed the name as "UniqueID", most likely a leftover artifact from my attempted debug efforts or perhaps even a default name.

As I originally suspected it was something simple; I had this haunting feeling like being frustrated because I can't find my pencil all the while it's stuck behind my ear.

I have changed the index name to the field name in the Index dialogue box and run-time error '3008' is no longer the issue. The table is getting built like it is supposed to with the exception of data mismatch somewhere so the bug hunt continues.

Thanx for the lesson and persistance. A valued lesson at that.

I'm still not sure why an indexed field cannot actually be an index, but I can accept it.

Soon I'll be trying to print an unlinked subreport ...

Thanx again,

Regards,

Manse