Autonumber

Is there a way to start autonumber at a number other than 1?

If so, any help would be appreciated.
TIA
Ally.

Use an append query to append a record 1 less than where you want to start.
Delete that record. The next record will begin as you wished.

Details:
Set AutoNumbers to start from ...
at:
http://members.iinet.net.au/~allenbrowne/ser-26.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ally H." wrote in message
...
Is there a way to start autonumber at a number other than 1?

If so, any help would be appreciated.
TIA
Ally.

Ally H. wrote:
Is there a way to start autonumber at a number other than 1?

If so, any help would be appreciated.
TIA
Ally.

Yes, but chances are you should not use it. :-)

Autonumber is not designed to provide consecutive numbers. Autonumber
is designed only to provide a set of unique numbers, generally used to
provide a means of linking tables. The user of a database generally should
never see a autonumber as it tends only to confuse them.

If you want a number that will maintain order 1, 2, 3 etc You really
need to do that yourself in VB. If you need that someone I am sure, will
post some sample code.

In short don't count on Autonumber to provide consecutive numbers, as it
often does not.

From the help file:

About primary keys
The power of a relational database system such as Microsoft Access comes
from its ability to quickly find and bring together information stored in
separate tables using queries, forms, and reports. In order to do this, each
table should include a field or set of fields that uniquely identifies each
record stored in the table. This information is called the primary key of
the table. Once you designate a primary key for a table, Access will prevent
any duplicate or Null values from being entered in the primary key fields.
There are three kinds of primary keys that can be defined in Microsoft
Access:
AutoNumber primary keys
An AutoNumber field can be set to automatically enter a sequential number as
each record is added to the table. Designating such a field as the primary
key for a table is the simplest way to create a primary key. If you don't
set a primary key before saving a newly created table, Microsoft Access will
ask if you want it to create a primary key for you. If you answer Yes,
Microsoft Access will create an AutoNumber primary key.



From the help file on auto numbers changing start number:

For a new table that contains no records, you can change the starting value
of an AutoNumber field whose NewValues property is set to Increment to a
number other than 1. For a table that contains records, you can also use
this procedure to change the next value assigned in an AutoNumber field to a
new number.
If your original table contains property settings that prevent Null values
in fields, you must temporarily change those properties. These settings
include:
The Required field property set to Yes
The Indexed field property set to Yes (No Duplicates)
A field and/or record ValidationRule property that prevents Null values in
fields
Create a temporary table with just one field: a Number field. Set its
FieldSize property to Long Integer and give it the same name as the
AutoNumber field in the table whose value you want to change.
In Datasheet view, enter a value in the Number field of the temporary table
that is one (1) less than the starting value you want for the AutoNumber
field. For example, if you want the AutoNumber field to start at 100, enter
99 in the Number field.
Create and run an append query to append the temporary table to the table
whose AutoNumber value you want to change.
How?
Create a query that contains the table whose records you want to append to
another table.
How?
In the Database window, click Queries under Objects, and then click New on
the Database window toolbar.
In the New Query dialog box, click Design View, and then click OK.
In the Show Table dialog box, click the tab that lists the tables or queries
whose data you want to work with.
Double-click the name of each object you want to add to the query, and then
click Close.
Add fields to the Field row in the design grid, and if you want, specify
criteria and a sort order.
To view the query's results, click View on the toolbar.
In query Design view, click the arrow next to Query Type on the toolbar,
and then click Append. The Append dialog box appears.
In the Table Name box, enter the name of the table you want to append
records to.
Do one of the following:
If the table is in the currently open database, click Current Database.
If the table is not in the currently open database, click Another Database
and type the path of the database where the table is stored or click Browse
to locate the database. You can also specify a path to a Microsoft FoxPro,
Paradox, or dBASE database, or a connection string to an SQL database.
Click OK.
Drag from the field list to the query design grid the fields you want to
append and any fields you want to use for setting criteria.
If all the fields in both tables have the same names, you can just drag the
asterisk (*) to the query design grid. However, if you're working in a
database replica, you'll need to add all the fields instead.
If you have a field with an AutoNumber data type, do one of the following:
Add AutoNumber values automatically
To have Microsoft Access add AutoNumber values automatically, don't drag the
AutoNumber field to the query design grid when you create the query.
With this method, Access appends records and automatically inserts
AutoNumber values. The first record appended has a value that is one larger
than the largest entry that was ever entered in the AutoNumber field (even
if the record that contained the largest AutoNumber value has been deleted).
Use this method if the AutoNumber field in the table you're appending to is
a primary key, and the original table and the table you're appending to
contain duplicate AutoNumber values.
Keep the AutoNumber values from the original table
To keep the AutoNumber values from the original table, drag its AutoNumber
field to the query design grid when you create the query.
If the fields you've selected have the same name in both tables, Microsoft
Access automatically fills the matching name in the Append To row. If the
fields in the two tables don't have the same name, in the Append To row,
enter the names of the fields in the table you're appending to.
In the Criteria cell for the fields that you have dragged to the grid, type
the criteria on which additions will be made.
To preview the records that the query will append, click View on the
toolbar. To return to query Design view, click View on the toolbar again.
Make any changes you want in Design view.
Click Run on the toolbar to add the records.

Delete the temporary table.
Delete the record added by the append query.
If you had to disable property settings in step 1, return them to their
original settings.
When you enter a record in the remaining table, Microsoft Access uses an
AutoNumber field value one (1) greater than the value you entered in the
temporary table.

--
Joseph E. Meehan

26 + 6 = 1 It's Irish Math

