How do I get row numbers shown in an MS Access Query?

[Neil D]

I have a simple Query which just orders some names into descending order
against a value, i.e. highest first.
I have a form (not a report) which has their name shown and then a subform
in which I enter some data against their name.
How do I show their position/row number in the original query (i.e. are they
first, second or 100th in the list) within the form.
Their position will change each month as the values are updated so it is not
a constant.
Thanks for any help.

 

[Tom van Stiphout]

On Fri, 10 Jul 2009 02:34:01 -0700, Neil D <Neil
(e-mail address removed)> wrote:

Reports have specific support for that (see Running Sum in the help
file), but you want this in a subform.
Try this:
In the click event of the button that launches the form, on the first
line (BEFORE you call DoCmd.OpenForm), write:
ResetCounter
We'll implement this routine in a second.

In the query that is the recordsource for the subform add one more
column:
TheCounter: GetNextCounter(myPkValue)
This calls a new function, and passes in the primary key value of each
row. Passing in this value is important, or Access will optimize the
query and call the function only once. We want to call it once for
every row.

In the subform add your counter field, and bind it (= set the
ControlSource) to the new TheCounter column in the underlying query.

In a new standard module write:
(this is the entire text of that module)
Option Compare Database
Option Explicit
dim m_theCounter as Long
public sub ResetCounter
m_theCounter = 0
end sub
public function GetNextCounter(byval vDummy as variant) as long
m_theCounter = m_theCounter + 1
GetNextCounter = m_theCounter
end function

-Tom.
Microsoft Access MVP

 

[Neil D]

Reports have specific support for that (see Running Sum in the help
file), but you want this in a subform.
Try this:
In the click event of the button that launches the form, on the first
line (BEFORE you call DoCmd.OpenForm), write:
ResetCounter
We'll implement this routine in a second.

In the query that is the recordsource for the subform add one more
column:
TheCounter: GetNextCounter(myPkValue)
This calls a new function, and passes in the primary key value of each
row. Passing in this value is important, or Access will optimize the
query and call the function only once. We want to call it once for
every row.

In the subform add your counter field, and bind it (= set the
ControlSource) to the new TheCounter column in the underlying query.

In a new standard module write:
(this is the entire text of that module)
Option Compare Database
Option Explicit
dim m_theCounter as Long
public sub ResetCounter
m_theCounter = 0
end sub
public function GetNextCounter(byval vDummy as variant) as long
m_theCounter = m_theCounter + 1
GetNextCounter = m_theCounter
end function

-Tom.
Microsoft Access MVP

Thanks Tom

It looks very complicated and above my "knowledge level" but I think I can
work through your steps and do it.

One question, to start you say "in the click event of the button that
launches the form" but I use the Swtichboard Manager to create a button to
open the form in edit mode. How do I attach the "ResetCounter" to this button?

Thanks

 

[Tom van Stiphout]

On Fri, 10 Jul 2009 08:43:01 -0700, Neil D

See if you can call it in the Form_Open event.

-Tom.
Microsoft Access MVP

 

[Neil D]

On Fri, 10 Jul 2009 08:43:01 -0700, Neil D

See if you can call it in the Form_Open event.

-Tom.
Microsoft Access MVP

OK, I got all the text entered but cannot get it to work.

Everytime I enter "theCounter: GetNextCounter(myPkValue)" into the query, it
adds hard brackets so I get "theCounter: GetNextCounter([myPkValue])" and
when I try to refresh the query it asks for a value for [myPkValue].

I have entered all the text (hopefully) in the right places but do not know
what to do next.

What am I doing wrong?

Neil D.

 

[John W. Vinson]

Everytime I enter "theCounter: GetNextCounter(myPkValue)" into the query, it
adds hard brackets so I get "theCounter: GetNextCounter([myPkValue])" and
when I try to refresh the query it asks for a value for [myPkValue].

I have entered all the text (hopefully) in the right places but do not know
what to do next.

What am I doing wrong?

Putting a VBA variable into a SQL query.

They're different compartments. The query engine has no way to see the values
of variables in your VBA procedures. You'll need to concatenate *THE VALUE* in
the variable into your SQL string.

 

[Tom van Stiphout]

On Mon, 13 Jul 2009 00:38:01 -0700, Neil D

I'm sorry, I did not add my normal admonition:
(of course you need to replace myObjectNames with yours)

So when I say "myPKValue" I mean that you need to substitute the name
of your primary key column.
Yes, between brackets is fine. Useless if per best practices your
column names don't have spaces or other funny characters.

-Tom.
Microsoft Access MVP

On Fri, 10 Jul 2009 08:43:01 -0700, Neil D

See if you can call it in the Form_Open event.

-Tom.
Microsoft Access MVP

OK, I got all the text entered but cannot get it to work.

Everytime I enter "theCounter: GetNextCounter(myPkValue)" into the query, it
adds hard brackets so I get "theCounter: GetNextCounter([myPkValue])" and
when I try to refresh the query it asks for a value for [myPkValue].

I have entered all the text (hopefully) in the right places but do not know
what to do next.

What am I doing wrong?

Neil D.