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>

"John" <Jo**@nospam.in fovis.co.uk> wrote in message
news:ee******** ******@TK2MSFTN GP09.phx.gbl...

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

[jacksonmacd]

Here is a function that can work: Put this in a public module:
Public Function NextNumber(varF ield As Variant, Optional varValue As
Variant) As Long
Static lngNextNumber As Long

If IsMissing(varVa lue) Then
lngNextNumber = lngNextNumber + 1
Else
lngNextNumber = varValue
End If
NextNumber = lngNextNumber
End Function
Then "prime" the function with a starting value by executing this from
the Immediate window:

call NextNumber("Som eValue",0)

Finally, add a column to your query like this:
RowNumber: NextNumber([SomeFieldInQuer y])

(field name is REQUIRED, but not significant -- any one will work)


On Mon, 13 Feb 2006 22:40:40 -0000, "John" <Jo**@nospam.in fovis.co.uk>
wrote:

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

*************** *******
ja************* *@telusTELUS.ne t
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security