Index problem in access

Posted on 2008-10-23
Last Modified: 2013-11-28
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?

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]")



    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!TOCCategory = TocEntry

        TocTable![page number] = intPageCounter


    End If


End Function

Function UpdatePageNumber()

    intPageCounter = intPageCounter + 1

End Function

Open in new window

Question by:egj75
  • 5
  • 2
LVL 18

Expert Comment

ID: 22793534
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

LVL 18

Expert Comment

ID: 22793573
Object names with spaces imbedded are unnecessary and prone to cause problems and extra work.

Author Comment

ID: 22795779
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?  

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

LVL 18

Accepted Solution

jmoss111 earned 500 total points
ID: 22798217
Save the statement in a query and execute it immediately after the table is built.
LVL 18

Expert Comment

ID: 22814406
What would have been required to get an A from you. Didn't the solution work well for you?

Author Comment

ID: 22814611
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 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!
LVL 18

Expert Comment

ID: 22815224
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.



Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now