Need help creating code for sequential numbering

[jmillerWV]

Hello again all,
I have spent time looking in books and on line for this with no luck. I am
using Access 2003 autonumbering as my "order number" From what I have heard
this is not a good idea, so can anyone point me as to where I may find how to
create sequential numbering for my orders without using Autonumbering? Many
people use the database at any given time entering new orders, Marking orders
done, invoicing ect. This DB will be upsized to SQL server 2000 in the near
future. Thanks to all who have helped to this point and to those that will
help in the future.

 

[Beetle]

The basic expression to do that is;

Nz(DMax("TheField", "TheTable"), 0) + 1

In a multi-user environment you would want to wait until
just before the new record is saved before assigning the value
to minimize the chance of two or more users generating
the same number. You can do so using the forms Before Update
event;

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.NewRecord Then
Me!OrderNo = Nz(DMax("OrderNo", "tblOrders"), 0) + 1
End If

End Sub

You would need to use your actual field and table names of course,
and you may need a few minor adjustments depending on your
specific needs. This is just a generic example.