Sequential Number Not Auto Number

[Fred Webster]

I am trying to generate a new number in a form.
I would like to have this number increase by one everytime an entry is made.
I was hoping I could use default value but I cannot seem to get it to work.
Does anyone have a simple suggestion,
Thanks in advance,
Fred

 

[Linq Adams via AccessMonster.com]

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.


For a text IDNumber:

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("[IDNum]", "YourTableName") + 1
End If
End If
End Sub

 

[Arvin Meyer [MVP]]

You can use the DefaultValue property. Use this as an example:

=DMax("[ID]","tblMyTable")+1

 

[Fred Webster]

Thanks, I will give it a try.

 

[John W. Vinson]

I am trying to generate a new number in a form.
I would like to have this number increase by one everytime an entry is made.
I was hoping I could use default value but I cannot seem to get it to work.
Does anyone have a simple suggestion,
Thanks in advance,
Fred

One simple way to do this is to use the Form's BeforeInsert event:

Private Sub Form_BeforeInsert()
Me!fieldname = NZ(DMax("[fieldname]", "[tablename]")) + 1
End Sub

 

[Fred Webster]

Tanks to all who replied. It is appreciated!!
Regards,
Fred

I am trying to generate a new number in a form.
I would like to have this number increase by one everytime an entry is made.
I was hoping I could use default value but I cannot seem to get it to work.
Does anyone have a simple suggestion,
Thanks in advance,
Fred

One simple way to do this is to use the Form's BeforeInsert event:

Private Sub Form_BeforeInsert()
Me!fieldname = NZ(DMax("[fieldname]", "[tablename]")) + 1
End Sub

 

[Fred Webster]

Hi, I tried that but get an error when running this. Any idea what went wrong?
Fred

I am trying to generate a new number in a form.
I would like to have this number increase by one everytime an entry is made.
I was hoping I could use default value but I cannot seem to get it to work.
Does anyone have a simple suggestion,
Thanks in advance,
Fred

One simple way to do this is to use the Form's BeforeInsert event:

Private Sub Form_BeforeInsert()
Me!fieldname = NZ(DMax("[fieldname]", "[tablename]")) + 1
End Sub

 

[John W. Vinson]

Hi, I tried that but get an error when running this. Any idea what went wrong?
Fred

Not unless you tell me what code you put in, what are your actual field and
tablenames, and the error message, no.

 

[BruceM]

I submit to you that adding one to the value is indeed performing a math
operation upon it, so a number data type is appropriate.