Related Topics

change autonumber

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.


Resetting Autonumber Field In Code
I know. I Know. But is it possible to reset an Autonumber Field in Code. Here's what I have tried.

1) Delete all records in an existing Table.

Now I would like to change the attributes of the IDField, so

The current Field Attributes are dbAutoIncrField. I want to reset it to dbFixedField to clear the AutoNumber Attribute

fldIDX.Attributes = dbFixedField

But it doesn't like this. Error is Invalid Operation.

I ASSUME THIS MEANS I CANNOT CHANGE THE ATTRIBUTE. OH WELL.

If anyone has any insight into how to Reset an Autonumber Field without repairing and compacting the database, I woull really enjoy seeing how you did it.


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.


AutoNumber
Is it possible to change how an AutoNumber is formed. For example I have a customer database, each custor has a Customer Code which is an AutoNumber, coluld I have it so that the Customer Code began with Co- then the number i.e Co


Why Doesn't the autonumbers change?
I am having a table in which I have an autonumber field. But I want to re-apply the autonumber after sorting the data. I cannot do it. Why doesn't the sequence of autonumbers change? I want to know the fundamentals of it. Kindly


Autonumber in form and subform question
I have a form and subform as shown below. The order number in the main form and in the subform is entered manually but of course ideally the Order Number should be an autonumber.

I've tried to change it by going through the steps to change the number field to an auto number (delete the relationships, make the change, put the relationships back). Both Order Number fields should be of the same type otherwise I can't make a relationship between them and the Order Number in the subform won't automatically be the same as in the main form.

The trouble is, being an autonumber, once I start to enter item no.2 on the subform the Order Number field goes to 2 when it should still be Order Number 1.

Is there a way of making the Order Number field an autonumber but have the subform keep that number on records higher than 1?


Autonumber form and subform question
I have a form and subform as shown below. The Order Number in the main form and in the subform is entered manually but of course ideally the Order Number should be an autonumber.

I've tried to change it by going through the steps to change the number field in the table that the subform is based on to an auto number (delete the relationships, make the change, put the relationships back).

Both Order Number fields should be of the same type otherwise I can't make a relationship between them and the Order Number in the subform won't automatically be the same as in the main form.

The trouble is, being an autonumber, once I start to enter item no.2 on the subform the Order Number field goes to 2 when it should still be Order Number 1.

Is there a way of making the Order Number field an autonumber but have the subform keep that number on records higher than


How to make a custom autonumber?
I was wondering if there is a way to have a more dynamic auto number system.

I basically want to enter invoice numbers, but usually the invoice numbers are successive i.e. 5243, 5244, 5245

But sometimes there will be a jump such as 6667,6668,6670, ect.

So I was wondering if there is a way to build an autonumber system that tries to auto complete the next number for you based on the initial number, so say if I typed 7000 as an invoice number and went to the next record, I would want the next record to fill in 7001 for me unless I go and manually change it to something else.

Is it possible to change the autonumber system built into access in order to accomplish this?


Duplication check on tables. AutoNumber
I have two master tables set up.

Right now in both tables, I have the PK set on a particular field(so the PKs are not the AutoNumber)

If I manually change the PK to be the AutoNumber for each of the two tables, is there any way to prevent adding the same records twice in each table?

I am assuming this can be done in a little VBA code? Some kind of duplication check?

Could someone guide me on how to go about implmenmenting this feature


Changing Empty Field Format To AutoNumber With Macro
I have used a data definition query to add a new field to a populated table and set the format of the new field to number. The new field contains no data but other table columns do contain data.

I would like to be able to change the format of the new field from Number to AutoNumber so that it auto populates but rather than do it manually I would like to change the field format from within a macro.

I have looked at the runcommand option available as a macro action but cannot find any method to make the change from within a macro. If I had some code in a module I would be able to use the OpenModule or possibly the RunCode action in the macro but my coding skills are nil.

If there are any solution or suggestions as to how I can go about changing the field format from Number to AutoNumber from within a macro I would be most grateful for


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.


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.


Autonumber form and subform question
I have a form and subform as shown below. The Order Number in the main form and in the subform is entered manually but of course ideally the Order Number should be an autonumber.

I've tried to change it by going through the steps to change the number field in the table that the subform is based on to an auto number (delete the relationships, make the change, put the relationships back).

Both Order Number fields should be of the same type otherwise I can't make a relationship between them and the Order Number in the subform won't automatically be the same as in the main form.

The trouble is, being an autonumber, once I start to enter item no.2 on the subform the Order Number field goes to 2 when it should still be Order Number 1.


to edit autonumber
I create a table and use autonumber as its primary key. But its starting number is 3 instead of 1. Why is this case?

How can I make the autonumber start from 1?


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.


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.


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
I designed a database and enter some test records. On some of the tables I decided I would be better to have an autonumber. I deleted the records in the table and did away with the relationships. Changed the field to an autonumber. When I entered the first record it started the autonumber at 3. If I deleted that record it starts at the next number (4). I would like for it to start at 1 and go up in sequence (1, 2, 3 ect). Can I do this?


Changing number of digits in Autonumber ID
My first post here in the forum.

I have "inherited" an access database from a colleague, and after a while a problem occured.
The database is frequently accessed by a large number of users, some of which creates new entries.

For the last few days, new entries have been assigned Autonumber ID's with nine digits although all old entries have only four.

I am the only person able to directly access the database (everyone else does so through Excel) and I have not changed anything.

Any ideas on how I can change the 1500 old and 50 new entries into common format with regards to Autonumber ID?


New Record - AutoNumber
I am wondering if there is a way to autopopulate a number field in Access in the following manner. The field is called InvoiceNumber, and it is a simple integer (no letters or anything else tacked on), and for every new record it increments by 1 according to what the current max value is within the table.

I don't want to rely on the AutoNumber option as I want to be able to control and change these numbers if I wish so.