Topics Search

AutoNumber Data Type

AutoNumber Data Type
Views: 148
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:

Similar posts...


CREATE TABLE and AutoNumber fields

CREATE TABLE and AutoNumber fields Icon
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 Access Table in VB

Create Access Table in VB Icon
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

Using 2 Autonumbers in 1 table Icon
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?

Autonumber Problem?

Autonumber Problem? Icon
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.

Create an AutoNumber field that starts with a number greater than 1

Create an AutoNumber field that starts with a number greater than 1 Icon
In Microsoft Access tables, the AutoNumber field type allows you to assign a unique sequential number to each row in a table. AutoNumber fields are often used as the Primary Key, since they are unique and permanent (i.e. the value cannot be changed once it is assigned).
For new tables, AutoNumbering begins with 1, and is incremented by 1 with each new row. However, we occasionally want to start the AutoNumber at a number higher than 1.

Duplicating Current Record

Duplicating Current Record Icon
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.
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?

Help with Imports..

Help with Imports.. Icon
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?

Access 2010: Autonumber field on Web Table

Access 2010: Autonumber field on Web Table Icon
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.

Setting the value of Microsoft Access AutoNumber Field

Setting the value of Microsoft Access AutoNumber Field Icon
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.

Serial Numbering

Serial Numbering Icon
I have a form on a database, which has a Serial No control, which is autonumber. We are in a situation at the moment, where we are using two manufacturing systems, a sort of crossover period.

Because the database is linked to only one system, I am changing the Serial No, from Autonumber, to Number, and using code to increment the number, which also allows the user to type a number in, if necessary.

The problem is, the Serial No on the new system, is alphanumeric, e.g S25/1-9. What I would like to do is, for the Serial Number to increment, as normal, but allow the user to type in text, if necessary.

So, say the Serial No was 12345, and the next one 12346, the user could type in S25/1-9, for the next one, it would be12347.I don't know if this can be done at all,