How to create a field similar to a unique identifier field

[pbaldy]

Why lookup a value you just set? Inefficient, even though the table may only contain 1 row. How about:

  Dim PreviousBatch As Integer
  Dim Newbatch As Integer

  PreviousBatch = Nz(DLookup("nextbatchno", "Tbl_BatchNo", "id=1"), 0)

  If PreviousBatch = 9999 Then
    Newbatch = 1000
  Else
    Newbatch = PreviousBatch + 1
  End If

  CurrentDb.Execute "UPDATE Tbl_BatchNo SET nextbatchno = " & Newbatch & " WHERE id=1", dbFailOnError
  Me.Txt_BatchNo = Newbatch