Autonumber field assigned was overwritten by a different user

[Catha]

We have an Access Database with an autonumber field we use as a unique
number. There are 5 different people entering data into a form that was
created from the main table. One of our employees entered 3 records using the
form. When she came back to those records 5 days later, it was discovered the
autonumber was assigned to another user and her data was gone. How did this
happen and is there any way to prevent this from happening again?

 

[vbasean]

Is your database split. If you have multiple users your database needs to be
split.

A split database is where the data is on a server/backend computer (It can
be someone's computer that others are networked to) and each user has a
'Front End' MS Access database that is linked to the backend.

see:
http://www.databasedev.co.uk/split_a_database.html
http://support.microsoft.com/kb/304932
http://allenbrowne.com/ser-01.html
http://www.members.shaw.ca/AlbertKallal/Articles/split/

 

[KARL DEWEY]

it was discovered the autonumber was assigned to another user
You really should not be looking at the autonumber as it needs to be behind
the scenes.
Search for the records on the data content instead of autonumber.

 

[Catha]

Our database isn't split. We have two forms they use one is where they enter
the complaint data. That one has the autonumber field that assigns a number
to that record. After they do their investigation they go to another form, do
a search for the number that was assigned to the complaint and enter the
investigation results for the complaint. When the autonumber assigned to her
complaint was pulled up in the close bugs form; that is when she discovered
the same number had been assigned to someone else's complaint.

 

[Catha]

That database has not been split. We plan to do that in the future but are
currently afraid of the results.

 

[vbasean]

you could have users open the db 'exclusively' if you don't plan on splitting
it soon.

How complex is this database?

Splitting a simple database is pretty straight forward. You make a copy of
the database.
you take one copy and make it the backend database, keep it on the
server/backend computer and delete all the forms. (deleteing the forms is not
manditory but it would create more space for data)
you take the other copy 'front end', delete all the tables and create linked
tables to the backend copy.
place a copy of the front end on each users desktop (not a shortcut, an
actual copy of the database)
This is the short cut version of splitting a db. Obviously there are a lot
of small 'gotha's' that have to be concidered, especially with a complex
Access app.

 

[Catha]

Thank you very much. I have saved the links you sent earlier in my favorites.
I read them and I think they will be really helpful when I split our database.