1

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

Erik A
  • 31,639
  • 12
  • 42
  • 67
tutu
  • 673
  • 2
  • 13
  • 31
  • 2
    *auto number in Access (sometimes produces duplicates)* - wait, what? That would be new for me. – Andre Aug 16 '18 at 10:21
  • @Andre If you google there are multiple resources confirming this. Example: http://allenbrowne.com/ser-40.html – tutu Aug 16 '18 at 10:23
  • 2
    These are all edge cases, where it would IMHO be better to avoid these cases (by table and form design) than try to create your own Autonumber system, which will be *much* more likely to fail or be erroneous. – Andre Aug 16 '18 at 10:45
  • @andre I use a Access (backend) Excel (frontend). Using an auto number field as PK. First I push the rows to a temp table in Access from Excel. Then; insert into main table from temp table where ID IS NULL. A few times, seemingly at random I get the duplicate key error. I'm forced to use my own method for auto numbering. If you see an error in the below answer please feel free to point it out. – tutu Aug 16 '18 at 12:31

2 Answers2

2

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>
Erik A
  • 31,639
  • 12
  • 42
  • 67
  • Thanks I like the method of not using a query access object. However, in both examples I'm struggling with dealing with a NULL value. Nz() works from within Access, but stops working as soon as I use Excel VBA – tutu Aug 16 '18 at 10:12
  • `Nz` is an Access application dependent function. You can try using standard VBA functions: `IIf(IsNull(MyVar), Something, MyVar)`. Of course, use these in the `SetLocalVar`, not the query. The possibilities are poorly documented, so this might not work. – Erik A Aug 16 '18 at 10:15
  • I'm using the Iif() function in the access query which seems to be working completely. Thanks a lot. – tutu Aug 16 '18 at 10:20
0

Replaces Access Autonumber LongInt Data Type Using simple Data Macro and one simple VBA function (see images x2)

Data Macro

VBA Code

Gary Chernipeski
  • 1
  • 3