Autonumber

Structure

Your table contains a numerical field of type Long. In this example it will be called ID.

For multi-user stability, and to have the option to start at any number, add a 'system' table:

USysKey
0¬fieldtype
*keyNameText 50
 keyValueLong

Implementation

Put the code below in a standard module. Note that you have to check the error handler.

If more users try to get a new number at the same time, there will be locking conflicts. Please inform me about anything that is missing from the code. Send your message to bas at heuveltop dot nl. 

Public Function getNewKey(cKeyName As String, Optional nInitValue = 1) As Long
'assumes table USysKey(keyName:text, keyValue:Long)
'keyValue means the next free number available
Dim rs As Recordset
Dim nRes As Long
On Error GoTo err_getNewKey
   Set rs = CurrentDb.OpenRecordset("USysKey", dbOpenDynaset)
   rs.FindFirst "keyName='" & cKeyName & "'"
   If rs.NoMatch Then
      rs.AddNew
      rs!keyname = cKeyName
      rs!keyvalue = nInitValue + 1
      nRes = nInitValue
   Else
      rs.Edit
      nRes = rs!keyvalue
      rs!keyvalue = rs!keyvalue + 1
      rs.Update
   End If
exit_getNewKey:
   getNewKey = nRes
   rs.Close
   Set rs = Nothing
   Exit Function
err_getNewKey:
   Select Case Err
   Case 3260
      Resume 'shouldn't take long
   Case Else
		MsgBox Err.Number & ": " Err.Description
      Resume exit_getNewKey
   End Select
End Function

Usage

In the BeforeInsert event of the form, put this code: 
	me!ID = getNewKey("ID")
If the key was not present in the table, the function will create a record for it. Should you want to start at something else than 1, supply an initial value e.g. 100: 
	me!ID = getNewKey("ID", 100)