asked on

Line Number in a form

Hi everyone, I am working on an Access 2000 training database. In the main form I have an employee and in the subform I have a list of training needed. I want to number the rows returned in the subform:
1) class
2) class
3) class. etc.

How do I put the numbers and ")" in the form? Thanks for your help.

Gladys

nico5038

In case your Training table has an autonumber, just add in the query editor a field like:

SeqNo:DCOUNT("[TrainingID]","tblTraining","EmployeeID="&[EmployeeID]&" AND TrainingID<="&[TrainingID]) & ") "

This will count the number of autonumberID's less or equal the current, thus numbering from 1 to ... and the ") " will add the needed trailing ")"

Just make sure the tablename and fieldnames are filled in correctly.

Nic;o)

gkadell

ASKER

Thanks - I haven't had a chance to try it, but I will tomorrow.

ASKER CERTIFIED SOLUTION

DSS

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gkadell

ASKER

Thanks, I'll get back to you guys soon...

Jim Dettman (EE MVE)

And as a 3rd alternative, you can add a field to the underlying recordsource (table) to hold the "row display" value.

You may want to do this as the last solution posted will change the row numbers if the user is able to scroll in the subform.

Nico's solution avoids this, but the DCount() will be slow if the recordset is large.

If you go this route, you can use the last solution posted as part of an update query to update the field, then display the subform. That should be fast and efficent and avoid the scrolling problem.

Jim.

gkadell

ASKER

Thank you all very much for your input. It took me a little while to test all of your options, and I sincerely apologize for the delay. Nico, the only reason your answer didn't work was because one of my fields was a text field and the others were integers - and I received mismatched field errors. DSS your answer worked great.