Create a dynamic index for a report?

[swamprat96]

Last task for the day- if possible. I've never done this before for a report

I have an item report that now page breaks by category thanks to this forum. If possible I'd like to generate an index by category for the report. It can be a separate report. I tried the microsoft suggestion but struggled with the module- as usual their code would not compile!

I am still using access 2003 for this- apologies

 

[vbaInet]

Are you talking about:

Page 1 of 20

for example?

 

[swamprat96]

No- I mean a one page report that says
Accessories- Page1
Ball sets - Page 5
Trees - Page 10

and so on.

So I need to pick up the starting page for each category so that I can create a Table of contents for want of a better description

Cheers

Tony

 

[vbaInet]

This isn't easily achievable in Access

 

[swamprat96]

yep I gathered that- thanks. Reading the microsoft docs now but looks tough

 

[vbaInet]

You would need to do some coding for that to work. Good luck!!

If you manage to pull together something and are stuck, post back.

 

[swamprat96]

I just had a quick and dirty idea

There are a maximum of 8 records per page. I can write a query to work out how many items are in each category. The report displays by category alphabetically so I know what category comes after each one

The first category has 13 records. This means that I know the next category will start on Page 3. Based on the number of records in each category I know what the page numbers will be.

I just have to work out the final query

 

[swamprat96]

Nearly there. I'm trying to use Dsum to make a running total of the field "Rounded Count" by "Category" in the following Query code - but its giving me weird numbers that don't make sense.

Any obvious mistake here?

SELECT Qpagenumber1.[Category Field], Qpagenumber1.[Rounded count], DSum([Rounded count],"Qpagenumber2") AS RunningTotal
FROM Qpagenumber1
GROUP BY Qpagenumber1.[Category Field], Qpagenumber1.[Rounded count];

 

[pbaldy]

I've never needed this, but it sounded a little intriguing. I had a report that breaks on customer. I put a textbox in the report footer and then this in the group header format event:

Me.Text182 = Me.Text182 & Me.CustomerName & " - page " & [Page] & vbCrLf

Quick and dirty, but it did produce a list of customers and the first page of their data along the lines of your request.

 

[swamprat96]

Hi paul,
Sounds good- I'll have a play in the morning

 

[DCrake]

I did this many moons ago, and if my memory serves me right what I did was to create an identical report but kept the detail section hidden Put the count of pages in the section footer along with the description. I then ran this report to get the TOC. When worked ok and I got the results I wanted I then added this report as a sub report in the original report in the report header section. That's the general gist of how it worked, I think.

 

[gemma-the-husky]

slight alternative

the access cookbook gives a method of putting the item range on each page

so

page 1 - A - C
page 2 - C - F
page 3 - F - L

etc. Is that idea any good?

 

[vbaInet]

I thought this was quite an interesting issue, I did a bit of playing around and built up something. It does what you've asked. The idea is:

1. Create a new table with three fields. First is the PK, Category Name you would like to show and the Page no. The first two must be set up with the same data types as that of the Categories table.
2. You run the report that lists your items and groups by Category. This report generates records of the page numbers associated to each grouping.
3. You would also need to create another report to display what has now been inserted into the new table.

Have a look at the attached for a clearer understanding.

NB: It's saved in Access 2000 so most people can use it.

 

[swamprat96]

having a play with this now- thanks!

 

[vbaInet]

Let us know if it works for you.

 

[stopher]

Great stuff vbaInet. A couple of questions/discussion points...

In the IF statement, why do you test against a previous group? Since the code is in the GroupHeader, then the group must be new i.e. no need to test. There is a caveat to this where a new header attempts to fit on the bottom of a page but it can't fit so then prints on next page. But since the OP is forcing a new page then that's not a problem here.

You use: If Me.Page = Me.Pages Then stopChaptersBuild = True
I think this is unnecessary. You can use the test me.pages=0 to test if you need to keep creating the contents or not. me.pages is only assigned a non-zero value once the report has determined the number of pages.

So the form code becomes (note no globals required):

Option Compare Database
Option Explicit

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
On Error Resume Next    ' Needed.

    ' Inserts the page number of each unique item found.
    If Me.Pages = 0 Then
        CurrentDb.Execute "INSERT INTO tblChapters ([ItemGroupID], [ItemName], [ItemPageNo]) " & _
                            "VALUES(" & Me.ItemID & ", '" & Me.ItemName & "', " & Me.Page & ");"
    End If

End Sub

Private Sub Report_Load()
    ' Initialises the variable and Deletes the contents of tblChapters
    CurrentDb.Execute "DELETE FROM tblChapters"
End Sub

One further point, I guess the OP wants to put the TOC at say the first page in the same report. As you mention, this can be added as a sub-report. However, we could do away with storing the TOC to a table and using a sub-report but just building up the TOC in a variable (using vbCrLf etc) then sticking the variable into the assigned TOC textbox. The following code would be added to the report header On Format event:

IF me.pages<>0 then
    me.TOCtextbox=strTOCvariable
ENDIF

Chris

 

[vbaInet]

Hello Chris. Great analysis there. It was just an idea I was playing with which took a few minutes to do. I knew it could do with further refinement (as you've done).

The if me.pages ... stopChaptersbuild was to ensure that code ran only once since there was an INSERT being performed. Once a report is opened and you move to the next record the Format event is fired again, so just didn't want that happening.

You've given some food for thought for the OP and anyone else reading this thread. Thanks