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 DoCmd.GoToRecord , , acNewRec
' Me.DataEntry = True (If you want to enter data as soon as your form opens, use this instead of the above)
If Me.newRecord Then Dim varResult As Variant varResult = DMax("field-name", "table-name") If IsNull(varResult) Then 'just in case the table is empty Me.[field-name] = "PO-4000" 'Use your own format here Else Me.[field-name] = Left(varResult, 3) & Val(Right(varResult, 4)) + 1 End If Else MsgBox "No new record created!" End If
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.