There are lots of possible solutions: try googling for something "Access
custom autonumbers multi-user".

FWIW, this is what I do:

 ' this is a partial command!
 jetSQLShort = "INSERT INTO MyTable(RequiredField, IDNum) " & _
   "VALUES( ""Default"", "

 ' need to error trap this, obviously
 dwInsertNumber = DMax("IDNum", "MyTable") + 1

 ' now find a new number
 Do While True
   ' try to insert it but trap any error
   On Error Resume Next
   db.Execute jetSQLShort & dwInsertNumbr * ")", dbFailOnError

   ' if that worked, then we've finished already
   If err.number = 0 then exit do

   ' if we are still here, then someone else already used
   ' that number. Need to screen for other errors too, like
   ' locked table etc etc

   ' restore normal runtime error handling (nb what about
   ' integer overflow..?
   On Error Goto 0
   ' and look at next number
   dwInsertNumber = dwInsertNumber + 1

 Loop

 ' on exit dwInsertNumber holds the correctly inserted number

Hope that helps

Tim F