updatiing number field

  • Thread starter Thread starter aglabrat
  • Start date Start date
A

aglabrat

I don't want to use Autonumber. I wish to use a new record button and want it
to put the next available number in the number field on the new record.
 
Use the BeforeUpdate event of the *form* to look up the highest number used
so far, add 1, and assign it to the control.

This example assumes the Number field is named ID, in Table1:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.ID) Then
Me.ID = Nz(DMax("ID", "table1"), 0) + 1
End If
End Sub

The form's BeforeUpdate is the last possible moment before the record is
saved. Using this event reduces the chance that 2 users will be given the
same number when adding records, but it could still happen.
 
Back
Top