Report index? 1

[weeze2]

Hi is it possible to automatically create an index to a report?? For example :

A......page 1
B......page 4
C......page 7

Any ideas??

 

[PaulBricker]

It can't be done with something like a wizard, but it can be done in VBA. What did you have in mind.

Paul

 

[weeze2]

Well I have about 1500 names and they will come alphabetiaclly (8 on each page).

I thought about having an "index page" with a - z and the page number where each letter starts.

 

[PaulBricker]

OK here are the steps. You will have to create a seperate report to hold the Index. I'll call it "MyReport_Index"

1. Create a table to hold the index information, I'll call it tblIndex. It should have two fields. IndexLetter and PageNumber. You can make up your own names but these are what I will use. IndexLetter is datatype text and PageNumber is datatype Number, Long Integer or Integer. Set both of the Fields as the Primary key. To do that, you highlight both fields in the table and then click on the Primary Key button on the Toolbar.
2. Create a query using tblIndex, I'll call it qryIndex. Turn on the Totals, the Greek looking E button, and GroupBy IndexLetter and set PageNumber to Min.
3. Create a delete query that will delete the information from tblIndex, I'll call it qryDeletetblIndex. Post back if you have question about doing this.
4. In the Open Event for the Report add this code.

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDeletetblIndex"
DoCmd.SetWarnings True

5. In the Format Event for the Detail Section of the Report put this code. Substitues your textbox name for the textbox I have in BOLD
Dim strSQL as String
strSQL = "Insert Into tblIndex(IndexLetter, PageNumber) Values ('" & Left(LastNameField,1) & "', " & Page & &quot"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

6. In the PageFooter Print Event put this code. Again, adjust the names in BOLD to your report names.

If Page = pages Then
If SysCmd(acSysCmdGetObjectState, acReport, "MyReport_Index") Then
DoCmd.Close acReport, "MyReport_Index"
End If
DoCmd.OpenReport "MyReport_Index", acViewPreview
End If

7. "MyReport_Index" will use qryIndex as the Record Source.
8. To run the Index report, you have to open the main report and then move to the last page. This will fire the Print Event and run the Index report.

That should get you close.

Paul

 

[weeze2]

Thanks!!

 

[weeze2]

This worked 100%
But if I now want to get all the names to stand in an index with the page they occur on, is it just a simple change?

I tried to modify the code for example

('" & Left(LastNameField,1) & "', " & Page & &quot"
to
('" & LastNameField & "', " & Page & &quot"

and then removed the keys in the table and the min in the query.
But what I then get is too many intsances (more than there are in the original table). I can not see what the problem is. It seems as if an instace occur on 2 pages if it stands as the first on a new page. (If it is the first on a new page of the report, it is written 2 in the table)

any ideas??

 

[PaulBricker]

The detail format event runs a number of addtional times when the report is opened so it ends up writing values to the table we don't need, as you have found out. You would have to establish a primary key for each person, maybe First, Middle Initial, Last or use an Autonumber (sorry to all the purists but autonumber can be useful). Something that will uniquely identify your people. The textbox that contains this number doesn't necessarily have to be seen in the report. You could put a textbox in your report, name it myUniqueVal, set it's Control Source to =1, the Running Sum property to Over All and then add this as the primary key to your table. It should work just like an Autonumber but without actually adding it to your main information table. You would need to adjust your Insert Into statement to include this number. Let me know which way you want to try and I'll do what I can to help.

Paul

 

[weeze2]

Hi thanks for the reply

I sort of got the report to work and changed the code a bit like this
**
Private Sub Detaljesektion_Format(Cancel As Integer, FormatCount As Integer)
Dim strSQL As String
strSQL = "Insert Into Efternavn_Page(Id, Efternavn, Navn, Pagenr) Values(Id, Efternavn, Navn, Page)"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End Sub
**

There is eight names on a page in the main report. But in the index there is always nine. It seems as if the first name on a new page still have the Page as the previous page number. It is only the second element on a page that in the Index report gets a higher page nr.

How can one fix this?

Thanks for the help.

 

[PaulBricker]

You are using the ID, but you may need to set all the Fields in the Table as your Key and then use the Max of Page number in your query to return the correct value. Without seeing the actual data it's hard to say what might be the solution. If you could post a sample of what is being returned that would help. Or you could email me a sample at pbricker@attbi.com and I can look at it. I'm going out for a couple hours but will check back in when I return.

Paul