AutoNumber Data Type

The AutoNumber data type stores an integer that Microsoft Access increments (adds to) automatically as you add new records. You can use the AutoNumber data type as a unique record identification for tables having no other unique value

Sponsored Links:

Related Topics

CREATE TABLE and AutoNumber fields
I'm trying to create a table that contains an AutoNumber field using the following SQL:

CREATE TABLE [tmpTable] ([ID] AutoNumber);

but I get a COM error stating that there's an error in the field description. If I use Long as the data type it accepts it, but according to w3schools online AutoNumber is a perfectly valid Access data type.

I've tried it with all lowercase, with PRIMARY KEY and a few other combinations but it simply refuses.

I'm using ADO (#importing msado15.dll).


CREATE TABLE and AutoNumber fields
I'm trying to create a table that contains an AutoNumber field using the following SQL:

CREATE TABLE [tmpTable] ([ID] AutoNumber);

but I get a COM error stating that there's an error in the field description. If I use Long as the data type it accepts it, but according to w3schools online AutoNumber is a perfectly valid Access data type.

I've tried it with all lowercase, with PRIMARY KEY and a few other combinations but it simply refuses. Can anyone tell me what I'm doing wrong here?

I'm using ADO (#importing msado15.dll


Create Access Table in VB
Allows the programmer to create an MS Access table in Visual Basic where the primary key's field data type is set to AutoNumber. It is not like creating the primary key field in Access where you can select AutoNumber date type. In VB 5 and 6 you can't request AutoNumber for a field type in the SQL Create Table string, it does not exist. So to create a table in VB where primary key's numeric field type will be AutoNumber, you have to do it the way the included source code shows. Hope this helps.


Using 2 Autonumbers in 1 table
I have 2 tables (Registrations and Courses), where each table has a Primary Key with a data type of Autonumber (RegID and CourseID). I need to add the PK from the Courses table to the Registration table which causes problems (only 1 Autonumber per table).

In order to avoid this problem, I would like to change the Data Type for the PK in the Courses table, but with allowing the user to automatically create the value of each record (generating a custom CourseID - I.e. Course01, Course02, Course03, etc).

Any ideas on how to create the custom field for data entry purposes?


Autofill field based on autonumber field
I have an add-only form that has an autonumber field. This autonumber gets a new number once you fill any other field on the form.
I need to have on the same form an autofill field that has the autonumber included on it, besides other data from the same form.
This autofill field should be stored on a table.

Let's say
1. Opened Form
2. Autonumber populated once I type on any field (ie 5623)
3. Autofill field empty but once ?I click on it? I get this:
DCA/TX-09-5623 (This field should be stored on a table)
where:
'DCA/TX' is the department I work for (combo box on form)
'09' is the last two digits of the current year (date on form)
'5623' is the autonumber given by MS Access

The objective is to generate a code name on things that come to the departments based on Access autonumber feature


Reset autonumber in a concatenate field
I would like to build a concatenate field using Year, ID Number, and a 3-digit autonumber. Year is a combo box in a form (YYYY), NumID is the two or three digit ID Number for a person, and then the three digit autonumber. I would also like the three digit autonumber to follow in sequence based on Year and NumID. For example, when starting a new year I would like the autonumber to reset to 000. When entering data for multiple people, I would like the autonumber to advanced based on the last autonumber for that person, not the next autonumber in the list.


Importing existing Autonumber fields
I have been using an online database and having issues with it. I might have to migrate my existing database to Access. The problem is I don't know how to migrate my existing autonumber fields into Access without messing my database up.

I need to be able to use my existing autonumbers as an autonumber data type.


Importing existing Autonumber fields
I have been using an online database and having issues with it. I might have to migrate my existing database to Access. The problem is I don't know how to migrate my existing autonumber fields into Access without messing my database up. I need to be able to use my existing autonumbers as an autonumber data type


Help with Imports..
I have an excel database in which I am trying to import into the access db I am working on, but gave up as I had some field type mismatches (CUSTOMER ID).

* In access I tried using Autonumber. (for the CUSTOMER ID field), when importing I bumped into trouble, the field in excel had general type and it is of format UKP#####. So it is not being imported.

so I decided to copy and paste the details field by field. and finally once it is done I added another field CUSTOMER ID, set it autonumber with format UKP#####. It worked fine for the first 172 records, but the 173rd record suddenly jumped up to 5495.

Or is there another way to convert the type of the excel db to the autonumber while importing to access?


Autonumber Problem?
Here is my issue. I have 3 Access dbs that all use a field called "product id" that uses autonumber. The person who set this up is using the auto number like a part number.

I need to combine all three to together but when I do it will not let continuing using autonumber because their is data in the field now.

Is their anyway to get around this. I still need to use autonumber after combining.


Duplicating Current Record
Below is the following code that I have used to duplicate a record in a table where there is a Primary Key that is a data type of AutoNumber. My form does have subforms, but that informationis not necessary to duplicate.
[CODE]
The problem is that the AutoNumber field becomes "0." I believe this is happening because of the statement in the code "On Error Go To 0"

