AutoNumber starting point

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

Any easy way to force/pick a starting number for a Autonumber field?

New table so no records yet.

Access 2003


Thanks in advance

D
 
In
Dave said:
Any easy way to force/pick a starting number for a Autonumber field?

New table so no records yet.
Click to expand...

You probably shouldn't want to, since autonumbers really aren't supposed
to have any meaning. However, one simple, non-technical way to do what
you want is to run an append query that inserts a record *with the value
for the autonumber field*, and then delete that record. The value you
insert should be one less than the one you want the next autonumber to
start at.

For example:

INSERT INTO MyTable (MyAutonumberField, SomeOtherField)
VALUES (999, 'XXX')

After executing that query, the next autonumber to be generated will be
1000 (assuming the numbering is set for consecutive, not random).
 
Dave said:
Any easy way to force/pick a starting number for a Autonumber field?

New table so no records yet.

Access 2003


Thanks in advance

D
Click to expand...

I suggest you may not want to use Autonumber for that use. Autonumbers are
designed to provide unique numbers. It in not designed to provide numbers
in order and for a number of reasons may not do so. As a result using them
in any application where the user sees the numbers is likely to end up with
confusion.

There are other ways of providing the numbers you want depending on the
particual application.
 
You can accomplish this. However, you should not EVER worry about the number
given anyway (you can't assign, or give this number meaning).

Do a compact and repair, and then paste the follong code into a module:


Sub SetNextID()

Dim strTable As String
Dim strSql1 As String
Dim strSql2 As String
Dim ibuf As String
Dim nextvalue As Long


strTable = InputBox("What table to modify")
If strTable = "" Then Exit Sub

strSql1 = "INSERT INTO " & strTable & " (ID) SELECT TOP 1 "
strSql2 = "DELETE ID FROM " & strTable & " WHERE ID = "

ibuf = InputBox("Enter next value (blank enter will exit)")
If ibuf <> "" Then
nextvalue = ibuf - 1
CurrentDb.Execute strSql1 & nextvalue & " AS Expr1 from " & strTable
' now delete this guy
CurrentDb.Execute strSql2 & nextvalue
End If

End Sub

the above assumes (ID) for the autonumber....
 
Hi,

You've already received excellent advice and suggestions from Dirk and
Albert.

The part of the issue that is rarely asked on the first posting is:
"Well then, what's an easy way to generate an unbroken sequence for my
invoices, sales orders, etc."?

The most elegant solution I've seen goes something like the following
pseudocode:

MySequenceNumber = DMax(MySequenceNumber) + 1

Gaps won't be generated but they can occur when records are deleted.
If you need to account for every number then those records should be
marked as "void" rather than deleted..

HTH
 
Thank you all.
The reason for this is that I have my Invoice Number (number) to be the same
as my OrderID (autonumber).
From what you are saying this was a BAD approach?
Not sure how easly I can back out now :(

D
 
Thank you all.
The reason for this is that I have my Invoice Number (number) to be the same
as my OrderID (autonumber).
From what you are saying this was a BAD approach?
Not sure how easly I can back out now :(
Click to expand...

Yes, it's a very bad idea, unless you're comfortable with multiple gaps in the
invoice number sequence. (Accountants tend to get antsy when they see invoice
numbers 415, 416, 417, 423, 424...)

And if you were thinking of having BOTH invoice number and OrderID being
autonumbers - *forget it*. This will emphatically NOT work; and you should not
have two independent, unrelated numbering systems in your tables and expect
them to remanin in synch. If you want to use the OrderID as a printed invoice
number - *use it*. Copying it, or (worse) programming it, into a separate
field is not necessary.

John W. Vinson [MVP]
 
You can "back out" now more easily than later and sooner or later
you'll decide that you have to change the design.

Note that changes to the schema will have to ripple up through
everything that's been built on the earlier design. Schema changes
are expensive. That's one compelling reason to pay attention to the
fundamentals and get it right the first time by doing a thorough job
of the front end documentation and analysis and design. Nobody ever
always gets it right the first time but you'll get better at it as you
go.

HTH
 
John and Larry,

OK - you guys are the experts - thats why I come here for help.
SO John - I can still back out, you are correct and that is just what I will
try to do.
Larry - can you please advise how to impliment the sugggestion you presented
above.

I am not sure of all of the ramification of changing this now but we are NOT
live yet so now IS the time.

Thanks again,
D
 
I can and probably should but I won't. :-) You'll learn a whole
bunch more by using Help and other resources at your command.

The big clue in the pseudocode that I suggested is the DMax()
function. It's a real Access function. You can look it up in Access
help and learn to use it.. It's a Domain Aggregate Function. There
are a few of them. They are useful. They are succinct and their
syntax gives some people grief.

In Access as in other programming environments, nothing exists in
isolation. Sooo... At the point where you are going to create a new
what-ever-it-is-that-you-are-sequencing, find the maximum value in the
sequence number field, add one to it and write that number into that
field in the record for your new thingy. To paraphrase:

MySequenceNumber = DMax(MySequenceNumber) + 1

The real syntax is, of course different. and I don't know the names of
your relevant table and field.

If you haven't already done so, please visit www.mvps.org/access

HTH
 
Thanks Larry,
I did understand that DMax() was thebuild in function you were suggesting I
use.
and I do understand the using "help" would help me understand how to use the
function.
As usual with me my real problem was not knowing "where" to use the function
however
I will do my best. :)

Thanks again for the input
and I will check out the link you providded also.

D
 
Dave said:
Thanks Larry,
I did understand that DMax() was thebuild in function you were
suggesting I use.
and I do understand the using "help" would help me understand how to
use the function.
As usual with me my real problem was not knowing "where" to use the
function however
I will do my best. :)

Thanks again for the input
and I will check out the link you providded also.
Click to expand...

And stop back if you get stuck.
 
Back
Top