Public function that will increment a number in a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I need to know if there is a way to program a function that will be called
from a select query to number each record in the query from 1 to the end of
the records pulled.

As an example I will be using a table with 1 field called client.
I would like to run a select query with the field client and a field with
the function.
I would like the first record pulled to be named 1, the second record pulled
to be named 2, and so on to the end of the records pulled.

I hope I was clear enough to explain the situation, I'm learning vba, but
haven't learned enough to give you an example of what I want to do, because I
don't know how to.

Waylen...
 
Waylen, there is no simple way to get that incrementing number in a query.

It's easy enough in a form or report. See:
Numbering Entries in a Report or Form
at:
Numbering Entries in a Report or Form

If you must do it in a query, use a subquery to count the number of
preceeding entries. If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

If the query must be editable, use DCount() instead of the subquery. It will
be much slower.

Both the subquery and the DCount() expression must be re-crafted if the
query is filtered or sorted differently, and unless the expressions include
the primary key, they have problems with counting records that are tied.

If you know for sure that the query will call every record, in turn, you can
craft a function that retains a static variable to count, e.g.:
Function GiveCount(varID As Variant) As Long
Static lngCount As Long
lngCount = lngCount + 1&
GiveCount = lngCount
End Function
But there are several problems with implementing this approach. The first is
that Access won't call the function for every row, unless it has to pass
something different in. Hence you must pass something like a primary key
field in just to get the query optimizer to call the function on each row,
even though the function doesn't do anything with the argument passed in.

But the more difficult problem is that JET may not call the function in the
order of the records in the query. For example, say the query returns 5000
records. When you view your query, say the first 30 show on screen at once,
and the numbers are correct. Now you click the navigation button at the foot
of the data sheet and jump to the last record. Access may not call the
function for every row in between, i.e. it only figures the calculated
fields as needed. And since the function was not called for the intervening
rows, the count is not correct.

(Of course, you also have to figure how/when to reset your static variable.)

So, the simplest solution is to append the records to a temporary table that
has an AutoNumber field. That gives you the counter. Next time round, you
delete the previous records:
dbEngine(0)(0).Execute "DELETE FROM Table1;", dbFailOnError
and reset the AutoNumber:
http://allenbrowne.com/func-DDL.html#AdjustAutoNum
or:
http://allenbrowne.com/func-ADOX.html#ResetSeed
 
Allen & Alex,

Thank you so much for all of the examples. Allen, thank you so much for the
examples and the insight to my problem. I was really starting to develop low
self esteem on this problem. I'm new to vba, and it appears that I've chosen
a pretty hard problem to solve using it. It's good to know that this isn't a
simple problem, because I figured it was very simple to solve. I've been
working on this for 6 weeks in my spare time away from work. Which hasn't
been all bad, I've learned a lot!

I will use your example of appending to a table and resetting the autonumber
field each time since you recommend this as the safest solution.

Thank you so much for everything!

~Waylen
 
Back
Top