?
Solved

Line Number in a form

Posted on 2002-06-28
6
Medium Priority
?
265 Views
Last Modified: 2006-11-17
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
Comment
Question by:gkadell
6 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 7117152
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
 

Author Comment

by:gkadell
ID: 7118988
Thanks - I haven't had a chance to try it, but I will tomorrow.
0
 
LVL 2

Accepted Solution

by:
DSS earned 800 total points
ID: 7119070
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 

Author Comment

by:gkadell
ID: 7121901
Thanks, I'll get back to you guys soon...
0
 
LVL 59
ID: 7133583
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
 

Author Comment

by:gkadell
ID: 7178469
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Implementing simple internal controls in the Microsoft Access application.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question