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.

Sponsored Links:

Related Topics

Resetting a Microsoft Access 2003 AutoNumber Field
"When you delete the last record or records in a table or in a form that has an AutoNumber field, and then you compact the database, the AutoNumber field does not start at the next higher number when you add new records."


Microsoft Access 2007: Setting up Tables and Fields
This lesson will walk you through the steps of setting up a database in Access 2007. You will be setting up tables according to the plan for our bookstore scenario. You will also be setting up the fields for each table, including establishing what data types can be entered in a given field.


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


Using the DMAX() Function in Microsoft Access
Microsoft Access contains the AutoNumber data type that will automatically increment a value by 1 when set to New Values: Increment in the properties of the field.
This may not always be what the user of your Microsoft Access database requires and we have detailed an alternative approach to incrementing a fields value by a specific amount.


Microsoft Access Table Design
If you need to add a textual prefix to a Microsoft Access auto number field rather than just go with the standard AutoNumber value, this article will demonstarte what actions you should take.


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.


Autonumber not working
I created a table in a budget database without a autonumber field. I then inserted a autonumber field after creating the form by inserting a row in the table which works fine in the table, now I want to use a text box on the form with BudgetID from the autonumber field to give me the total amount of records in the database but the autonumber field is not in the record sourcedropdown.


Find next incremental value for an AutoNumber field
I'm using Access 2003/XP/2000/97 and VB6 as the front-end. I need to get the next value in the AutoNumber field before inserting the next record using SQL. How can I find out what the next value will be in an AutoNumber field?


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.


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.


Access 2003 Report - Printer Settings
I currently have the following code


Dim stDocName As String
stDocName = "rptProduct"
DoCmd.OpenReport stDocName, acNormal, , "[Autonumber] = " & Me.AutoNumber
DoCmd.OpenReport stDocName, acNormal, , "[Autonumber] = " & Me.AutoNumber

The problem I am running in to is that the printer it is using, has the right settings in the control panel's printer settings but the wrong setting in Access. I want to be able to print without going to the preview and changing the settings every time. It's a label printer, and the length and width settings are wrong. I also have to click "Use Printer Setting" on the advanced setup tab in order for it to work correctly. Any ideas on how to code these settings?


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.


How can I create a field (not autonumber) which automatically increments
How can I create a field (not autonumber) which automatically increments but allows me to reset its value to 1 at the start of each year?

Early last year I created a database of rescue animals in which I have, for each animal record, an autonumber animal ID (ID) and a field for year (of) which is auto completed. I also have an animal reference number (Ref) which is the one I use for our website etc. What I want is for me to be able to manually reset that reference number to 1 for the first record each year and for it to then auto increment throughout the year. I can then easily display our reference numbers as nnnn/yy.

I have been reading through a number of forums and Microsoft support pages but my knowledge of Access is still so basic that when I read suggested answers, I have no idea as to which screen and field to enter the given information. Please can anyone help? Be gentle with me please as this is my first post


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
Is there a way to reset an autonumber field after testing the db without having to delete the relationships?

Also can you tell the autonumber field where to start say 100 instead of


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.


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


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 (Y), 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.

I would like the autonumber to advanced based on the last autonumber for that person, not the next autonumber in the list.


tbale- autonumber field
I have a database (version 2002) that uses an autonumber filed to create unique records.

of a sudden when I try to add a new record the autonumber field starts using numbers that already exist so then I get an error message and can not save the record.


resetting an Autonumber field in a Table.
I have a module which adds records to a Table.
The Primary Key for the Table is defined as Autonumber.
As I'm in the testing phase, I've some code which deletes each record in the Table, to clear it down prior to my next effort.
However, the Autonumber field (as you would expect) just keeps trundling on.

My question (which is not vital), is

"Is there any way from the VB script that I can reset this Autonumber field either to a base value, or to a specific value before re-populating the Table?" (I suspect I may have to delete the field, then add a new one).

What I want to achieve is that each time I run a "Table reload" test I get the same values in the field