asked on

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

jmoss111

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

jmoss111

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

egj75

ASKER

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.

ASKER CERTIFIED SOLUTION

jmoss111

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jmoss111

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

egj75

ASKER

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!

jmoss111

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