Changing Autonumber

[Aurora]

I am using Access 2000

I created a db last year, with an autonumber field
(labeled ID), incrementing by 1 and starting at 30000.
This number is used as a quote number in our Sales Dept.
Now I want to change the autonumber to begin with 40000.
I know I have done this in the past (mostly with a new and
empty db), by changing the starting value of an
incrementing autonumber field. But this time it's not
working. Could this be because there are records in the
Db.

I created a temp Db with one number (Long Int) field, ID.
I put 40000 in this field. In the Quote Db, I changed any
fields that were indexed with "No duplicates"
to "duplicates OK". I don't have any required fields and
I took the primary key off of the ID autonumber field.
I then created an append query to append this record to
the quote Db. But nothing happened. The Db is still
using the 30000 numbering system.

Does anyone have any ideas of to where I went wrong. I
really need some help here.

Aurora

 

[Steve Schapel]

Aurora,

Did the Append Query actually put the 40000 record into the Autonumber
field in your Quote table?

- Steve Schapel, Microsoft Access MVP

 

[Tim Ferguson]

This number is used as a quote number in our Sales Dept.
Now I want to change the autonumber to begin with 40000.

The idea is to create a record with an AN value of 39999 -- you can do this
with an update query

INSERT INTO MyTable (MyANField)
VALUES (39999);

which is just the easy way of doing what the instructions tell you with
temp tables and all that stuff.

In the Quote Db, I changed any
fields that were indexed with "No duplicates"
to "duplicates OK". I don't have any required fields and
I took the primary key off of the ID autonumber field.

There is no need to change any keys or indexes. Are you sure it's an
autonumber field?

By the way, I strongly suspect a Design Problem here -- if you are trying
to code the YearCreated into the number then you will find life much easier
keeping it in a separate field. There are forty years of research backing
up normalisation and R theory !

All the best


Tim F

 

[Aurora]

NO! Nothing happened.

Aurora

-----Original Message-----
Aurora,

Did the Append Query actually put the 40000 record into the Autonumber
field in your Quote table?

- Steve Schapel, Microsoft Access MVP




.

 

[Aurora]

Tim - Thank you for answering.

I tried the Update Query, but got a message that this
field is not updateable. The field is an autonumber
field.

I know I should not put the year into the quote number but
this is what Sales wants. They used to keep track of
their quotes on Excell and it was easy to sort and or to
find the quote you wanted. None of them have ever really
used Access and so they want to keep it simple. They
borrowed a quote Db developed by a sister company. I am
here to try to make the changes they want to make it fit
our Company.

Do you have any other suggestions????

Aurora

 

[Steve Schapel]

Sorry, Aurora, I can't see anything to explain this problem. The
Append Query as you described would normally work as expected, unless
there is another field with Required set to Yes, or if some other data
requirement in one of the other fields is being violated. An example
of this is Validation Rule settings... did you check these, both at
the field and table level? The other thing to try would be making an
entire "bogus" record, with valid data in all fields, and append that
to the table with an Append Query.

- Steve Schapel, Microsoft Access MVP

 

[Tim Ferguson]

I tried the Update Query, but got a message that this
field is not updateable. The field is an autonumber
field.

It's not updateable, but it is appendable (IFYSWIM). This query does work
as long as we are talking about an Autonumber field in a Jet database.

I know I should not put the year into the quote number but
this is what Sales wants.

No it's not: it's what the Sales people want _to_ _see_. I might not much
like the colour co-ordination in the wiring in my car, but as long as the
lights come on when I switch on the lights I don't have a right to
complain. You can present them with whatever number style they like (as
long as it has a reasonable algorithm) but there is no reason for them to
dictate how you bring it about.

B Wishes



Tim F