Auto increment form field I am working with a database, with  some forms. What I am trying to do is an auto increment field in my form. For example ID -- every time when I type some new record, this field ID should increment automatically like this: ID0001, ID0002 etc. I need a formula to put in before update to do that. Thanks.
Where My_ID = ID0001, ID0002, ID0003…
IIF(Val(Right(DMax("[My_ID]","[MyTableName]"),4))+1&lt;10,"ID000" &amp; Val(Right(DMax("[My_ID]","[MyTableName]"),4))+1,
IIF(Val(Right(DMax("[My_ID]","[MyTableName]"),4))+1&lt;100,"ID00" &amp; Val(Right(DMax("[My_ID]","[MyTableName]"),4))+1,
IIF(Val(Right(DMax("[My_ID]","[MyTableName]"),4))+1&lt;1000,"ID0" &amp; Val(Right(DMax("[My_ID]","[MyTableName]"),4))+1,
"ID" &amp; Val(Right(DMax("[My_ID]","[MyTableName]"),4))+1
)))


With this, you will be good for 9,999 unique ID numbers before going from a 6 character ID to 7 characters.

The formula display has extra carrage returns for visual effect which can be removed to provide a continuous string.

If you wish to use VBA, you might consider the following:

    Dim IDNum as Integer
    Dim NewID as String

    IDNum = Val(Right(DMax("[My_ID]","[MyTableName]"),4))+1 

    IF IDNum&lt; 10 then
        NewID  = "ID000" &amp; IDNum
    ElseIF IDNum&lt; 100 then
        NewID  = "ID00" &amp; IDNum
    ElseIF IDNum&lt; 1000 then
        NewID  = "ID0" &amp; IDNum
    Else
        NewID  = "ID" &amp; IDNum
    EndIf

    Me.My_ID = NewID


I hope that this is helpful.