Table ?

[Michelle]

I have a table for equipment.
If the equipment has a service tag we use it as the primary key if the
equiment does not have a service tag we want to start at 0000001 and go up.
Is there anyway to code this or have the primary key set to autonumber but
let the user enter a different number if you have a service tag?
Thanks
Michelle

 

[Klatuu]

It is never a good idea to use an autonumber field for anything a human may
see or has any meaning to your application. An autonumber field should only
be used for surrogate primary fields and to relate child tables.

If you want to use an incrementing number, regardless of whether a piece of
equipment has a service tag. There is a common way to do this. How you do
it depends on whether it is a single or multi user application.

Let me know, and I will post back with the proper technique.

 

[Evi]

Be aware that if your Primary Key field is the Foreign Key field in another
table then resetting it will screw up your data.
Resetting an autonumber is a fairly horrible process and you certainly can't
type things directly into an autonumber field - I've described what's
involved in another message and I'm not even sure if that method works in
Acc2007 which (rightly) hangs on to its autonumbers.
You may perhaps be best off letting an Autonumber Primary Key field do its
job in the background, reliably joining up your tables but having both the
ServiceTag field and a seperate field called eg NoTag which contains your
incrementing values for equipment with no service tag.


To combine the two fields into one in a query (for display or sorting
purposes) you can use

IIF(IsNull(ServiceTag),NoTag, ServiceTag)

To put a sequential number into NoTag, have a search in Google Access Groups
for Sequential Number DMax and you will see many instances of how to put an
incrementing number in a field using a relatively simple coding in your
form.

Evi