Can someone explain to me why this going to error instead of creating a new AutoNumber in the Auto field?


Limit to List difficulty
I am looking to put a combo-box on a form. The box is set to query Table2 and get the ID, and Type. The selected index will store the ID in Table1.Type. I would like to hide the Index column and set the combo-box to Limit to List: NO so that the user can either click on the Type or Type in a new Type which will be added to Table2. Can anyone help me with this one. When I try to set Limit to List to NO, Access yells at me because my first column is hidden and I have to fix my Columnwidth then set Limit to List to NO.

My Database:
Table1:

ID - autonumber Type - number Description - text

Table2:

ID - autonumber Type - text

Table2.ID has a one to many relationship with Table1.Type


AutoNumber Info
I just had a quick couple questions about AutoNumber fields.

1. In order to us the Primary Key AutoNumber field "Client ID" as a Foreign Key in another field - does the FK have to be set to the type "number"?

2. Does the FK name/title have to be the same, in this example "Client ID", or could they hypothetically be different? FK "Related ID From Clients"

3. If you format the data in the PK AutoNumber field as such:

"Client-0001", "Client-0002" etc.

Is it misleading because under the formatting the number in the table is just "1", "2", and so on? Would this screw up a search for one of the formatted IDs?

4. Is it bad practice to name tables or other rows or whatever with spaces in the name? Should "My Row" be "MyRow" or "My_Row"?

Many thanks - I'm new to this so I appreciate the guided help. I've read so many random Google searches the past week my brain is just toast


Convert Long Integer Number to Autonumber
Tried to convert a Long Integer column to Autonumber and get the error:
"Once you enter data in a table, you can't change th edata typ of any field to AutoNumber, even if you haven't yet added data to the field."

Just normalized many tables into one table by turning off the Autonumber.
Used a Hashing method to keep track across the many updates.

The field that I need to change back to an autonumber starts at 1,000 to 1,832 - has gaps (tht was the first table), then 2,000 to 2,304 - has gaps (that was the second table) . and so on.

All I need to do now is to convert my first column (Long Integer) back to an Autonumber - and keep the data carefully migrated in.


Inserting records into tables with autonumber
how do I enter data into tables with columns which have datatypes of Autonumber?

Here is the code column that has a datatype autonumber when I created it in SQL Management Studio

CaseNumber int IDENTITY(1,1) NOT NULL PRIMARY KEY

When I imported the tables into Access, the column CaseNumber was set to Autonumber. I've done some research but I can't get it to work.

What I found goes like this:
INSERT INTO (col2,col3) values ('value2','value3');
col1 needs not to be included because it is autonumber.


Inserting records into tables with autonumber
How do I enter data into tables with columns which have datatypes of Autonumber? Here is the code column that has a datatype autonumber when I created it in SQL Management Studio

CaseNumber int IDENTITY(1,1) NOT NULL PRIMARY KEY

When I imported the tables into Access, the column CaseNumber was set to Autonumber. I've done some research but I can't get it to work.


Access 2010: Autonumber field on Web Table
I've imported some excel sheets into new tables. These sheets were from another existing Access database, and already contained an ID field, which I selected as the primary key when importing.

I want this field to be an autonumber, but I can't change the type. For that matter, I can't even seem to add a new Autonumber field to this table. How do I do this?

I need these tables to have an autonumber. I looked at using the standard work around, using DMax, but aggregate functions aren't allowed with web tables.


How to Manage this type of Data?
I would like to ask your kind advise for this case: I am using this relationship among 2 tables and data entry through Form and subform where "TestNo" is key for relationship.

TblT8

TestNo PK AutoNumber
TestDate
TechID CB:Value List
KilnNo CB:Value List
MtrlID CB:Value List
Note

TblRT8
ResultNo PK AutoNumber
TestNo FK
PrdDate
Top
Middle
Bottom

this relationship is working fantastic but now I need a third tble where need to add data link to Top, Middle & Bottom Values in each entry.

The fields for data required in 3rd table is only

W1 1st Weight


Setting the value of Microsoft Access AutoNumber Field
By using an append query, you can change the starting value of an AutoNumber field in a table to a number other than 1.
Microsoft Access always numbers AutoNumber fields beginning with the number 1. If the table has data in it already, the starting value of the autonumber will be higher than the highest value already in the table. You cannot manually edit an AutoNumber field or change its starting value.


Autonumber field order
I need to add an autonumber to existing data in a particular order in Access 2007. I've set the autonumber field to long integer, increment, no duplicates. Before adding the autonumber field my table is sorted in the order I need the autonumbers to be assigned, starting with 1. However, the autonumbers are assigned in a totally different order when I reopen the table. They start with 1, but not on the record I need it to. I've done "compact and repair" between creating the Autonumber field and actually opening the table, but no luck.