Access data macro get value from query (auto numbering)

Instead of the using the auto number in Access (sometimes produces duplicates) I've decided to generate my own numbers.

I am using the data macro Before Change but I'm not sure on how to run the query SELECT MAX(ID)+1 FROM MyTable and insert it into the ID field on each Insert.

I've messed around with the SetField, SetLocalVar, LookUpRecord actions but no luck so far.

EDIT: I've tried using DMAX in the expression as per example: https://www.599cd.com/tips/access/incrementing-your-own-counter/. This works when I add a row manually. However, I add rows from Excel VBA at which point this method stops working, generating the error, the function is not valid for expressions used in data macros

You can only use very limited SQL statements in data macros. You can use queries, though.

Create a query (called QueryA), and enter SELECT MAX(ID)+1 As Expr1 FROM MyTable as the SQL

Then, you can use a data macro with the following structure:

If [IsInsert] Then
    Look Up A Record In      QueryA
          SetLocalVar 
                  Name = NewID
                  Expression = [QueryA].[Expr1]
    SetField
          Name = ID
          Value = NewID

The AXL is the following:

<?xml version="1.0" encoding="UTF-8"?>
<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application">
   <DataMacro Event="BeforeChange">
      <Statements>
         <ConditionalBlock>
            <If>
               <Condition>[IsInsert]</Condition>
               <Statements>
                  <LookUpRecord>
                     <Data>
                        <Reference>QueryA</Reference>
                     </Data>
                     <Statements>
                        <Action Name="SetLocalVar">
                           <Argument Name="Name">NewID</Argument>
                           <Argument Name="Value">[QueryA].[Expr1]</Argument>
                        </Action>
                     </Statements>
                  </LookUpRecord>
                  <Action Name="SetField">
                     <Argument Name="Field">Field1</Argument>
                     <Argument Name="Value">[NewID]</Argument>
                  </Action>
               </Statements>
            </If>
         </ConditionalBlock>
      </Statements>
   </DataMacro>
</DataMacros>

You shouldn't use VBA functions or domain aggregates such as DMax in data macros, nor in the queries data macros are dependent upon. If you do, it can only be triggered from a running Access application, because these are only valid from within Access.

---

Alternatively, you can rewrite your SQL statement to be valid for data macros. This means: no aggregates, no calculations! But you can use ordering to get the maximum value:

If [IsInsert] Then
    Look Up A Record In      SELECT [MyTable].[ID] As [Expr1] FROM [MyTable] ORDER BY [MyTable].[ID] DESC
                      Alias A
          SetLocalVar 
                  Name = NewID
                  Expression = [A].[Expr1] + 1
    SetField
          Name = ID
          Value = NewID

The AXL is the following (which makes it easier to understand the limited SQL):

<?xml version="1.0" encoding="UTF-8"?>
<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application">
   <DataMacro Event="BeforeChange">
      <Statements>
         <ConditionalBlock>
            <If>
               <Condition>[IsInsert]</Condition>
               <Statements>
                  <LookUpRecord>
                     <Data Alias="A">
                        <Query>
                           <References>
                              <Reference Source="MyTable" />
                           </References>
                           <Results>
                              <Property Source="MyTable" Name="ID" Alias="Expr1" />
                           </Results>
                           <Ordering>
                              <Order Direction="Descending" Source="MyTable" Name="ID" />
                           </Ordering>
                        </Query>
                     </Data>
                     <Statements>
                        <Action Name="SetLocalVar">
                           <Argument Name="Name">NewID</Argument>
                           <Argument Name="Value">[A].[Expr1]+1</Argument>
                        </Action>
                        <Action Name="SetField">
                           <Argument Name="Field">Field1</Argument>
                           <Argument Name="Value">[NewID]</Argument>
                        </Action>
                     </Statements>
                  </LookUpRecord>
               </Statements>
            </If>
         </ConditionalBlock>
      </Statements>
   </DataMacro>
</DataMacros>