Printing first and last record on each page (Access 2K/SR1)

Topic

New Reply

I would like to get some ideas on how I could print first and last record on each page in the Page Section — Sort of like telephone book.

Can anyone suggest an easy solution? grin.

TIA

Reply | Quote

Replies

Check This Thread.

Reply | Quote

Thanks for finding this thread! I couldn’t find it – maybe bad search criteria.

I had tried that code from the Solutions database as well but exact same thing that happened to John happened to me…When I entered all that code…nothing happened. I run Access 2000 just like John. From Charlotte, it sounds like I need to modify my code using slightly different code. I’ll try that and get back to you if I don’t get it to work.

Thanks.

Reply | Quote

Brent,

I’ve used the code and even elaborated on it, but I also know that it can go belly up at times. I’ve had reports stop working and the only thing I could do was copy and paste everything to a new report, which then proceeded to work properly!

Reply | Quote

Yeah…That have happened to me more than once. I tried using Dim dbs as DAO.Database to no avail. I’ll try cutting and pasting to new report and see what happens.

Reply | Quote

Well, cutting and pasting didn’t work. I’m pasting my codes and see if anyone could help.

(Name of Report is “Report1” because I made a copy and I will replace it as soon as I get the darned code working)

Option Compare Database ‘Use database order for sring comparisons.
Option Explicit

‘ Array for last record on each page and last record in recordset.
Dim gLast() As String

‘ Flag for number of passes through report.
Dim gLastPage As Integer

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
‘ During second pass, fill in FirstEntry
‘ and LastEntry text boxes.
If gLastPage = True Then
Reports!Report1!FirstEntry = Reports!Report1!Name
Reports!Report1!LastEntry = gLast(Reports!Report1.Page)
End If

End Sub

Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
‘ During first pass, increase size of array
‘ and enter last record on page into array.
If Not gLastPage Then
ReDim Preserve gLast(Reports!Report1.Page + 1)
gLast(Reports!Report1.Page) = Reports!Report1!Name
End If

End Sub

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

Dim dbs As DAO.Database
Dim rst As Recordset

‘ Set flag after first pass has been completed.
gLastPage = True

‘Open recordset for report.
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(“tblParticipant”)
‘ Move to last record in recordset.
rst.MoveLast
‘ Enter last record into array.
ReDim Preserve gLast(Reports!Report1.Page + 1)
gLast(Reports!Report1.Page) = rst!Name

End Sub

Reply | Quote

I can see one problem right here:

Dim dbs As DAO.Database
Dim rst As Recordset

You need to dim the recordset as a DAO.Recordset to avoid confusing the Access engine unless you’ve turned off the ADO reference.

Reply | Quote