MS Access - How to custom format auto-incrementing fields?

Question

I have a pretty basic Table here called Game.

The data looks like this:

[Id]  [Description]

The Id columns is set to auto-increment, but it is not formatting the way I would like.

The sample data looks like this:

[Id]  [Description]
GM-001 Super Mario Bros.
GM-002 The Legend of Zelda
GM-003 Super Metroid

The desired result is for the Id column to look like this:

GM-1 (Super Mario Bros.)
GM-1-1 (The Legend of Zelda)
GM-1-1-1 (Super Metroid)

My current formatter is "GM-000" but even attempts at "GM-0" do not work nor does "GM-0-0". I know this is not a traditional auto-increment, so is there a way to custom format the column to the desired result?

Thanks and sorry for the elementary question.

Answer 1

Use Table event Before Change to generate ID

---

<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<DataMacros
    xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application">
    <DataMacro Event="BeforeChange">
        <Statements>
            <ConditionalBlock>
                <If>
                    <Condition>[IsInsert]</Condition>
                    <Statements>
                        <Action Name="SetLocalVar">
                            <Argument Name="Name">prefix</Argument>
                            <Argument Name="Value">"GM -"</Argument>
                        </Action>
                        <LookUpRecord>
                            <Data Alias="gm">
                                <Query>
                                    <References>
                                        <Reference Source="Game" Alias="gm"/>
                                    </References>
                                    <Results>
                                        <Property Source="gm" Name="alternate_id"/>
                                    </Results>
                                    <Ordering>
                                        <Order Direction="Descending" Source="gm" Name="alternate_id"/>
                                    </Ordering>
                                </Query>
                                <WhereCondition>[gm].[alternate_id] Like [prefix] &amp; "*"</WhereCondition>
                            </Data>
                            <Statements>
                                <Action Name="SetLocalVar">
                                    <Argument Name="Name">prefix</Argument>
                                    <Argument Name="Value">[gm].[alternate_id] &amp; "-"</Argument>
                                </Action>
                            </Statements>
                        </LookUpRecord>
                        <Action Name="SetField">
                            <Argument Name="Field">alternate_id</Argument>
                            <Argument Name="Value">[prefix] &amp; 1</Argument>
                        </Action>
                    </Statements>
                </If>
            </ConditionalBlock>
        </Statements>
    </DataMacro>
</DataMacros>

---

Output

---

Table Structure