duplicate keys

[M.Sellers]

What do I need to do to stop users who are adding new records in a
mutiuser database from having the database give them the same number?
To create a new record, a VB procedure runs, gets the number of the
last record, and adds one to it. That has worked fine with just one
user, but now I have many people entering data at once. Any
suggestions?

[Joe Fallon]

Give them the number and Save the record immediately. (If possible).
Then move the focus to the next field.
The user probably won't even notice that they left edit mode.

M.Sellers <msel...@marin.k12.ca.us> wrote in message
news:3702481e...@msnews.microsoft.com...

[Richard Summers]

Use Auto Numbering, set the field type to AutoNumber. You
may find it difficult to upgrade an existing table, as you can not
change a field of type number to autonumber (Access will not
allow it). In this case you will have to copy the table to
a backup, delete the contents of the original, change the type
to autonumber,copy back from the backup. (Dont rename the
original table as the relationships are preserved)

It will be easier if you use VB code to copy from the backup.
As this will set the autonumber to your existing 'value'.
for example :

Set myRsTarget = myDb.OpenRecordset("SELECT * FROM [PROSPECT]")
Set myRsSource = ErrorDb.OpenRecordset("SELECT * FROM [BACKUP_PROSPECT]
WHERE [REFERENCE] <> NULL

' Always a good idea to do this
myRsSource.MoveLast
myRsSource.MoveFirst

' Copy all records in source
While Not myRsSource.EOF

' Add new record if necc.
myRsTarget.FindFirst "[REFERENCE] = " &
myRsSource.Fields("REFERENCE")
If myRsTarget.NoMatch Then
myRsTarget.AddNew
End If

' copy over all fields
For Each myfield In myRsSource.Fields
myRsTarget.Fields(myfield.Name) =
myRsSource.Fields(myfield.Name)
Next
myRsTarget.Update
Wend

I have removed the error trapping and a progress bar with cancel button,
which you
may find useful if you have alot of records.

Other ideas:-
Create a new table which contains just the next number to be used. When you
wish to
add a new record, lock it against update, increment the number and save it
back to
this new table. You know have a unique number to use. If you find the table
locked,
you know someone else is adding and thus must retry.

If you do not want an autonumber as the primary key, create a new column
of say the user ID and make this part of the primary key. If you reference
all
your table through queries, then (i asumme) you can make the querie use
the UsrID as a parameter and hence will not need to change much code. I
have not tried this myself, just an idea

M.Sellers wrote in message <3702481e...@msnews.microsoft.com>...