Solved

Add a counter on a form

Posted on 2010-09-16
17
468 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 3
  • +1
17 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 350 total points
ID: 33697526
see this link


How to Display Line Numbers on Subform Records

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

Expert Comment

by:hnasr
ID: 33697583
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
ID: 33697588
Subform! Not all records!
I am rethinking!
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 30

Expert Comment

by:hnasr
ID: 33697598
Not working as expected!
0
 
LVL 30

Expert Comment

by:hnasr
ID: 33697660
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
ID: 33697699
Check this model database.

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

Expert Comment

by:Jeffrey Coachman
ID: 33698049
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
ID: 33700570
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33700679
if you will visit the link i posted you will see the function used

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

0
 

Author Comment

by:pdvsa
ID: 33701096
OK thanks...have not had time...
0
 
LVL 30

Expert Comment

by:hnasr
ID: 33702093
"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
ID: 33705449
<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
ID: 33709519
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
ID: 33709547
Thk there is an issue w ee mobil
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33712251
ok
0
 
LVL 30

Expert Comment

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

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

690 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