Index problem in access

Hi - I'm using the attached code (which I found here) to create a table will serve as a TOC for an access report.  I created it in the Northwinds sample db with no problems, but when I try to apply to my db I get Run-time error '3800' 'TOCCategory' is not an index in this table.  I've triple checked the properties settings and "Indexed" property is set to "Yes (no duplicates)."  What am I doing wrong?

THANKS!
Option Explicit
 
Dim db As DAO.Database
Dim TocTable As DAO.Recordset
Dim intPageCounter As Integer
 
Function InitToc()
 
    Dim qd As DAO.QueryDef
 
    Set db = CurrentDb()
 
    intPageCounter = 1
 
    Set qd = db.CreateQueryDef("", "Delete * From [Table of Contents]")
 
    qd.Execute
    qd.Close
 
    Set TocTable = db.OpenRecordset("Table of Contents", dbOpenTable)
 
    TocTable.Index = "TOCCategory"
 
End Function
 
Function UpdateToc(TocEntry As String, Rpt As Report)
 
    TocTable.Seek "=", TocEntry
    
    If TocTable.NoMatch Then
        TocTable.AddNew
        TocTable!TOCCategory = TocEntry
        TocTable![page number] = intPageCounter
        TocTable.Update
    End If
    
End Function
 
Function UpdatePageNumber()
    intPageCounter = intPageCounter + 1
End Function

Open in new window

egj75Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jmoss111Commented:
I don't think that you can set an Index on a dao recordset.

You can't use CREATE TABLE to add a nonunique index. To add a nonunique index to an existing table use CREATE INDEX
To create an index I'd use SQL:
CREATE INDEX IX_TocCategory ON TableOfContents (TocEntry) WITH DISALLOW NULL;

Open in new window

0
jmoss111Commented:
Object names with spaces imbedded are unnecessary and prone to cause problems and extra work.
0
egj75Author Commented:
I removed the spaces.  I'm a beginner here so I've upped the points since you'll probably have to spell things out a bit...

The code I was using generated this table when a report was opened, so the "on print' properties in various sections of the report call these various functions.  How/where would I include the sql language you provided?  

Thanks.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jmoss111Commented:
Save the statement in a query and execute it immediately after the table is built.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jmoss111Commented:
What would have been required to get an A from you. Didn't the solution work well for you?
0
egj75Author Commented:
No, it didn't work but I assumed it was due to the fact that I don't have a big picture understanding of programming and that your code would work for someone more knowledgable...so I wanted to give you the points but close the question.  (The report was due this morning so I had to abandon this effort and just create it manually.)  Do you not get all of the points if it's not an A?   Let me know I 'll change it - I appreciate your efforts!
0
jmoss111Commented:
If it didn't work then you shouldn't have closed the question, but what I gave you does work. I'd rather have no points than a B and I don't want the points if a solution didn't work. I would have been glad to work through the solution with you, and the question doesn't have to be closed in X days. I'll continue working with you even though the question is closed.

Regards,

Jim
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.