• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 498
  • Last Modified:

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

0
egj75
Asked:
egj75
  • 5
  • 2
1 Solution
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
jmoss111Commented:
Save the statement in a query and execute it immediately after the table is built.
0
 
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now