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]

Re: Assigning sequential numbers

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" <John@nospam.in fovis.co.uk> wrote in message
news:ee8jG6OMGH A.2216@TK2MSFTN GP09.phx.gbl...[color=blue]
> 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
>[/color]

[jacksonmacd]

Re: Assigning sequential numbers

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" <John@nospam.in fovis.co.uk>
wrote:
[color=blue]
>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
>[/color]

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