• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 306
  • Last Modified:

Implement a row counter in Report View (A2K7)

Hello, I've got a report that displays several hundred records, and I need to display it in report view, because I've hyperlinked some of the fields, and they are only clickable in report view. All I need to do, is to display a counter field to the left of each record so that the user can keep track of which record they're working on.

The solution I usually use involves VBA code in the detail_format event, but that doesn't work in report view. Surely, there must be a simple way to do this. Maybe a custom column in the query?
0
Jon Jaques
Asked:
Jon Jaques
  • 6
  • 4
1 Solution
 
itsdivyaksCommented:
From the link

http://databases.aspfaq.com/database/how-do-i-return-row-numbers-with-my-query.html

you can get the row number from the query and use this field to populate that cell
0
 
Jon JaquesInformation TechnologistAuthor Commented:
Ugh, I think that's going to be too messy, given the underlying data source of the report:

SELECT tblTrips.PublishToInternet, tblRoomingLists.GroupLeader AS [Group], tblStudentTrip.OnlineSeatWarmer, tblStudentTrip.StudentLast, tblStudentTrip.StudentFirst, tblTripClient.TripClient, tblTripClient.Cancelled, tblTripClient.TripNo, tblTrips.Price, tblClients.Last & IIf(IsNull(tblClients.First),"",", " & tblClients.First) AS SortName, tblTrips.TripName, tblTrips.DepartsOn, tblTrips.ReturnsOn, tblTripClient.TotalCost, tblTripClient.CostToUse, tblTripClient.TotalPayments, [TotalPayments]/[Travelers] AS TotalPaymentsEa, tblTripClient.Balance, [Balance]/[Travelers] AS BalanceEa, tblTripClient.Travelers, tblRoomingLists.StudentTeeShirt, tblRoomingLists.AdultTeeShirt, tblRoomingLists.Permission, tblStudentTrip.EmergencyNo, tblStudentTrip.FamilialRelation, tblTrips.SchoolName, tblClients.Address, tblClients.ZipCode, tblClients.Home, tblTripClient.WaitList, tblClients.ClientNo, tblClients.OnlinePartyID
FROM ((tblTrips INNER JOIN tblTripClient ON tblTrips.TripNo = tblTripClient.TripNo) LEFT JOIN tblClients ON tblTripClient.ClientNo = tblClients.ClientNo) LEFT JOIN (tblStudentTrip RIGHT JOIN tblRoomingLists ON tblStudentTrip.ID = tblRoomingLists.TravelerID) ON tblTripClient.TripClient = tblRoomingLists.TripClient;

Open in new window


All it takes is just a little bit of VBA, as long as you're in the Print Preview mode of the report, I wonder why that's disabled in report view?
0
 
Jon JaquesInformation TechnologistAuthor Commented:
I guess the above query isn't too clear, but it's pulling data from 5 different tables to display trip booking information in one conglomerated report. More specifically, it's being used in this case to report on a problem condition associated with some client accounts whereby they have not selected the traveler for their booking; this is why it is desired to show this data in the "report view", with hyperlinked client names, to make the manual debugging process easier and more efficient for the operators who need to work on correcting these issues.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Jeffrey CoachmanMIS LiasonCommented:
Here is a sample DB illustrating how to count rows (records) in an Access report by using a hidden textbox
with a control source of: =1
...and the running sum property set to: Over All
...and another textbox to display this value

JeffCoachman
0
 
Jeffrey CoachmanMIS LiasonCommented:
sample
db345.mdb
0
 
Jon JaquesInformation TechnologistAuthor Commented:
It works! But, why? LOL! Guess I need to read up on that running sum property!?!?!
0
 
Jeffrey CoachmanMIS LiasonCommented:
It works because the visible control reads the incrementing values in the hidden control...
0
 
Jon JaquesInformation TechnologistAuthor Commented:
So why use a hidden control, would it not also work to display the hidden field?
0
 
Jon JaquesInformation TechnologistAuthor Commented:
Aha, yes, it seems to work just as well by only using the one field, not hidden, with the running sum property set to "Over All"!
0
 
Jeffrey CoachmanMIS LiasonCommented:
<So why use a hidden control, would it not also work to display the hidden field?>
Good question...
LOL

Simple, ...this same technique is used to count the number of Groups on a report.
In that case only the final count is needed so the incremental values need not be shown.

So I just use the same design here to keep things consistient...

;-)

JeffCoachman
0
 
Jon JaquesInformation TechnologistAuthor Commented:
Awesome answer, thank you very very much for your help!

--Jon
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now