Reference Different Details

[shudini]

I have a library database which generates a report of all available books. Is there any way, in the page footer of the report, to display the first and last book listed on the page (much like a dictionary)? Whenever I reference a textbox from the detail section it always returns the value of the initial detail.

Thanks

 

[DrSnuggles]

In your Report code, copy and past this in where necessary.
Create a label on the Page Footer too called lblFooterIndex

The 3 dim statements below stick at the top of your report just after Option Compare Database:

Dim mstrFrom As String
Dim mstrTo_new As String
Dim mstrTo As String


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

'Go through the controls on the Report to assign variables
Call GetPageFooterDetails

'Update the page footer to reflect page details
lblFooterIndex.Caption = mstrFrom & " - " & mstrTo

End Sub


Public Function GetPageFooterDetails()
' This function gets footer details for each page


Dim ctl As Control

'Assign last value of control before updated
'For some reason Access picks up the next page value
mstrTo = mstrTo_new

'Loop through each control and assign pertinant footer values
For Each ctl In Detail.Controls

'Find control with text we want to assign in footer

If ctl.Name = ">Name of Control/Field you want to display in Footer<" Then
'Only assign this once
If mstrFrom = "" Then
mstrFrom = ctl.Value
End If
'Assign for every instance of control
mstrTo_new = ctl.Value

End If
Next

End Function

Private Sub Report_Page()

'Set variable to "" for each page before populated by function GetPageFooterDetails
mstrFrom = ""


End Sub


------------------------------------------

Now this does work, but unfortunately for some reason Access Reports look at the next page details too. Thus I have to read the penultimate field value.
The only knock on effect is that . . . well, you've guessed it, at the end of the report.

If anyone else knows why Access looks at the next page Detail's first line too, do then please inform me!!

 

[shudini]

That was clever! Now we just have to figure out what to do on the last page. Once we're dealing with the last page, maybe i can just do a lookup for the last value in the recordset.

Thanks!

 

[shudini]

I actually got it working on the last page by cheating a little -

I already have the page # of total pages displayed, so i added in the code the following at the end of your GetPageFooterDetails function:

If Report.Page = Report.Pages Then
    mstrTo = ctl.Value
End If

This way it is forced to take the last value if it is the last page.

Thanks again!