Autonumber field properties keep changing to random

[Jim]

My autonumber fields properties keep changing to random instead of
sequential. I reset them to sequential but when I open the database again
and add a new record it's back to random. Can anyone give me any clues as to
why this is happening please?

 

[Brendan Reynolds]

Is the database replicated? Replicating a database automatically changes the
New Values property of all AutoNumber fields to Random.

How do you 'reset them to sequential'? I was not aware that there was any
way to do that. In my experience, once the New Values property has been
changed to Random, Access will not change it back.

 

[Jerry Whittle]

To add to Brendan's ideas, do you mean that the New Values property for the
autonumber field changes back to Random or that the numbers just seem to be
random?

Even though the New Values property says Increment, the numbers produced
aren't garenteed to be that way. Random does definitely scramble them though.
If you must have incremented numbers with no holes, an autonumber is the
wrong tool.

 

[Jim]

Yes I can confirm that the database is being replicated via Windows
Briefcase. I also get locked out the design when I've replicated with the
message "This is not the design master, Etc".

All I want is to have a system of numbering and thought autonumber would be
the answer. Is there a better way?

Thanks
Tim

 

[Jerry Whittle]

One way is to use the DMax function to pull the largest number out of the
table and add one to it. For this to work you need to be using a form and
fire the code on one of the form's events such as Before Insert. If very few
records are added or only one person is adding records at a time, Before
Insert is good enough.

 

[Brendan Reynolds]

Both of those things are supposed to happen when a database is replicated.
The AutoNumber fields have to be random in a replicated database, it's the
only way to prevent duplicate values being created in different replicas.
And design changes can only be made to the design master.

Here's a link to a Microsoft Knowledge Base article describing 'How to
Create a Multiuser Custom Counter' ...

http://support.microsoft.com/kb/210194

 

[John Vinson]

Both of those things are supposed to happen when a database is replicated.
The AutoNumber fields have to be random in a replicated database, it's the
only way to prevent duplicate values being created in different replicas.
And design changes can only be made to the design master.

Here's a link to a Microsoft Knowledge Base article describing 'How to
Create a Multiuser Custom Counter' ...

http://support.microsoft.com/kb/210194

Even that won't work, I don't think, with a replicated database. If
two people have two different replicas, on totally different machines,
not connected by any network, and each creates a new record - each
will expect to get the next available ID. If they both got a replica
(after synchronization) with 3125 as the highest ID in the table,
they'll BOTH - quite properly! - get 3026. When you again synchronize,
you'll have a conflict.


John W. Vinson[MVP]

 

[Brendan Reynolds]

You're quite right, of course, John.

Various possibilities come to mind. Users could be assigned ranges of
numbers, or the number could be combined with another item of data such as a
user name. Neither of those would ensure strict sequential numbering,
though. I guess one possible solution, if sequential numbering is required
in a replicated scenario - and assuming all PCs involved have access to the
Internet - would be to create a web service to retrieve the next available
number from a common source.

 

[Jim]

Thanks to all responders. I'm a bit stunned by the speed of you responses
and your knowledge of course. You've given me plenty to work with. Thanks
again.

Regards
Jim