[AccessD] Sequence number in query

Rocky,

I can't remember where I got this...but I used it to generate a number 
sequence in a query...the problem was displaying and scrolling through the 
records...but it did work using an append query and saving the results.  I 
did have to reset the counter after each time, but no big deal.  I just 
called the foolowing function from my query:

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

Hope it helps...

Mark A. Matte

>From: Rocky Smolin - Beach Access Software <bchacc at san.rr.com>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>Subject: Re: [AccessD] Sequence number in query
>Date: Tue, 24 Jan 2006 06:07:16 -0800
>
>Gustav:
>
>I'm trying to create a column for the x axis of a graph, so it's for a
>report.  I think I may do what I usually do which is to whip up some
>code to create a temp table rather than trying to make a query do tricks.
>
>I did use the Qcntr thing and it worked.   Now I've got to start
>fiddling with the chart thingy to see if I can get it to behave.
>
>This is for a Preventive Maintenance application that I am developing in
>partnership with a guy in Bangalore.  There's not a very big market for
>the application but it's fun trying to do a joint venture at that
>distance with someone you've never met.  I actually don't think that the
>graph will be all that useful but it's part of his design so I'm going
>to do it.  He knows the user's needs.
>
>
>Rocky
>
>
>Gustav Brock wrote:
> > Hi Rocky
> >
> > It won't work for a select query you display and browse.
> > Don't know it that is your case.
> >
> > How many records do you have?
> > Did you try out "my" SQL only trick? Not invented by me and not very 
>fast but fast enough for small recordset and very reliable. And no reset 
>code needed.
> >
> > /gustav
> >
> >
> >
> >>>> bchacc at san.rr.com 24-01-2006 02:14 >>>
> >>>>
> > Aha!  Thank you.  I thought there was something one could do in the
> > query like the running sum trick but a function with a global should do
> > nicely.
> >
> > Thanks again
> >
> > Rocky
> >
> >
> > Charlotte Foust wrote:
> >
> >> Take a look at http://support.microsoft.com/?id=94397  "Adding Dynamic
> >> Counter to Query Count Records"
> >>
> >> Charlotte
> >>
> >
> >
> >
>
>--
>Rocky Smolin
>Beach Access Software
>858-259-4334
>www.e-z-mrp.com
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com