# AutoNumber Starting on a certain number

#### gheyman

##### Well-known Member
I need a field that automatically numbers. But for what I am doing now, I need the numbering in this new table to start with number 279.

Is this possible?

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
if you put the 279 in a cell, then in the next two consecutive cells put 280 and 281. Then you can highlight those three and drag it will sequentially fill the numbers.

if you put the 279 in a cell, then in the next two consecutive cells put 280 and 281. Then you can highlight those three and drag it will sequentially fill the numbers.
Lynn,
This is an Access question, not Excel!

gheyman,
You would need VBA code to do that (you cannot really control an Autonumber field - and they shouldn't be used for this purpose anyway).
How is the data entered into the table? If manually through a Form, you can have VBA code run upon new record addition that will populate this field according to your rules.
If you are importing data, you will need to create a process (i.e. Macro) that runs and updates the field after the import.
We can help you with either of those options if you let us know how new records are created.

Thanks for the help. But if I am understanding your reply correctly, that's not going to work for what I need.

When I enter a new Row (Record) I want the first field to auto number. Just as the Data Type "Autonumber" does. This Access function starts with the number "1" for the first record. If you add a new record it auto numbers it "2" and so on.

This is what I want, but I want the first record to start at 279.

If I understood your solution correctly, that builds a table with columns populated by dragging down the first number...

Lynn,
This is an Access question, not Excel!

gheyman,
You would need VBA code to do that (you cannot really control an Autonumber field - and they shouldn't be used for this purpose anyway).
How is the data entered into the table? If manually through a Form, you can have VBA code run upon new record addition that will populate this field according to your rules.
If you are importing data, you will need to create a process (i.e. Macro) that runs and updates the field after the import.
We can help you with either of those options if you let us know how new records are created.

Oops - I always forget to look at the forum when looking at zero reply posts

Thanks Joe.

The data us being entered Manually through a Form. There will not be any Importing into this table.

OK. That process is explained here: Automatically Increment a Fields Value | Database Solutions for Microsoft Access | databasedev.co.uk

You would just want to add one more step in your code.
In between these two blocks of code:
Code:
``````    'Find highest Employee ID in the tblPracticeEmployeeData table and add 1
lngNextID = DMax("[lngID]", "tblPracticeEmployeeData") + 1

'Assign function the value of the Next ID
NewCustID = lngNextID``````
you want to make sure it starts at 279 like this:
Code:
``````    'Find highest Employee ID in the tblPracticeEmployeeData table and add 1
lngNextID = DMax("[lngID]", "tblPracticeEmployeeData") + 1

[COLOR=#ff0000]    'Make sure number is never less than 279
If lngNextID < 279 Then lngNextID = 279
[/COLOR]
'Assign function the value of the Next ID
NewCustID = lngNextID``````

Last edited:
Thanks again. Will give it a shot

Replies
5
Views
182
Replies
5
Views
404
Replies
9
Views
221
Replies
9
Views
294
Replies
10
Views
396

1,203,124
Messages
6,053,645
Members
444,676
Latest member
locapoca

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back