[AccessD] ms-access variant of @@rowcount or rowcount

Something I use...but has a problem keeping the numbers as you scroll is 
below.  I use this in a query that populates a temp table...and the numbers 
are always correct and sequential...just not good for a display query.

1.  put the following in a module:

Option Compare Database
Option Explicit
Global lngTableCounter As Long

Function MyAutoCtr(prmAny)
'Trick is to pass a field from the input table(s) so that function called 
for each record
'otherwise Access thinks that the function will always return the same value 
and
'only calls it once, and every output record gets the same value
MyAutoCtr = lngTableCounter
lngTableCounter = lngTableCounter + 1
End Function

2.  In your query use the following field:

Countfld: MyAutoCtr([fldYourPK])

3.  Everytime you need to rerun this query, the following needs to be 
executed somewhere(I put it behind the button that launches my query:

lngTableCounter = 1

I hope this helps.

Thanks,

Mark A. Matte


>From: "Gustav Brock" <Gustav at cactus.dk>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: <accessd at databaseadvisors.com>
>Subject: Re: [AccessD] ms-access variant of @@rowcount or rowcount
>Date: Mon, 04 Dec 2006 16:06:01 +0100
>
>Hi Sad
>
>Look here:
>
>http://databaseadvisors.com/mailman/htdig/accessd/2005-June/035324.html
>
>Generating a rownumber field in a query.
>
>/gustav
>
> >>> accessd666 at yahoo.com 04-12-2006 15:51 >>>
>No that did not do the trick. That would return:
>a 1
>b 1
>
>What i need is:
>a 1
>b 2
>...
>...
>z 26
>
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com

_________________________________________________________________
Stay up-to-date with your friends through the Windows Live Spaces friends 
list. 
http://clk.atdmt.com/MSN/go/msnnkwsp0070000001msn/direct/01/?href=http://spaces.live.com/spacesapi.aspx?wx_action=create&wx_url=/friends.aspx&mk