By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,837 Members | 1,813 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,837 IT Pros & Developers. It's quick & easy.

Assigning sequential numbers

P: n/a
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
Feb 13 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.infovis.co.uk> wrote in message
news:ee**************@TK2MSFTNGP09.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

Feb 13 '06 #2

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

If IsMissing(varValue) 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("SomeValue",0)

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

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


On Mon, 13 Feb 2006 22:40:40 -0000, "John" <Jo**@nospam.infovis.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.net
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
Feb 14 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.