Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

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

0
gkadell
Asked:
gkadell
1 Solution
 
nico5038Commented:
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)
0
 
gkadellAuthor Commented:
Thanks - I haven't had a chance to try it, but I will tomorrow.
0
 
DSSCommented:
This will work too...

1.  Put this code in a module:

Global NextSeqVar As Long

Function NextSeq() As String
    NextSeq = NextSeqVar + 1 & ")"
    NextSeqVar = NextSeqVar + 1
   
End Function

2. Put this line of code in the onLoad event of the form you are designing:

NextSeqVar = 0

3. Put this as the Control Source of the field that will display the record number:

=NextSeq()



0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
gkadellAuthor Commented:
Thanks, I'll get back to you guys soon...
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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.
0
 
gkadellAuthor Commented:
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.
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

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

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