Count column in subform?

[blyxx86]

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.

[NeoPa]

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 :

Option Compare Database
Option Explicit
 
Public Function RowCount(Optional blnReset As Boolean = False) As Long
    Static lngCount As Long
 
    If blnReset Then
        lngCount = 0
    Else
        lngCount = lngCount + 1
    End If
    RowCount = lngCount
End Function

You should call it from code with the blnReset option before expecting it to work correctly in your query.

Call RowCount(blnReset:=True)

[blyxx86]

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 :

Option Compare Database
Option Explicit
 
Public Function RowCount(Optional blnReset As Boolean = False) As Long
    Static lngCount As Long
 
    If blnReset Then
        lngCount = 0
    Else
        lngCount = lngCount + 1
    End If
    RowCount = lngCount
End Function

You should call it from code with the blnReset option before expecting it to work correctly in your query.

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.)

[NeoPa]

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 ;).

[MMcCarthy]

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:

Private Sub cmdGetRecCount_Click()
 
   Me.txtRecCount = Me.RecorsetClone.RecordCount
 
End Sub
 

Mary

[NeoPa]

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:

Private Sub cmdGetRecCount_Click()
 
   Me.txtRecCount = Me.RecorsetClone.RecordCount
 
End Sub
 

Mary

Mary,

Shock Horror!
He's after the ordinal for each record in the dataset!

[MMcCarthy]

Mary,

Shock Horror!
He's after the ordinal for each record in the dataset!

Original question only asked for a count.

Mary

[NeoPa]

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.

[blyxx86]

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

[NeoPa]

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 :) ).