Autonumber / Incrementing Value Boo Boo

[Andy Roberts]

I think I have made a problem for myself by the way I have structured my
table.

I have a database which has in it legacy data relating to tenders and jobs
Up to now everything has been paper based hence the need for the database).
Every tender we send out has an incrementing number which is 4 digits and
just increments by 1 each time. The same goes for any job we do (4 digit
sequential number) and I therefore have the same problem in both tables.

I have a tblTenders with a primary key which is an autonumber called
TenderID and a second field which is a description of the tender called
TenderDesc.

I also have a tblJobs with a primary key which is an autonumber called JobID
and a second field which is a description of the job called JobDesc.

I have a button on a main form which opens an "Add New Tender Form" which is
blank. What I need to happen now is that the TenderID field needs to find
the largest number (ie the last tender) and automatically assign a new
number which is the last number +1. My problem seems to be that the
tenderIDs I have imported (over 3000 of them) are all in the Primary key
(autonumber) field. What is the best way to combat this and what code do I
need?

I need to do the same thing for the job table (and eventually have the
tender turn into a job using an append query via a button). The append
query would then need to generate the unique job number. but one step at a
time....

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007
Liverpool, UK

 

[Tom Wickerath]

Hi Andy,

An autonumber data type may not be the best choice in this situation, as
this data type should be considered totally meaningless. If you decide to use
a Long Integer instead, then try Access MVP Roger Carlson's demo, available
here:

http://www.rogersaccesslibrary.com/...?TID=395&SID=1816bf796cf1d72b9615d79z6a63e9z1

On the other hand, if you wish to maintain the Autonumber field, and you
have existing values that you need to populate (that have not already been
used), then you can use an append query to append your data to this table.
For the autonumber field, you would append the existing TenderID data.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________