Assigning sequential numbers

[John]

Hi

I need to assign sequential invoice numbers to orders starting from the last
highest number + 1. I have tried the following code;

UPDATE Orders SET Orders.[Invoice No] = DMax("[Invoice No]","Orders")+1
WHERE Orders.[Invoice No]) Is Null AND ...

The problem is that all orders get the same number which is the last highest
number + 1. Apparently the query does not recalculate DMax("[Invoice
No]","Orders")+1 for each record and instead only gets the value once in the
beginning and assigns this same value to all the records. How can I make it
work?

Thanks

Regards

 

[Jeff Boyce]

John

Are you trying to do this "in bulk"? How many do you have to update? It
might be faster to just open the table and start typing unless you have
several hundred or more...

The DMax() + 1 approach works great ... when the record is being saved.
There's no reason it would update (itself) after each row is updated. Look
to add this function as part of how you save a new record (i.e., via a
form).

Regards

Jeff Boyce
<Office/Access MVP>

 

[Guest]

John:

To do this en bloc you'll need a column in the table which uniquely
identified each row's position in the desired order. An autonumber column
might do, but that only guarantees uniqueness not necessarily the position in
the order. The best thing for this is a Date/Time column with unique values,
usually set automatically by making its DefaultValue property Now() in table
design. So if you have one, or any other column which uniquely determines
the order, use that (called DateTimeStamp below). If not, use an autonumber
column if you have one. If you don't then you can add an autonumber to the
table and use that in the UPDATE query below, but the numbers added to the
table, while sequential, won't necessarily follow a logical order.

The UPDATE query would then go like this, using a subquery to count the rows
up to and including the current row and adding that to the highest existing
Order No:

UPDATE Orders AS O1
SET O1.[Invoice No] =
(SELECT COUNT(*)
FROM Orders AS O2
WHERE O2.DateTimeStamp <= O1.DateTimeStamp
AND O2.[invoice No] IS NULL
AND…) +
(SELECT MAX([Invoice No])
FROM Orders)
WHERE O1.[Invoice No]) IS NULL
AND ...;

As you see the WHERE clause of the first subquery matches that of the outer
query, but also has the additional criterion O2.DateTimeStamp <=
O1.DateTimeStamp which correlates the subquery with the outer query, using
the aliases O1 and O2 to distinguish the separate instances of the Orders
table.

When inserting new rows into the table the use of the DMax function works
fine in a single user environment, but can give rise to conflicts in a
multi-user environment where two or more users are adding records
simultaneously. As it happens I've just posted a demo elsewhere of a method
to avoid this by storing the latest number in a separate database which is
opened exclusively in code. You'll find it at:

http://community.netscape.com/n/pfx/forum.aspx?nav=messages&tsn=1&tid=12455&webtag=ws-msoffice

Its attached to message 6 in that thread.

Ken Sheridan
Stafford, England

 

[Guest]

John,

That seems like a simple request to achieve in Access. However, from the
above responses it sounds like it is not! I have nothing to add on how to
accomplish this in Access.

However, it sounds like it might be simpler to export the table to Excel,
increment the invoice numbers (very easily with a formula) and then import
the table back into Access.

 

[ben]

Dear All,

This reply is more for the general public than for this specific
issue.
Incrementing a field, creating a sequence or adding one to a
number in a query are all things that Access does not do well. Oracle
has a sequence function (I believe) but there is nothing in Access.
What's more, creating such a function is not easy. I have sucessfully
created two versions of an Increment() function and thought I'd share
them with you so that anyone searching for "Access Increment sequence
query" will find it - including me!

So here's the simplest & fastest version:

Public Function RunOnceInc(Optional someField As Variant) As Long
Static val As Long
If IsMissing(someField) Then 'reset
val = 0
End If
runOnceInc = val
val = val + 1
End Function

Looks simple huh? The trick is to run this function in your query
twice. Let's say we have a field called "test" in our query that will
generate an index number increment with each record. The field should
look like this:
test: runOnceInc() + runOnceInc([AnotherField])

