Sequentially Increment a Text Field

If you use a form for data entry, here's an easy way to sequentially increment text fields such as invoice numbers (INV-12345) and purchase order numbers (PO-12345) without relying on an autoincrement field.

Insert the following code in an appropriate event (Onload, OnCurrent, OnClick, etc.) and change the field and table names to match your own.

' Use this line if creating a new record by clicking a button
[color red]DoCmd.GoToRecord , , acNewRec[/color red]

' Me.DataEntry = True (If you want to enter data as soon as your form opens, use this instead of the above)

[color red]If Me.newRecord Then
Dim varResult As Variant
varResult = DMax("field-name", "table-name")
If IsNull(varResult) Then[/color red] 'just in case the table is empty
[color red] Me.[field-name] = "PO-4000"[/color red] 'Use your own format here
[color red] Else
Me.[field-name] = Left(varResult, 3) & Val(Right(varResult, 4)) + 1
End If
Else
MsgBox "No new record created!"
End If[/color red]

Note: This bit of code Left(varResult, 3) & Val(Right(varResult, 4)) + 1 will vary depending on your needs.

I can't take credit for this - I found it somewhere on the 'Net a few months ago and have had great success with it.