Solved

Add a counter on a form

Posted on 2010-09-16
17
428 Views
Last Modified: 2013-11-28
Experts,
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
0
Comment
Question by:pdvsa
  • 7
  • 4
  • 3
  • +1
17 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 350 total points
Comment Utility
see this link


How to Display Line Numbers on Subform Records

http://support.microsoft.com/?kbid=210340
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
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
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
Subform! Not all records!
I am rethinking!
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
Not working as expected!
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
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] & "'")
0
 
LVL 30

Assisted Solution

by:hnasr
hnasr earned 50 total points
Comment Utility
Check this model database.

Add-counter-to-subform.mdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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:
http://www.google.com/search?q=site%3Awww.experts-exchange.com+microsoft+Access+add+line+numbers+to+a+query

;-)

JeffCoachman
0
 

Author Comment

by:pdvsa
Comment Utility
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:
JDettman:

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:

=GetLineNumber(Form,"dfID",[dfID])

JimD.

Is this all it takes?  Coudl not read all of it.  I see you responded after Jim but not time.  
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
if you will visit the link i posted you will see the function used

=GetLineNumber(Form,"ID",[ID])

0
 

Author Comment

by:pdvsa
Comment Utility
OK thanks...have not had time...
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
"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] & "'")

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 parent.pk.
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 100 total points
Comment Utility
<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.

JeffCoachman



Access-EEQ26479555LineNumbersInS.mdb
0
 

Author Comment

by:pdvsa
Comment Utility
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.  
0
 

Author Comment

by:pdvsa
Comment Utility
Thk there is an issue w ee mobil
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
ok
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
Did you try my comment ID:33702093?
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

762 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now