The query will call the function with no parameters ONLY ONCE before it
does anything else. This resets the internal increment variable from
the last time you ran this function. The second time the function is
called it takes a field as a parameter (any field) which forces it to
be called once for every row.

This function works fine for Update or Insert type queries, but has a
serious draw back when it comes to Select queries. The function is
re-evaluated every time you re-display the results of the query (i.e.
every time another window is dragged on top of it or the scroll bar is
moved). This is obviously no good, but the solution (to follow) is a
little more bulky in terms of memory usage. For this reason, I have
included the "unsafe" version for use in large Update or Append type
queries.

For select queries we need to store the generated increment value in a
Map against some unique identifier for that row. Here is the code:

Public Function SafeInc(Optional UID As String) As Long
Static val As Long
Static seq As New Collection

If IsMissing(UID) Or UID = "" Then 'reset
Set seq = New Collection
val = 0
Else
If collectionHas(seq, UID) Then
inc = seq.Item(UID)
Else
seq.Add val, UID
inc = val
val = val + 1
End If
End If
End Function

Here I used a VB Collection to store each sequance value and
implemented the CollectionHas() function to make it work a bit like a
Map. I'll include that function a little later, but first to talk about
the SafeInc(). It obviously requires a unique identifier for each row,
but that is not normally hard to find or to create. Here's an example
of you it should be used:

test: safeInc() + safeInc([surname] & " " & [firstname])

If you don't have a Unique ID handy, you can create one from whatever
fields you like. I suppose you could even concatenate all of your
fields together. This in-memory Map object could obviously get pretty
big if you are using a large dataset. For select queries however, I'd
imagine that large datasets would become difficult to handle anyway.

Here is the CollectionHas() function:

Public Function collectionHas(coll As Collection, key As Variant) As
Boolean
collectionHas = False
On Error GoTo fail
Dim tmp As Variant
tmp = coll.Item(key)
collectionHas = True
fail:
End Function

This function simply catches the "index not found" error and returns
false instead of true. Now, I actually use a modified version of this
function because I also call it directly from other VB functions and if
you call this from INSIDE another error handling block (after the
jumpto label) the NEW error trapping block kills the old error. It's a
little difficult to explain and probably beyond the scope of this issue
- but I felt I should mention this in case people start using this
function from within an error trap in code and then wonder why their
error disapears. I modified this function to copy any pre-existing
error object onto an explicit stack structure and then pop it off again
at the end. I'd be happy to supply this code to anyone who is
interested.

I hope this will help someone. I spent a long time searching for this
code before experimenting myself. Lets hope a latr version of Access
has a nice, quick, efficient, sequence or increment function built in.

Regards,

Ben.

 

[BruceM]

How about =Nz(DMax("[YourField]","YourTable"))+1 as the default value for a
text box bound to YourField? Or am I missing your point? Incrementing
numbers have been discussed extensively in this group.

Dear All,

This reply is more for the general public than for this specific
issue.
Incrementing a field, creating a sequence or adding one to a
number in a query are all things that Access does not do well. Oracle
has a sequence function (I believe) but there is nothing in Access.
What's more, creating such a function is not easy. I have sucessfully
created two versions of an Increment() function and thought I'd share
them with you so that anyone searching for "Access Increment sequence
query" will find it - including me!

So here's the simplest & fastest version:

Public Function RunOnceInc(Optional someField As Variant) As Long
Static val As Long
If IsMissing(someField) Then 'reset
val = 0
End If
runOnceInc = val
val = val + 1
End Function

Looks simple huh? The trick is to run this function in your query
twice. Let's say we have a field called "test" in our query that will
generate an index number increment with each record. The field should
look like this:
test: runOnceInc() + runOnceInc([AnotherField])

The query will call the function with no parameters ONLY ONCE before it
does anything else. This resets the internal increment variable from
the last time you ran this function. The second time the function is
called it takes a field as a parameter (any field) which forces it to
be called once for every row.

