Problem with a Report Index

G

Guest

I am definitely in over my head with this, and would greatly appreciate any
help.

As a volunteer for our local historical society, I have created a database
(Access 2000) for our cemetery, and a report of the names sorted by section,
row, and family plot name. Now I need an index to find the names (16,000+) in
this report.

Using this Microsoft article as a starting point:
http://support.microsoft.com/?kbid=210269 (How to Create a Table of Contents
or Index for a report), this is what I have:

I created a table: TableOfContents
Field Name: FullName
Data Type: Text
Indexed: Yes (No duplicates}
Field Name: PageNumber
Data Type: Number/Long Integer
Indexed: No

Here is the module:

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

qd.Execute
qd.Close

Set TocTable = db.OpenRecordset("TableOfContents", dbOpenTable)

TocTable.Index = "FullName"
End Function

Function UpdateToc(TocEntry As String, Rpt As Report)
TocTable.Seek "=", TocEntry

If TocTable.NoMatch Then
TocTable.AddNew
TocTable!ID = TocEntry
TocTable![PageNumber] = intPageCounter
TocTable.Update
End If
End Function
Function UpdatePageNumber()
intPageCounter = intPageCounter + 1
End Function
-------------
I set OnOpen of rptCemeteryListing: =InitToc()
I set OnPrint of the header just above the listings for the names:
=UpdateToc([FullName],[Report])
I set OnPrint of the page footer: =UpdatePageNumber()

{One side note -- the text box on my report that I named FullName is
actually a formula of 4 fields. This is the information I am trying to get
into the Table of Contents field also named FullName.}

When I run the report, I get error message saying "FullName is not an index
in this table." I accidentally changed the code above to TocTable.Index =
"LName" (LName is a field in the formula to create FullName. It is also the
name of a field in a text box for the family plot name.

I'm stumped. Thank you so much for any help or advice, even bad jokes
comparing Access projects to tombstones.
 
G

Guest

Big apology -- I didn't finish the last paragraph before I posted. Here is my
complete question.
 
M

Marshall Barton

Carol said:
I am definitely in over my head with this, and would greatly appreciate any
help.

As a volunteer for our local historical society, I have created a database
(Access 2000) for our cemetery, and a report of the names sorted by section,
row, and family plot name. Now I need an index to find the names (16,000+) in
this report.

Using this Microsoft article as a starting point:
http://support.microsoft.com/?kbid=210269 (How to Create a Table of Contents
or Index for a report), this is what I have:

I created a table: TableOfContents
Field Name: FullName
Data Type: Text
Indexed: Yes (No duplicates}
Field Name: PageNumber
Data Type: Number/Long Integer
Indexed: No

Here is the module:

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

qd.Execute
qd.Close

Set TocTable = db.OpenRecordset("TableOfContents", dbOpenTable)

TocTable.Index = "FullName"
End Function

Function UpdateToc(TocEntry As String, Rpt As Report)
TocTable.Seek "=", TocEntry

If TocTable.NoMatch Then
TocTable.AddNew
TocTable!ID = TocEntry
TocTable![PageNumber] = intPageCounter
TocTable.Update
End If
End Function
Function UpdatePageNumber()
intPageCounter = intPageCounter + 1
End Function
-------------
I set OnOpen of rptCemeteryListing: =InitToc()
I set OnPrint of the header just above the listings for the names:
=UpdateToc([FullName],[Report])
I set OnPrint of the page footer: =UpdatePageNumber()

{One side note -- the text box on my report that I named FullName is
actually a formula of 4 fields. This is the information I am trying to get
into the Table of Contents field also named FullName.}