"Joseph Meehan" wrote in message
...
Ally H. wrote:
Is there a way to start autonumber at a number other than 1?

If so, any help would be appreciated.
TIA
Ally.

Yes, but chances are you should not use it. :-)

Actually, while I agree with you on the topic of when you should and
shouldn't use autonumbers. this question can have a legitimate reason,
Joseph. You might have a situation where you have historic data that you
want in the database, and you want to use autonumbers from now on.

If that's the case, then appending the existing data to Autonumber table
will ensure that subsequent numbers don't conflict with the existing
numbers.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

Douglas J. Steele wrote:
"Joseph Meehan" wrote in message
...
Ally H. wrote:
Is there a way to start autonumber at a number other than 1?

If so, any help would be appreciated.
TIA
Ally.

Yes, but chances are you should not use it. :-)

Actually, while I agree with you on the topic of when you should and
shouldn't use autonumbers. this question can have a legitimate reason,
Joseph. You might have a situation where you have historic data that you
want in the database, and you want to use autonumbers from now on.

If that's the case, then appending the existing data to Autonumber table
will ensure that subsequent numbers don't conflict with the existing
numbers.

I do agree with that, and I am sure some other reasons for Autonumber.
Which is why I included an answer to the question and stated "_chances are_
you should not use it." I may have overstated the case for not using it
however so your comments are appreciated, true and useful.

--
Joseph E. Meehan

26 + 6 = 1 It's Irish Math

Thanks for all your help everyone, the append query works fine. I'm not
actually using the number to rely on sequential numbering but, i have to
split the database so that it can be used on 3 different sites, but chances
are they'll want it back in one place again eventually, so I just want to
ensure that I dont have duplicate numbers.

Thanks Again
Ally.

"Joseph Meehan" wrote:

Douglas J. Steele wrote:
"Joseph Meehan" wrote in message
...
Ally H. wrote:
Is there a way to start autonumber at a number other than 1?

If so, any help would be appreciated.
TIA
Ally.

Yes, but chances are you should not use it. :-)

Actually, while I agree with you on the topic of when you should and
shouldn't use autonumbers. this question can have a legitimate reason,
Joseph. You might have a situation where you have historic data that you
want in the database, and you want to use autonumbers from now on.

If that's the case, then appending the existing data to Autonumber table
will ensure that subsequent numbers don't conflict with the existing
numbers.

I do agree with that, and I am sure some other reasons for Autonumber.
Which is why I included an answer to the question and stated "_chances are_
you should not use it." I may have overstated the case for not using it
however so your comments are appreciated, true and useful.

--
Joseph E. Meehan

26 + 6 = 1 It's Irish Math

I definitely wouldn't trust sequential Autonumbers in that case.

You could try using random Autonumbers, and hope that there isn't going to
be a duplication (this is what Access does when you use Replication to keep
multiple copies in synch), or you could add an additional field that
contains the site detail. That way if you ever have to merge them together,
you'd be able to distinguish record 123 from Site A vs. record 123 from Site
B. (It can be a little hairy to consolidate them, but it's certainly doable.
I used that approach in Access 2.0, which didn't have Replication built into
it)


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Ally H." wrote in message
...
Thanks for all your help everyone, the append query works fine. I'm not
actually using the number to rely on sequential numbering but, i have to
split the database so that it can be used on 3 different sites, but
chances
are they'll want it back in one place again eventually, so I just want to
ensure that I dont have duplicate numbers.

Thanks Again
Ally.

"Joseph Meehan" wrote:

Douglas J. Steele wrote:
"Joseph Meehan" wrote in message
...
Ally H. wrote:
Is there a way to start autonumber at a number other than 1?

If so, any help would be appreciated.
TIA
Ally.

Yes, but chances are you should not use it. :-)

Actually, while I agree with you on the topic of when you should and
shouldn't use autonumbers. this question can have a legitimate reason,
Joseph. You might have a situation where you have historic data that
you
want in the database, and you want to use autonumbers from now on.

If that's the case, then appending the existing data to Autonumber
table
will ensure that subsequent numbers don't conflict with the existing
numbers.

I do agree with that, and I am sure some other reasons for
Autonumber.
Which is why I included an answer to the question and stated "_chances
are_
you should not use it." I may have overstated the case for not using it
however so your comments are appreciated, true and useful.

--
Joseph E. Meehan

26 + 6 = 1 It's Irish Math

Isn't this a perfect situation for a GUID?


"Ally H." wrote in message
...
Thanks for all your help everyone, the append query works fine. I'm not
actually using the number to rely on sequential numbering but, i have to
split the database so that it can be used on 3 different sites, but
chances
are they'll want it back in one place again eventually, so I just want to
ensure that I dont have duplicate numbers.

Thanks Again
Ally.

"Joseph Meehan" wrote:

Douglas J. Steele wrote:
"Joseph Meehan" wrote in message
...
Ally H. wrote:
Is there a way to start autonumber at a number other than 1?

If so, any help would be appreciated.
TIA
Ally.

Yes, but chances are you should not use it. :-)

Actually, while I agree with you on the topic of when you should and
shouldn't use autonumbers. this question can have a legitimate reason,
Joseph. You might have a situation where you have historic data that
you
want in the database, and you want to use autonumbers from now on.

If that's the case, then appending the existing data to Autonumber
table
will ensure that subsequent numbers don't conflict with the existing
numbers.

I do agree with that, and I am sure some other reasons for
Autonumber.
Which is why I included an answer to the question and stated "_chances
are_
you should not use it." I may have overstated the case for not using it
however so your comments are appreciated, true and useful.

--
Joseph E. Meehan

26 + 6 = 1 It's Irish Math