Changing Data Type from Number to AutoNumber

  • Thread starter Thread starter SamDev
  • Start date Start date
S

SamDev

I have created a table and used the number data type for a field named
Invoice Number. I did not use the AutoNumber data type as I needed to input
over a 1000 records that were in no particular order that had already been
given invoice numbers and mailed out to clients etc. Now, I have completed
the input of all the existing invoices, I want the field to start numbering
automatically, therefore, autonumber data type, but I can't change the data
type of a field with data in it.

Any ideas of how to start the autonumbering - much thanks!!
 
I figured it out....

I created a new table with the exact same fields and data type with the
exception of Invoice Number. My Invoice Number in the new table was an auto
number. I then did an append query that transferred the data from the
original to the new. But had to be careful with relationships etc....
 
I have created a table and used the number data type for a field named
Invoice Number. I did not use the AutoNumber data type as I needed to input
over a 1000 records that were in no particular order that had already been
given invoice numbers and mailed out to clients etc. Now, I have completed
the input of all the existing invoices, I want the field to start numbering
automatically, therefore, autonumber data type, but I can't change the data
type of a field with data in it.

Any ideas of how to start the autonumbering - much thanks!!
Click to expand...

Well... DON'T.

Autonumbers have one purpose only: to provide a guaranteed unique key.
They *WILL* have gaps, and can even become random. Auditors tend to
get antsy when they see invoices 1221, 1223 and 1224 but find no trace
of 1222; and they'll freak right out when three consecutive invoices
are numbered 1228413224, -884208416 and 1923439846.

Instead, keep your Long Integer and put VBA code in the Form's
BeforeInsert event to increment it:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me![Invoice Number] = DMax("[Invoice Number]", "[Invoices]") + 1
End Sub

John W. Vinson[MVP]
 
Back
Top