This function works fine for Update or Insert type queries, but has a
serious draw back when it comes to Select queries. The function is
re-evaluated every time you re-display the results of the query (i.e.
every time another window is dragged on top of it or the scroll bar is
moved). This is obviously no good, but the solution (to follow) is a
little more bulky in terms of memory usage. For this reason, I have
included the "unsafe" version for use in large Update or Append type
queries.

For select queries we need to store the generated increment value in a
Map against some unique identifier for that row. Here is the code:

Public Function SafeInc(Optional UID As String) As Long
Static val As Long
Static seq As New Collection

If IsMissing(UID) Or UID = "" Then 'reset
Set seq = New Collection
val = 0
Else
If collectionHas(seq, UID) Then
inc = seq.Item(UID)
Else
seq.Add val, UID
inc = val
val = val + 1
End If
End If
End Function

Here I used a VB Collection to store each sequance value and
implemented the CollectionHas() function to make it work a bit like a
Map. I'll include that function a little later, but first to talk about
the SafeInc(). It obviously requires a unique identifier for each row,
but that is not normally hard to find or to create. Here's an example
of you it should be used:

test: safeInc() + safeInc([surname] & " " & [firstname])

If you don't have a Unique ID handy, you can create one from whatever
fields you like. I suppose you could even concatenate all of your
fields together. This in-memory Map object could obviously get pretty
big if you are using a large dataset. For select queries however, I'd
imagine that large datasets would become difficult to handle anyway.

Here is the CollectionHas() function:

Public Function collectionHas(coll As Collection, key As Variant) As
Boolean
collectionHas = False
On Error GoTo fail
Dim tmp As Variant
tmp = coll.Item(key)
collectionHas = True
fail:
End Function

This function simply catches the "index not found" error and returns
false instead of true. Now, I actually use a modified version of this
function because I also call it directly from other VB functions and if
you call this from INSIDE another error handling block (after the
jumpto label) the NEW error trapping block kills the old error. It's a
little difficult to explain and probably beyond the scope of this issue
- but I felt I should mention this in case people start using this
function from within an error trap in code and then wonder why their
error disapears. I modified this function to copy any pre-existing
error object onto an explicit stack structure and then pop it off again
at the end. I'd be happy to supply this code to anyone who is
interested.

I hope this will help someone. I spent a long time searching for this
code before experimenting myself. Lets hope a latr version of Access
has a nice, quick, efficient, sequence or increment function built in.

Regards,

Ben.

 

[BruceM]

An additional comment on the subject is that what I have suggested does not
occur in a query. Maybe there is a specific reason you want to do this in a
query. My reply was based on the assumption that you want to store the
number.

Dear All,

This reply is more for the general public than for this specific
issue.
Incrementing a field, creating a sequence or adding one to a
number in a query are all things that Access does not do well. Oracle
has a sequence function (I believe) but there is nothing in Access.
What's more, creating such a function is not easy. I have sucessfully
created two versions of an Increment() function and thought I'd share
them with you so that anyone searching for "Access Increment sequence
query" will find it - including me!

So here's the simplest & fastest version:

Public Function RunOnceInc(Optional someField As Variant) As Long
Static val As Long
If IsMissing(someField) Then 'reset
val = 0
End If
runOnceInc = val
val = val + 1
End Function

Looks simple huh? The trick is to run this function in your query
twice. Let's say we have a field called "test" in our query that will
generate an index number increment with each record. The field should
look like this:
test: runOnceInc() + runOnceInc([AnotherField])

The query will call the function with no parameters ONLY ONCE before it
does anything else. This resets the internal increment variable from
the last time you ran this function. The second time the function is
called it takes a field as a parameter (any field) which forces it to
be called once for every row.

This function works fine for Update or Insert type queries, but has a
serious draw back when it comes to Select queries. The function is
re-evaluated every time you re-display the results of the query (i.e.
every time another window is dragged on top of it or the scroll bar is
moved). This is obviously no good, but the solution (to follow) is a
little more bulky in terms of memory usage. For this reason, I have
included the "unsafe" version for use in large Update or Append type
queries.

