Increasing value of last record by 1 automatically

[shevo]

How can I increase the value of last record by 1 automatically whenever I enter a new record?
I am not using autonumber for my primary key to uniquely identify each customer. I have used Text as the datatype. The format is e.g. CN001 for the 1st customer.
Please help.....Thanks!

 

[llkhoutx]

Query the field, with the numeric portion only, for the maximum value and increment that value, then append the prefix.

 

[shevo]

Query the field, with the numeric portion only, for the maximum value and increment that value, then append the prefix.

Where do I query the field? I found this DLast function. Does it works? Gave me error when I only wanna try to get the value of the last record.

 

[missinglinq]

For the purposes of this Auto-Incrementing Number demo

Table Name: AITable
Table Field holding PK Number: MyPK
Form Textbox holding PK: PKField

You'll need to substitute the actual names of your objects for these

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
 If RecordsetClone.RecordCount = 0 Then
  Me.PKField = "CN001"
 Else
  Me.PKField = "CN" & Format(DMax("val(Right([MyPK],3))", "AITable") + 1, "000")
  End If
End If
End Sub

 

[shevo]

For the purposes of this Auto-Incrementing Number demo

Table Name: AITable
Table Field holding PK Number: MyPK
Form Textbox holding PK: PKField

You'll need to substitute the actual names of your objects for these

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
 If RecordsetClone.RecordCount = 0 Then
  Me.PKField = "CN001"
 Else
  Me.PKField = "CN" & Format(DMax("val(Right([MyPK],3))", "AITable") + 1, "000")
  End If
End If
End Sub

Works perfectly...A thousand thanks to u!

 

[missinglinq]

Glad it works for you!