Turning number field to autonumber at a certain number

[Douglas J Steele]

Is there already data in the table? If so, create a new table that's
identical to the table in question, except that it has an AutoNumber field
rather than a Long Integer. Append the existing table to the new table
(using an Append query). The next AutoNumber to generate in the new table
will be one higher than the largest existing number in the table.

 

[Guest]

I have ported a database to Access.

The key field I have set up as the company number. This number is unique and
sequential. 3456, 3457, 3458 etc. But it is not autogenerated by Access.

This data base for the past several years was used primarily read data from
one Informix Database to Access. The sequential numbers where originally
created in the Informix DB and then simply copied over to access. Now we
want to actually use the access db to create new records, and enter data in
rather than just use it to read info from.

The problem is that the number field called Contacts# was not an autonumber
field in the past but now needs to be one.

I tried to changed the type of field it was but got Relationship errors. It
would not let you do it while there was relationships to that field.
I deleted the relationship and then got the message

"Once you enter data in a table you cant change the data type of any field
to the AutoNumber, even if you haven't yet added data to that field.
Add a new field to the table, and define its data type as AutoNumber.
Microsoft access then enters data in the AutoNumber field automatically,
numbering the records consecutively starting with 1."

The reality is that there are already numbers in the Contacts# field and I
don't want to start at 1. There may be a few records missing also and that
would certainly create problems. Being a little out of sequence.

Questions.
A. I was thinking that I could create some VBA routine in a form that could
automatically add one to that last record when adding a new record in that
form and put that number in the Contacts# field. Im not exactly sure how to
do that if it can be done.

B. Any other suggestions.

I want the numbering to start at somewhere in the 8000's for the next
Contact number.

Thanks.

 

[Joseph Meehan]

I have ported a database to Access.

The key field I have set up as the company number. This number is
unique and sequential. 3456, 3457, 3458 etc. But it is not
autogenerated by Access.
....

I want the numbering to start at somewhere in the 8000's for the next
Contact number.

Thanks.

You may not want an autonumber field.

Autonumber fields are designed to provide unique numbers for each
record, more or less in order. The problem is they don't always provide
them in order or them may skip a few etc. There are ways of providing new
unique numbers in order. Usually just finding the largest existing number
and adding one to in a query or form. This can have the advantage of doing
things like making the next number start at 50,000 then next January one
make change that to 60,000 etc. or whatever.

Autonumbers generally should not be used where real live people will see
the numbers as they tend to get concerned when autonumber does something
they did not expect.

BTW Douglas's solution should do exactly what he said and if you decide
autonumber is what you want to use, then do use his instructions.

 

[Albert D.Kallal]

Yes, you can in the forms after update event use:

dim lngNextNumber as long

if isnull(me.MyNumberID) = true then

lngNextNumber = dmax("MyNumberID","tblCustomers") + 1

me.MyNumberID = lngNextNumber

end if

If you application is not multi-user, then you could also use the after
insert event.

If you want to change the next starting number to 10,000, simply enter the
number manually into the form...since the above code will only run if your
number field is blank. Also, if you set the default of the MyNumber field to
zero, then the above code needs to be changed

if me.MyNumberID = 0 then

So, I don't know if your designs allow null fields or not, but just keep
that in mind to test for a zero, or a null depending on how you set things
up...

 

[Guest]

It appears that your suggestion does not do (if I did it right) exactly what
I want.
Please tell me if Im wrong,

For a variety of reasons some of the Contacts #'s have been deleted. This
means that the record numbers are like
1
2
4
6
7
8
9

When I do what you have suggested the numbering is out because of these
missing numbers.

Am I right in assuming this to be true. And is there a work around. Someone
else is suggesting in the news group to just do some sort of increment
counter.

Regards


The problem with my current numbers as I mentioned in my first post is that
the numbers are not perfectly in order. When the

 

[Guest]

Autonumber fields are designed to provide unique numbers for each

record, more or less in order. The problem is they don't always provide
them in order or them may skip a few etc. There are ways of providing new
unique numbers in order. Usually just finding the largest existing number
and adding one to in a query or form. This can have the advantage of doing
things like making the next number start at 50,000 then next January one
make change that to 60,000 etc. or whatever.

Autonumbers generally should not be used where real live people will see
the numbers as they tend to get concerned when autonumber does something
they did not expect.

BTW Douglas's solution should do exactly what he said and if you decide
autonumber is what you want to use, then do use his instructions.

So you are suggesting that I write some code that just "manually" calculates
and adds the number to the field.

How would I do this?
Would I have a button eg. New Record and this automatically finds the last
number in a table, adds one to it and then creates a new record adding that
new number to the right field. Any suggestions? Advice.

 

[Guest]

Thanks I will try this.