For select queries we need to store the generated increment value in a
Map against some unique identifier for that row. Here is the code:

Public Function SafeInc(Optional UID As String) As Long
Static val As Long
Static seq As New Collection

If IsMissing(UID) Or UID = "" Then 'reset
Set seq = New Collection
val = 0
Else
If collectionHas(seq, UID) Then
inc = seq.Item(UID)
Else
seq.Add val, UID
inc = val
val = val + 1
End If
End If
End Function

Here I used a VB Collection to store each sequance value and
implemented the CollectionHas() function to make it work a bit like a
Map. I'll include that function a little later, but first to talk about
the SafeInc(). It obviously requires a unique identifier for each row,
but that is not normally hard to find or to create. Here's an example
of you it should be used:

test: safeInc() + safeInc([surname] & " " & [firstname])

If you don't have a Unique ID handy, you can create one from whatever
fields you like. I suppose you could even concatenate all of your
fields together. This in-memory Map object could obviously get pretty
big if you are using a large dataset. For select queries however, I'd
imagine that large datasets would become difficult to handle anyway.

Here is the CollectionHas() function:

Public Function collectionHas(coll As Collection, key As Variant) As
Boolean
collectionHas = False
On Error GoTo fail
Dim tmp As Variant
tmp = coll.Item(key)
collectionHas = True
fail:
End Function

This function simply catches the "index not found" error and returns
false instead of true. Now, I actually use a modified version of this
function because I also call it directly from other VB functions and if
you call this from INSIDE another error handling block (after the
jumpto label) the NEW error trapping block kills the old error. It's a
little difficult to explain and probably beyond the scope of this issue
- but I felt I should mention this in case people start using this
function from within an error trap in code and then wonder why their
error disapears. I modified this function to copy any pre-existing
error object onto an explicit stack structure and then pop it off again
at the end. I'd be happy to supply this code to anyone who is
interested.

I hope this will help someone. I spent a long time searching for this
code before experimenting myself. Lets hope a latr version of Access
has a nice, quick, efficient, sequence or increment function built in.

Regards,

Ben.

 

[John]

Because the underlying table field is text DMax("[YourField]","YourTable")
as far as I can tell brings say 99 as the largest previous number instead of
say 100020 (text comparison instead of numeric comparison)and then adds one
to it thus creating a duplicate id 100 which is already in the table.

Regards

How about =Nz(DMax("[YourField]","YourTable"))+1 as the default value for
a text box bound to YourField? Or am I missing your point? Incrementing
numbers have been discussed extensively in this group.

Dear All,

This reply is more for the general public than for this specific
issue.
Incrementing a field, creating a sequence or adding one to a
number in a query are all things that Access does not do well. Oracle
has a sequence function (I believe) but there is nothing in Access.
What's more, creating such a function is not easy. I have sucessfully
created two versions of an Increment() function and thought I'd share
them with you so that anyone searching for "Access Increment sequence
query" will find it - including me!

So here's the simplest & fastest version:

Public Function RunOnceInc(Optional someField As Variant) As Long
Static val As Long
If IsMissing(someField) Then 'reset
val = 0
End If
runOnceInc = val
val = val + 1
End Function

Looks simple huh? The trick is to run this function in your query
twice. Let's say we have a field called "test" in our query that will
generate an index number increment with each record. The field should
look like this:
test: runOnceInc() + runOnceInc([AnotherField])

The query will call the function with no parameters ONLY ONCE before it
does anything else. This resets the internal increment variable from
the last time you ran this function. The second time the function is
called it takes a field as a parameter (any field) which forces it to
be called once for every row.

This function works fine for Update or Insert type queries, but has a
serious draw back when it comes to Select queries. The function is
re-evaluated every time you re-display the results of the query (i.e.
every time another window is dragged on top of it or the scroll bar is
moved). This is obviously no good, but the solution (to follow) is a
little more bulky in terms of memory usage. For this reason, I have
included the "unsafe" version for use in large Update or Append type
queries.

