Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Index problem in access

Posted on 2008-10-23
7
Medium Priority
?
492 Views
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?

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
Comment
Question by:egj75
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
7 Comments
 
LVL 18

Expert Comment

by:jmoss111
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

0
 
LVL 18

Expert Comment

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

Author Comment

by:egj75
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?  

Thanks.
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 18

Accepted Solution

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

Expert Comment

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

Author Comment

by:egj75
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 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
 
LVL 18

Expert Comment

by:jmoss111
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.

Regards,

Jim
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

721 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