Populate a field in formbased on last record

[Pirie]

Hi,

I'm trying to add an ID for a new record and want the field to automatically display the correct ID number in sequence which is one more than the last record in the table.

I'm struggling with this one

 

[missinglinq]

Here's a typical Auto-incrementing Number hack. The first code here would be for an IDNumber that is defined in the table as Text datatype. "Number" fields that aren't used for math really should be defined as Text.

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.NewRecord Then
   If RecordsetClone.RecordCount = 0 Then
   Me.IDNumber = "1"
  Else
    Me.IDNumber = DMax("val([IDNumber])", "YourTableName") + 1
  End If
End If
End Sub

Here's the same code for an IDNumber defined as Numerical:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
  If RecordsetClone.RecordCount = 0 Then
    Me.IDNumber = 1
  Else
    Me.IDNumber = DMax("[IDNumber]", "YourTableName") + 1
  End If
End If
End Sub

The above can be used safely for multi-user environments because it doesn't assign the ID number until the very last minute before the record is saved. This is necessary to decrease the chance of two users getting the sanme ID number. The only drawback is that the user cannot see the ID number until the record is saved.

In a single user environment, the code can be moved to the Form_Current event and the ID number will appear as soon as a new record is started.