For select queries we need to store the generated increment value in a
Map against some unique identifier for that row. Here is the code:

Public Function SafeInc(Optional UID As String) As Long
Static val As Long
Static seq As New Collection

If IsMissing(UID) Or UID = "" Then 'reset
Set seq = New Collection
val = 0
Else
If collectionHas(seq, UID) Then
inc = seq.Item(UID)
Else
seq.Add val, UID
inc = val
val = val + 1
End If
End If
End Function

Here I used a VB Collection to store each sequance value and
implemented the CollectionHas() function to make it work a bit like a
Map. I'll include that function a little later, but first to talk about
the SafeInc(). It obviously requires a unique identifier for each row,
but that is not normally hard to find or to create. Here's an example
of you it should be used:

test: safeInc() + safeInc([surname] & " " & [firstname])

If you don't have a Unique ID handy, you can create one from whatever
fields you like. I suppose you could even concatenate all of your
fields together. This in-memory Map object could obviously get pretty
big if you are using a large dataset. For select queries however, I'd
imagine that large datasets would become difficult to handle anyway.

Here is the CollectionHas() function:

Public Function collectionHas(coll As Collection, key As Variant) As
Boolean
collectionHas = False
On Error GoTo fail
Dim tmp As Variant
tmp = coll.Item(key)
collectionHas = True
fail:
End Function

This function simply catches the "index not found" error and returns
false instead of true. Now, I actually use a modified version of this
function because I also call it directly from other VB functions and if
you call this from INSIDE another error handling block (after the
jumpto label) the NEW error trapping block kills the old error. It's a
little difficult to explain and probably beyond the scope of this issue
- but I felt I should mention this in case people start using this
function from within an error trap in code and then wonder why their
error disapears. I modified this function to copy any pre-existing
error object onto an explicit stack structure and then pop it off again
at the end. I'd be happy to supply this code to anyone who is
interested.

I hope this will help someone. I spent a long time searching for this
code before experimenting myself. Lets hope a latr version of Access
has a nice, quick, efficient, sequence or increment function built in.

Regards,

Ben.

 

[BruceM]

That's not how it works. It's simple enough to set up a test table that
consists of a single number field, and to use the formula I provided as the
default value in the text box bound to that field. Scroll through the
records (the mouse wheel can do that quickly) until you get to 101. Change
the value of the field in that record to 100019. Create a new record. The
number is 100020.
I have also used DMax + 1 in expressions involving text fields in order to
get sequences such as RPT-06-01, RPT-06-02. I use the form's Current event
rather than the default value to construct that number, but the principle is
the same. If YourID is a text field, it seems I need to pad the number with
zeros to get it to work. For instance:

Private Sub Form_Current()

Dim strID As String
Dim varID As Variant

If Me.NewRecord Then
strID = "YourID Like """ & "*"""
varID = Nz(DMax("YourID", "tblYourTable", strID))
Me.YourID = Format(Val(Right(varID, 3)) + 1, "000")
End If

End Sub

From what I can tell I need to format for the maximum number of digits.
However, if I am using just a number, I can see no reason to store it in a
text field.

There are provisions for using the DMax system in a multi-user database.
One way is explained here:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb
I have worked out other methods, too.
I have tested the system extensively. It works. If you can identify an
instance in which it does not work, I would very much like to see an
example.

The person who posted originally seems to have over-complicated the problem.
I asked for clarification, in case I had misunderstood, but received none.
As to using a query to obtain the next number in a sequence, maybe that is
more complicated, but if I need to increment a number I will opt for the
simplest way of doing so. If I need to use a wrench to drive nails, maybe
it will be possible to complete the building project, but it will be much
harder.

Because the underlying table field is text DMax("[YourField]","YourTable")
as far as I can tell brings say 99 as the largest previous number instead
of say 100020 (text comparison instead of numeric comparison)and then adds
one to it thus creating a duplicate id 100 which is already in the table.

