By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,476 Members | 1,376 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,476 IT Pros & Developers. It's quick & easy.

Count column in subform?

blyxx86
100+
P: 256
Hey guys, I know it's possible, but I do not know how to do this...

I am looking for a way to count the total number of rows returned in a subform or a query. Similar to the way an Excel spreadsheet has the row numbers, but always starting from 1. I just hate having to physically count the items when someone wants to know how many things were finished or shipped or whatever else.

So, It looks sorta like:
ID Date Cust
005 11/4 Joe
007 11/4 Dave
010 11/5 Joe

I want those values to retun with another field in the front:
Count ID Date Cust
1 005 11/4 Joe
2 007 11/4 Dave
3 010 11/5 Joe

I would like this to be displayed within a subform, but most likely just a query. I tried using an autonumber, but it keeps 'remembering' the last value even if all the data was deleted.
Dec 12 '06 #1
Share this Question
Share on Google+
9 Replies


NeoPa
Expert Mod 15k+
P: 31,186
Hey guys, I know it's possible, but I do not know how to do this...

I am looking for a way to count the total number of rows returned in a subform or a query. Similar to the way an Excel spreadsheet has the row numbers, but always starting from 1. I just hate having to physically count the items when someone wants to know how many things were finished or shipped or whatever else.

So, It looks sorta like:
ID Date Cust
005 11/4 Joe
007 11/4 Dave
010 11/5 Joe

I want those values to retun with another field in the front:
Count ID Date Cust
1 005 11/4 Joe
2 007 11/4 Dave
3 010 11/5 Joe

I would like this to be displayed within a subform, but most likely just a query. I tried using an autonumber, but it keeps 'remembering' the last value even if all the data was deleted.
I'm glad you know it can be done Blyxx86 because I don't ;).
The only way that I know of to handle this at all (and that in a complicated and unreliable manner) is to provide a Public function which can be called one way to reset the count and another way to increment and return that value.
The code, in a standard (non object) module would be something like :
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public Function RowCount(Optional blnReset As Boolean = False) As Long
  5.     Static lngCount As Long
  6.  
  7.     If blnReset Then
  8.         lngCount = 0
  9.     Else
  10.         lngCount = lngCount + 1
  11.     End If
  12.     RowCount = lngCount
  13. End Function
You should call it from code with the blnReset option before expecting it to work correctly in your query.
Expand|Select|Wrap|Line Numbers
  1. Call RowCount(blnReset:=True)
Dec 12 '06 #2

blyxx86
100+
P: 256
I'm glad you know it can be done Blyxx86 because I don't ;).
The only way that I know of to handle this at all (and that in a complicated and unreliable manner) is to provide a Public function which can be called one way to reset the count and another way to increment and return that value.
The code, in a standard (non object) module would be something like :
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public Function RowCount(Optional blnReset As Boolean = False) As Long
  5.     Static lngCount As Long
  6.  
  7.     If blnReset Then
  8.         lngCount = 0
  9.     Else
  10.         lngCount = lngCount + 1
  11.     End If
  12.     RowCount = lngCount
  13. End Function
You should call it from code with the blnReset option before expecting it to work correctly in your query.
Expand|Select|Wrap|Line Numbers
  1. Call RowCount(blnReset:=True)
Well, actually... Does it really need to be a public function? I have a seperate table that information is output to when I run a specific query, so I could just have it reset the boolean row count of a certain autonumber column?

Or is there any way to have a function similar to an excel function in a column?
Then I could just do a =SUM(A1:A1)+1 type of thing (I can't think of what the actual excel function would be, much too tired to think.)
Dec 12 '06 #3

NeoPa
Expert Mod 15k+
P: 31,186
That fundamentally wouldn't work.
If it did you wouldn't even really need to ask the question.
You're welcome to see if you can work out a way.
Don't forget to post the solution here if you find or develop one though ;).
Dec 12 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
Hey guys, I know it's possible, but I do not know how to do this...

I am looking for a way to count the total number of rows returned in a subform or a query. Similar to the way an Excel spreadsheet has the row numbers, but always starting from 1. I just hate having to physically count the items when someone wants to know how many things were finished or shipped or whatever else.

So, It looks sorta like:
ID Date Cust
005 11/4 Joe
007 11/4 Dave
010 11/5 Joe

I want those values to retun with another field in the front:
Count ID Date Cust
1 005 11/4 Joe
2 007 11/4 Dave
3 010 11/5 Joe

I would like this to be displayed within a subform, but most likely just a query. I tried using an autonumber, but it keeps 'remembering' the last value even if all the data was deleted.
In the form footer of the subform put an unbound textbox (txtRecCount) and a command button (cmdGetRecCount). In the code for cmdGetRecCount put the following:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdGetRecCount_Click()
  2.  
  3.    Me.txtRecCount = Me.RecorsetClone.RecordCount
  4.  
  5. End Sub
  6.  
Mary
Dec 13 '06 #5

NeoPa
Expert Mod 15k+
P: 31,186
In the form footer of the subform put an unbound textbox (txtRecCount) and a command button (cmdGetRecCount). In the code for cmdGetRecCount put the following:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdGetRecCount_Click()
  2.  
  3.    Me.txtRecCount = Me.RecorsetClone.RecordCount
  4.  
  5. End Sub
  6.  
Mary
Mary,

Shock Horror!
He's after the ordinal for each record in the dataset!
Dec 13 '06 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
Mary,

Shock Horror!
He's after the ordinal for each record in the dataset!
Original question only asked for a count.

Mary
Dec 13 '06 #7

NeoPa
Expert Mod 15k+
P: 31,186
Shock Horror referred to our shared understanding of why that's not a reasonable expectation. I wasn't being derisory (wouldn't dare).
Original question only asked for a count.

Mary
Although the original post used that term it was just miscommunication from his example output.
Dec 13 '06 #8

blyxx86
100+
P: 256
Shock Horror referred to our shared understanding of why that's not a reasonable expectation. I wasn't being derisory (wouldn't dare).

Although the original post used that term it was just miscommunication from his example output.
But actually... the solution would work. I do not necesarily need a new column if I can get something that would just output the number of records (which is what I'm going after anyways.)

Although I can see a use for resetting the count of an autonumber column, in my current situation, just counting total records would be perfect.

I'm guessing I can post that code and make the value of a textbox display the number.

Again, thank you both - I have no idea what this "Shock Horror" is, but it sounds much like The Rocky Horror Picture Show
Dec 13 '06 #9

NeoPa
Expert Mod 15k+
P: 31,186
Ahh. The problem changes again :(.
In that case a simple record count will do you (and is infinitely easier).
Rocky Horror Picture Show is a favourite classic film of mine.
Shock Horror, in this case though, refers to the earlier request for something which is a no-no in an RDBMS (This was directed at Mary and she would understand :) ).
Dec 14 '06 #10

Post your reply

Sign in to post your reply or Sign up for a free account.