Add a counter on a form

I have a form with sub in datasheet.
I need to add number for each of these rows (like a counter)
I could do this in a report with no issues but it does not seem to work in a form.
(add a unbound txtbox, put =1 in control source, format of over group.)

How can I add this type of counter on a form?  I am thinking I can add a text box or something directly on the form and not add a field in the table.  

thank you
pdvsaProject financeAsked:
Who is Participating?
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
see this link

How to Display Line Numbers on Subform Records
Have a textbox in the subform:
Set it's control source to:

=DCount("f1","e","f1<='" & [f1] & "'")

In this example, table: e, primary key: f1
Subform! Not all records!
I am rethinking!
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Not working as expected!
Well another try:

Main form-Record source table e (f1, f2, ...) -primary key f1

Sub form -Record source table e1 (f1, f2,...) - primary key f1, f2
Link Master Fields: f1
Link Child fields: f1

Text Box on subform:
Control source:

=DCount("f2","e1","f2<='" & [f2] & "' AND f1 = '" & [parent].[f1] & "'")
hnasrConnect With a Mentor Commented:
Check this model database.

Jeffrey CoachmanMIS LiasonCommented:
You can also add the line number to the subform's underlying recordset, then add this "Numbering" field to the form.
The advantage here is that now you can have numbering in the base query.
Then the line numbers can be used as normal field in any form or reports (without having to be recreated)

See here for links that demonstrate how this can be done:


pdvsaProject financeAuthor Commented:
Cap:  will need some time to go over that.  
hnasr:  I dl and do see it counts with the dcount fx.  Will need some time to figure how to implement in db.
boag:  that sounds good.  Not sure if same as in Cap's link.  

I thought this would be  simple solution but requires a little bit of reading.  I was hoping to avoid functions

boag:  I read a little of link.  Accepted solution was:

GetLineNumber() is written to be used within a form, not from a query.

In your suform, add a text control to the form for the line number and set its controlsource to:



Is this all it takes?  Coudl not read all of it.  I see you responded after Jim but not time.  
Rey Obrero (Capricorn1)Commented:
if you will visit the link i posted you will see the function used


pdvsaProject financeAuthor Commented:
OK thanks...have not had time...
"hnasr:  I dl and do see it counts with the dcount fx.  Will need some time to figure how to implement in db. "

It is simple:
Main form recordsource tblMani (pk, other fields)
Sub form recordsource tblSub (fk, f2, other fields) ; fk is foreign key that refers to pk, fk, f2 form primary key for tblSub

Master link fields: pk
Child link fields: fk

txtCounter control source: =DCount("","tblSub","f2<='" & [f2] & "' AND fk = '" & [parent].[pk] & "'") refers to the pk of tblMain, parent is used because the code is in the subform and the main form is referred to as its parent. The code in the subform refers to a field pk in its parent form as
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
<boag:  I read a little of link. >
The link I posted was a "Link" to "ALL" questions here about adding line numbers to a query.
So I don't know which one of those links specifically you are referring to.

But the basic SQL Syntax to do this is:
SELECT (Select Count(*) FROM YourTable AS b WHERE b.KeyField<=a.KeyField) AS LineNumber, *
FROM YourTable AS a;

However this creates a Non-updateable query.  All this means is that you must add your records to the source table directly. It also means that (again, depending you your sorting and Key fields) this may, or may not be sequentially numbered in the "subform"
From your post, it cannot be determined if this is a consideration...

Something else that is not quite clear, based on your original request:
>I have a form with sub in datasheet.
>I need to add number for each of these rows (like a counter)

... here it is not clear if you want the subform counter to reset to 1 for each main record.
meaning each set of subform records starts with 1
Or if the "All" related Child records will simply be sequentially numbered.

But any of these techniques will have drawbacks and sorting considerations.
Remember, these techniques will insert these number on the fly, they are not stored anywhere.

So my question is why the sequential number need to be displayed in the form?
My other question is why the Primary key field in the Child table cant be the "Line Number"?

As you stated yourself, this is much more easily done and controlled in a report than it a query.
So why is it not practical to do it there?

In other words, can you simply provide a sample of the source data and a "Graphical" example of the exact expected output (based on the sample data) so we are not forced to guess and what you are needing exactly.


pdvsaProject financeAuthor Commented:
Boag:  I see the LIne number on the form.  It is numbering sequentially.  You mentioned a qry not being updatable but this is OK.  YOu mentioned that it is sort on the fly which is OK.  The  line no is only for this form and I dont need a report.   The explanation is good and I appreciate it.  I will keep it in mind if I need to have a Line No. in the qry but right now I do not need this.

Cap:  this was what I was looking for.  

thank you.  
pdvsaProject financeAuthor Commented:
Thk there is an issue w ee mobil
Jeffrey CoachmanMIS LiasonCommented:
Did you try my comment ID:33702093?
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.

All Courses

From novice to tech pro — start learning today.