Solved

Add a counter on a form

Posted on 2010-09-16
17
444 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 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

786 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