Set the value of AutoNumber field manually instead of the automatically generated one!

[Özden Irmak]

Hello,

I want to set the AutoNumber field value by myself when I add a new record
on some situations.

One thing as a solution for this I found is to set the seed value of that
column by using an "ALTER TABLE" statement but this doesn't seem to work on
Access 97?

Anybody knows a solution for this?

Thanks in advance,

Özden

 

[Tom Ellison]

Dear Özden:

If you have the need to use an automatically generated number that you
will display to the user, or which you want to manipulate, then don't
use an autonumber for this, just a long integer.

It is best to use autonumbers only for hidden identity values used to
relate rows between tables. Instead, you can keep track of a "next
record number" in a row of another table (what I call the persistent
value table) and increment this each time you use a number. Some
locking is necessary in a multi-user situation when you do this.

Also, when you have the situation where you want to add a new record
with a predetermined value for this unique valued column, how do you
intend to asure that value isn't already one of the "computer
generated" values already in the table? If some of the values are
computer generated and some are user entered, couldn't this cause
duplication?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Özden Irmak]

Hello Tom,

I appreciate all of your suggestions concerning the usage of AutoNumber
columns but this is not the case...

I'm writing a general purpose tool to compare and synchronize the data
differences on two access databases (a completer tool for my other tool
which compares and synchronizes two access databases structures) so the
thing I want to achieve is a must...

Anyway, thanks again for the suggestions...

Regards,

Özden

 

[Rick Brandt]

Hello Tom,

I appreciate all of your suggestions concerning the usage of AutoNumber
columns but this is not the case...

His comment was that if you need to *control* the value don't use an
AutoNumber. You are attempting to do exactly that, but think his comment
does not apply?

 

[Özden Irmak]

Hello Rick,

Sorry if my sentence means anything wrong as I don't come from a native
english background...

I only wanted to mention that it's not under my control to use AutoNumber or
not...Somehow I have to find a way to control the values created at those
fields...

Sorry again as I think Tom shared his valuable knowledge with me, which I'm
thankfull, and I didn't want to mean anything wrong...

Thanks again,

Özden

 

[Rick Brandt]

Hello Rick,

Sorry if my sentence means anything wrong as I don't come from a native
english background...

I only wanted to mention that it's not under my control to use AutoNumber or
not...Somehow I have to find a way to control the values created at those
fields...

Sorry again as I think Tom shared his valuable knowledge with me, which I'm
thankfull, and I didn't want to mean anything wrong...

Thanks again,

No problem, but you might be in a bit of a fix then. I don't think the
ALTER TABLE will work with Access 97. You can do an insert query that
provides an explicit value for the AutoNumber field and Access will use
that value provided it is not a duplicate. Perhaps you can use that method
instead.

 

[Tim Ferguson]

I want to set the AutoNumber field value by myself when I add a new
record on some situations.

Having read the rest of this thread, I think it is worth pointing out that
you _can_ enter any number you want into an Autonumber field: but I echo
all the provisos that others have offered in that it is not neccessarily a
good thing to do. Furthermore, poking in records with PK values that have
been used before and deleted raises the risk of creating wrong data: it may
be acceptable to have an orphaned record in a related but that record will
suddenly become valid and incorrect if a parent record appears with the
same PK value. If you see what I mean.

Still, if you really have to have a record with a particular autonumber
value, you can use SQL to do it:

INSERT INTO MyTable(MyAutonumberField, MyRequiredField)
VALUES (2038, "Eric");

and for all I know UPDATE would work too. But please note that this is a
rare thing to have to do and almost certainly denotes a database that is in
dire need of a Design Rethink.

Best wishes


Tim F