G
Guest
Is there a way to have a autonumber key start at say 1004 instead of 1?
Thanks,
Thanks,
G
Guest
Ted,
Usually I do that as follows:
1. Create a table with one field datatype integer
2. Insert 1004 as a number in the table
3. Create a query with the newly created table as the source
4. Make the query an appendquery
5. Append the numberfield to the autonumber field of the table which you
want to start at 1004
6. Run the query
7. Delete the query and the newly created table
8. Now if you insert a record in the table which contains the autonumber it
should start at 1004
hth
Usually I do that as follows:
1. Create a table with one field datatype integer
2. Insert 1004 as a number in the table
3. Create a query with the newly created table as the source
4. Make the query an appendquery
5. Append the numberfield to the autonumber field of the table which you
want to start at 1004
6. Run the query
7. Delete the query and the newly created table
8. Now if you insert a record in the table which contains the autonumber it
should start at 1004
hth
L
Larry Daugherty
You're heading down a path you may want to abandon sooner rather than
later.
The Autonumber datatype exists for the sole purpose of generating
uniqueness for use as a surrogate Primary Key - *that's all*.
It has been my recommendation for years that no one, including the
developer should ever see that number once the design is in play. The
developer should only ever care what the value happens to be if it's
necessary to know for debugging. No user should even know it exists.
Once you have it set to begin with a value that you want, what will
you do if and when it leaves the occasional gaps? When it does a few
other things that still generate unique values but aren't what you
wanted???
How about when someone, your customers or a micromanaging authority
wants that visible number to behave according to arbitrary dictates?
If you want to supply and control a running sequence then you should
provide the code to create and control it. It isn't hard to do. Then
you can write code to make it behave as you want now and in the
future.
While Microsoft and lots of clever Access developers have devised
methods for beating the Autonumber setting down to submit to their
will. However, those solutions tend to be purpose focused, manual
interventions without lasting benefit. You have to do some research
and learning and work to overcome issues that shouldn't have affected
you in the first place. When the problem comes back you have to apply
the solution again. Better to design a workable solution in the first
place.
By the way, I use Autonumber primary keys all the time.
HTH
later.
The Autonumber datatype exists for the sole purpose of generating
uniqueness for use as a surrogate Primary Key - *that's all*.
It has been my recommendation for years that no one, including the
developer should ever see that number once the design is in play. The
developer should only ever care what the value happens to be if it's
necessary to know for debugging. No user should even know it exists.
Once you have it set to begin with a value that you want, what will
you do if and when it leaves the occasional gaps? When it does a few
other things that still generate unique values but aren't what you
wanted???
How about when someone, your customers or a micromanaging authority
wants that visible number to behave according to arbitrary dictates?
If you want to supply and control a running sequence then you should
provide the code to create and control it. It isn't hard to do. Then
you can write code to make it behave as you want now and in the
future.
While Microsoft and lots of clever Access developers have devised
methods for beating the Autonumber setting down to submit to their
will. However, those solutions tend to be purpose focused, manual
interventions without lasting benefit. You have to do some research
and learning and work to overcome issues that shouldn't have affected
you in the first place. When the problem comes back you have to apply
the solution again. Better to design a workable solution in the first
place.
By the way, I use Autonumber primary keys all the time.
HTH
G
Guest
Thanks I will try this!
Maurice said:
G
Guest
Wow! That will take me a while to digest!
I want to start using this as an invoice number that currently I am at 1004
within Quickbooks and want my employee to invoice out in the field a invoice
number that will correspond. Now I'm a little concerned...Will it skip
numbers? What would be the proper way that you would suggest?
Thanks,
I want to start using this as an invoice number that currently I am at 1004
within Quickbooks and want my employee to invoice out in the field a invoice
number that will correspond. Now I'm a little concerned...Will it skip
numbers? What would be the proper way that you would suggest?
Thanks,
G
Guest
Hi Ted,
I agree with Larry on this one. But it will start at 1004 and will leave no
gaps besides the numbers missing before 1004. My posting is purely based on
the fact that you wanted to start at 1004 and not to explain to you about the
autonumber. Well you got a good explanation from Larry though. I use
autonumber frequently and never start at a designated number.
I agree with Larry on this one. But it will start at 1004 and will leave no
gaps besides the numbers missing before 1004. My posting is purely based on
the fact that you wanted to start at 1004 and not to explain to you about the
autonumber. Well you got a good explanation from Larry though. I use
autonumber frequently and never start at a designated number.
J
Jeff Boyce
Pardon my intrusion...
If you want to have "invoice numbers" that you can control, create a field
in your table and a control on your form. Enter the values you wish.
If you want an "auto-incrementing" invoice number, do a search on "custom
autonumber" to come up with a routine that looks up the highest value and
adds one. Note that this is NOT an Access "autonumber"!
(and I've used Autonumber primary key fields for years... but not for human
consumption)
Regards
Jeff Boyce
Microsoft Office/Access MVP
If you want to have "invoice numbers" that you can control, create a field
in your table and a control on your form. Enter the values you wish.
If you want an "auto-incrementing" invoice number, do a search on "custom
autonumber" to come up with a routine that looks up the highest value and
adds one. Note that this is NOT an Access "autonumber"!
(and I've used Autonumber primary key fields for years... but not for human
consumption)
Regards
Jeff Boyce
Microsoft Office/Access MVP
L
Larry Daugherty
Hi Jeff,
It's never an intrusion to jump in and give the OP correct and timely
information. I'm often absent from the newsgroups for days, months
and even years between posts. I see these posts as a way to help, not
as a way to establish ownership of an OP's thread.
Many contributors means that more than one view might be presented.
When motivated by the earnest desire to assist the OP that's all to
the good.
Thanks for your frequent and accurate posts.
It's never an intrusion to jump in and give the OP correct and timely
information. I'm often absent from the newsgroups for days, months
and even years between posts. I see these posts as a way to help, not
as a way to establish ownership of an OP's thread.
Many contributors means that more than one view might be presented.
When motivated by the earnest desire to assist the OP that's all to
the good.
Thanks for your frequent and accurate posts.
L
Larry Daugherty
Hi Maurice,
Your heart is pure and your intentions are good but you are or may be
in error as to there never being any future gaps when using an
Autonumber.
Rather then me lecturing as some kind of trumped up "authority" I
suggest that you google the Access groups on 'Autonumber'. Be patient
and thorough and you'll discover some recurring themes and situations.
Think of just one simple case: You decide that the record you just
started to create is unnecessary. You cancel out and delete that
record. Using an Autonumber datatype, that deleted value will never
again be used in this application without explicit human
intervention - as in the Original Post.
In the above case, if you had your own system for generating sequences
that went something like "find the highest number in this field, add
one to it for the new number", your sequence system would work
flawlessly.
HTH
Your heart is pure and your intentions are good but you are or may be
in error as to there never being any future gaps when using an
Autonumber.
Rather then me lecturing as some kind of trumped up "authority" I
suggest that you google the Access groups on 'Autonumber'. Be patient
and thorough and you'll discover some recurring themes and situations.
Think of just one simple case: You decide that the record you just
started to create is unnecessary. You cancel out and delete that
record. Using an Autonumber datatype, that deleted value will never
again be used in this application without explicit human
intervention - as in the Original Post.
In the above case, if you had your own system for generating sequences
that went something like "find the highest number in this field, add
one to it for the new number", your sequence system would work
flawlessly.
HTH
G
Guest
Hi Larry,
Thank you for the 'pureness' idea. I think that you are quit right on this
subject. I also believe that an autonumber should never be used for keeping
track of things. As you stated earlier the best option to use it for is
uniqueness. So I know that there will be gaps before and maybe in the future
data entry. On that point i have to admit that my judgement was to quick. In
the case Ted stated I begin to believe that he's better of using a number
field and do the math by hand.
cheers
Thank you for the 'pureness' idea. I think that you are quit right on this
subject. I also believe that an autonumber should never be used for keeping
track of things. As you stated earlier the best option to use it for is
uniqueness. So I know that there will be gaps before and maybe in the future
data entry. On that point i have to admit that my judgement was to quick. In
the case Ted stated I begin to believe that he's better of using a number
field and do the math by hand.
cheers
G
Guest
Thank you Jeff, Maurice, and Larry. Your very infomative and I appreciate
the advice along with the pointed direction. I always enjoy my time spent on
this Discussion Group and come out with knowlege that helps me!
Ted
the advice along with the pointed direction. I always enjoy my time spent on
this Discussion Group and come out with knowlege that helps me!
Ted
J
Jeff Boyce
Ted
Thanks for taking the time to post the kind words!
Regards
Jeff Boyce
Microsoft Office/Access MVP
Thanks for taking the time to post the kind words!
Regards
Jeff Boyce
Microsoft Office/Access MVP
G
Guest
One more point.
The range of a Long data type (ie Autonumber) is from -2,147,483,648 to
2,147,483,647.
So it is possible your Invoice number (if you used the autonumber) could go
negative. From what I have read, nothing guarantees the autonumber will stay
positive.
The range of a Long data type (ie Autonumber) is from -2,147,483,648 to
2,147,483,647.
So it is possible your Invoice number (if you used the autonumber) could go
negative. From what I have read, nothing guarantees the autonumber will stay
positive.