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
New table so no records yet.
Access 2003
Thanks in advance
D
D
Dirk Goldgar
In
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:
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).
A
Albert D. Kallal
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....
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....
L
Larry Daugherty
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
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
D
Dave
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
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
J
John W. Vinson
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
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]
L
Larry Daugherty
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
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
D
Dave
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
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
L
Larry Daugherty
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
You'll learn a wholebunch 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
D
Dave
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
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