When I run the report, I get error message saying "FullName is not an index
in this table." I accidentally changed the code above to TocTable.Index =
"LName" (LName is a field in the formula to create FullName. It is also the
name of a field in a text box for the family plot name.

I'm stumped. Thank you so much for any help or advice, even bad jokes
comparing Access projects to tombstones.
Click to expand...


That error message usually means what it says. Double
check the TOC table to make sure the ***index*** name is the
same as the field name (View Indexes menu item).

I think you forgot to change the ID field name to FullName.
In addition. the code in that KB article is rather crude,
especially where you can use the report's Page property
instead of calculating it. You can simplify things a fair
amount:

Dim db As DAO.Database
Dim TocTable As DAO.Recordset

Function InitToc()
Set db = CurrentDb()
db.Execute "Delete * From [TableOfContents]"
Set TocTable = _
db.OpenRecordset("TableOfContents",dbOpenTable)
TocTable.Index = "FullName"
End Function

Function UpdateToc(TocEntry As String, Rpt As Report)
With TocTable
.Seek "=", TocEntry

If .NoMatch Then
.AddNew
!FullName= TocEntry
![PageNumber] = Me.Page
.Update
End If
End Function
 
G

Guest

Problem solved! Thanks again, Marshall. Your code is a definite improvement.
I also discovered that you have to physically page through every page of the
report to populate the table of contents. The directions from Microsoft tell
you to do that, but don't really explain that it is necessary.

Carol

Marshall Barton said:
Carol said:
I am definitely in over my head with this, and would greatly appreciate any
help.

As a volunteer for our local historical society, I have created a database
(Access 2000) for our cemetery, and a report of the names sorted by section,
row, and family plot name. Now I need an index to find the names (16,000+) in
this report.

Using this Microsoft article as a starting point:
http://support.microsoft.com/?kbid=210269 (How to Create a Table of Contents
or Index for a report), this is what I have:

I created a table: TableOfContents
Field Name: FullName
Data Type: Text
Indexed: Yes (No duplicates}
Field Name: PageNumber
Data Type: Number/Long Integer
Indexed: No

Here is the module:

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

qd.Execute
qd.Close

Set TocTable = db.OpenRecordset("TableOfContents", dbOpenTable)

TocTable.Index = "FullName"
End Function

Function UpdateToc(TocEntry As String, Rpt As Report)
TocTable.Seek "=", TocEntry

If TocTable.NoMatch Then
TocTable.AddNew
TocTable!ID = TocEntry
TocTable![PageNumber] = intPageCounter
TocTable.Update
End If
End Function
Function UpdatePageNumber()
intPageCounter = intPageCounter + 1
End Function
-------------
I set OnOpen of rptCemeteryListing: =InitToc()
I set OnPrint of the header just above the listings for the names:
=UpdateToc([FullName],[Report])
I set OnPrint of the page footer: =UpdatePageNumber()

{One side note -- the text box on my report that I named FullName is
actually a formula of 4 fields. This is the information I am trying to get
into the Table of Contents field also named FullName.}

When I run the report, I get error message saying "FullName is not an index
in this table." I accidentally changed the code above to TocTable.Index =
"LName" (LName is a field in the formula to create FullName. It is also the
name of a field in a text box for the family plot name.

I'm stumped. Thank you so much for any help or advice, even bad jokes
comparing Access projects to tombstones.
Click to expand...


That error message usually means what it says. Double
check the TOC table to make sure the ***index*** name is the
same as the field name (View Indexes menu item).

I think you forgot to change the ID field name to FullName.
In addition. the code in that KB article is rather crude,
especially where you can use the report's Page property
instead of calculating it. You can simplify things a fair
amount:

Dim db As DAO.Database
Dim TocTable As DAO.Recordset

Function InitToc()
Set db = CurrentDb()
db.Execute "Delete * From [TableOfContents]"
Set TocTable = _
db.OpenRecordset("TableOfContents",dbOpenTable)
TocTable.Index = "FullName"
End Function

Function UpdateToc(TocEntry As String, Rpt As Report)
With TocTable
.Seek "=", TocEntry

If .NoMatch Then
.AddNew
!FullName= TocEntry
![PageNumber] = Me.Page
.Update
End If
End Function
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Code Help: Table of Contents 5
db As DAO.Database gives me errors 2
page numbering -- continue from previous post 2
How can I get a table of contents 4
CreateQueryDef question 4
Open a report to specific data 2
Open report from a prompt form 4
Excel VBA PivotItems Problem 0

Top