Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Add a counter on a form

Posted on 2010-09-16
17
Medium Priority
?
474 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 1400 total points
ID: 33697526
see this link


How to Display Line Numbers on Subform Records

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

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 31

Expert Comment

by:hnasr
ID: 33697588
Subform! Not all records!
I am rethinking!
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 31

Expert Comment

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

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 31

Assisted Solution

by:hnasr
hnasr earned 200 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 31

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 400 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 31

Expert Comment

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

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
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…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

604 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