Regards

How about =Nz(DMax("[YourField]","YourTable"))+1 as the default value for
a text box bound to YourField? Or am I missing your point? Incrementing
numbers have been discussed extensively in this group.

Dear All,

This reply is more for the general public than for this specific
issue.
Incrementing a field, creating a sequence or adding one to a
number in a query are all things that Access does not do well. Oracle
has a sequence function (I believe) but there is nothing in Access.
What's more, creating such a function is not easy. I have sucessfully
created two versions of an Increment() function and thought I'd share
them with you so that anyone searching for "Access Increment sequence
query" will find it - including me!

So here's the simplest & fastest version:

Public Function RunOnceInc(Optional someField As Variant) As Long
Static val As Long
If IsMissing(someField) Then 'reset
val = 0
End If
runOnceInc = val
val = val + 1
End Function

Looks simple huh? The trick is to run this function in your query
twice. Let's say we have a field called "test" in our query that will
generate an index number increment with each record. The field should
look like this:
test: runOnceInc() + runOnceInc([AnotherField])

The query will call the function with no parameters ONLY ONCE before it
does anything else. This resets the internal increment variable from
the last time you ran this function. The second time the function is
called it takes a field as a parameter (any field) which forces it to
be called once for every row.

This function works fine for Update or Insert type queries, but has a
serious draw back when it comes to Select queries. The function is
re-evaluated every time you re-display the results of the query (i.e.
every time another window is dragged on top of it or the scroll bar is
moved). This is obviously no good, but the solution (to follow) is a
little more bulky in terms of memory usage. For this reason, I have
included the "unsafe" version for use in large Update or Append type
queries.

For select queries we need to store the generated increment value in a
Map against some unique identifier for that row. Here is the code:

Public Function SafeInc(Optional UID As String) As Long
Static val As Long
Static seq As New Collection

If IsMissing(UID) Or UID = "" Then 'reset
Set seq = New Collection
val = 0
Else
If collectionHas(seq, UID) Then
inc = seq.Item(UID)
Else
seq.Add val, UID
inc = val
val = val + 1
End If
End If
End Function

Here I used a VB Collection to store each sequance value and
implemented the CollectionHas() function to make it work a bit like a
Map. I'll include that function a little later, but first to talk about
the SafeInc(). It obviously requires a unique identifier for each row,
but that is not normally hard to find or to create. Here's an example
of you it should be used:

test: safeInc() + safeInc([surname] & " " & [firstname])

If you don't have a Unique ID handy, you can create one from whatever
fields you like. I suppose you could even concatenate all of your
fields together. This in-memory Map object could obviously get pretty
big if you are using a large dataset. For select queries however, I'd
imagine that large datasets would become difficult to handle anyway.

Here is the CollectionHas() function:

Public Function collectionHas(coll As Collection, key As Variant) As
Boolean
collectionHas = False
On Error GoTo fail
Dim tmp As Variant
tmp = coll.Item(key)
collectionHas = True
fail:
End Function

This function simply catches the "index not found" error and returns
false instead of true. Now, I actually use a modified version of this
function because I also call it directly from other VB functions and if
you call this from INSIDE another error handling block (after the
jumpto label) the NEW error trapping block kills the old error. It's a
little difficult to explain and probably beyond the scope of this issue
- but I felt I should mention this in case people start using this
function from within an error trap in code and then wonder why their
error disapears. I modified this function to copy any pre-existing
error object onto an explicit stack structure and then pop it off again
at the end. I'd be happy to supply this code to anyone who is
interested.

I hope this will help someone. I spent a long time searching for this
code before experimenting myself. Lets hope a latr version of Access
has a nice, quick, efficient, sequence or increment function built in.

Regards,

Ben.

 

[BruceM]

I should clarify that I have used the DMax expression as described to
increment a number field. I never said that I was talking about a text
field, not did the person who originally posted. My previous post on this
date includes comments about using it to increment a text field.

