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?
LVL 3
Jon JaquesInformation TechnologistAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.