Because the underlying table field is text DMax("[YourField]","YourTable")
as far as I can tell brings say 99 as the largest previous number instead
of say 100020 (text comparison instead of numeric comparison)and then adds
one to it thus creating a duplicate id 100 which is already in the table.

Regards

How about =Nz(DMax("[YourField]","YourTable"))+1 as the default value for
a text box bound to YourField? Or am I missing your point? Incrementing
numbers have been discussed extensively in this group.

Dear All,

This reply is more for the general public than for this specific
issue.
Incrementing a field, creating a sequence or adding one to a
number in a query are all things that Access does not do well. Oracle
has a sequence function (I believe) but there is nothing in Access.
What's more, creating such a function is not easy. I have sucessfully
created two versions of an Increment() function and thought I'd share
them with you so that anyone searching for "Access Increment sequence
query" will find it - including me!

So here's the simplest & fastest version:

Public Function RunOnceInc(Optional someField As Variant) As Long
Static val As Long
If IsMissing(someField) Then 'reset
val = 0
End If
runOnceInc = val
val = val + 1
End Function

Looks simple huh? The trick is to run this function in your query
twice. Let's say we have a field called "test" in our query that will
generate an index number increment with each record. The field should
look like this:
test: runOnceInc() + runOnceInc([AnotherField])

The query will call the function with no parameters ONLY ONCE before it
does anything else. This resets the internal increment variable from
the last time you ran this function. The second time the function is
called it takes a field as a parameter (any field) which forces it to
be called once for every row.

This function works fine for Update or Insert type queries, but has a
serious draw back when it comes to Select queries. The function is
re-evaluated every time you re-display the results of the query (i.e.
every time another window is dragged on top of it or the scroll bar is
moved). This is obviously no good, but the solution (to follow) is a
little more bulky in terms of memory usage. For this reason, I have
included the "unsafe" version for use in large Update or Append type
queries.

For select queries we need to store the generated increment value in a
Map against some unique identifier for that row. Here is the code:

Public Function SafeInc(Optional UID As String) As Long
Static val As Long
Static seq As New Collection

If IsMissing(UID) Or UID = "" Then 'reset
Set seq = New Collection
val = 0
Else
If collectionHas(seq, UID) Then
inc = seq.Item(UID)
Else
seq.Add val, UID
inc = val
val = val + 1
End If
End If
End Function

Here I used a VB Collection to store each sequance value and
implemented the CollectionHas() function to make it work a bit like a
Map. I'll include that function a little later, but first to talk about
the SafeInc(). It obviously requires a unique identifier for each row,
but that is not normally hard to find or to create. Here's an example
of you it should be used:

test: safeInc() + safeInc([surname] & " " & [firstname])

If you don't have a Unique ID handy, you can create one from whatever
fields you like. I suppose you could even concatenate all of your
fields together. This in-memory Map object could obviously get pretty
big if you are using a large dataset. For select queries however, I'd
imagine that large datasets would become difficult to handle anyway.

Here is the CollectionHas() function:

Public Function collectionHas(coll As Collection, key As Variant) As
Boolean
collectionHas = False
On Error GoTo fail
Dim tmp As Variant
tmp = coll.Item(key)
collectionHas = True
fail:
End Function

This function simply catches the "index not found" error and returns
false instead of true. Now, I actually use a modified version of this
function because I also call it directly from other VB functions and if
you call this from INSIDE another error handling block (after the
jumpto label) the NEW error trapping block kills the old error. It's a
little difficult to explain and probably beyond the scope of this issue
- but I felt I should mention this in case people start using this
function from within an error trap in code and then wonder why their
error disapears. I modified this function to copy any pre-existing
error object onto an explicit stack structure and then pop it off again
at the end. I'd be happy to supply this code to anyone who is
interested.

I hope this will help someone. I spent a long time searching for this
code before experimenting myself. Lets hope a latr version of Access
has a nice, quick, efficient, sequence or increment function built in.

Regards,

Ben.