Referencing Just Added Record When Using Call to VBA in AfterInsert DataMaco

Question

Access 2016, table with a AfterInsert datamacro.

In a comment to a previous question that I can no longer find, Albert D. Kallal (user:10527) noted that if one uses the Set LocalVar approach to call a user-defined function in vba, that vba "session" does not see the newly inserted record.

Even if one passes in the identity of the newly added record, any queries referencing the table where the record was just added won't see the newly inserted record.

Has anyone developed a work around so that for example in building a closure-table, the newly added record is visible or can be used?

This is very similar to the problem Alberta Kallal has nicely shared solutions for of copying over an existing record using the "after update" approach to get around being unable to create a record within a for each loop. However, in this case I would like to use the result of a query as the basis for copying / inserting, not just copying over an existing row or 'hard coding' the business logic for the modifications into the datamacro (hence would be easy to do if vba approach could see the just added row).

Specific use case is trying to create a way of inserting "induced" records in an accounting application. One earns some money that has no tax deducted, so it is nice to have an automatic journal entry created showing a future liability to pay the tax. In other words, the presence of a record in a table should automatically generate additional records - and the business logic of those additional records is defined through queries.

Even more specifically, given a "transactions" table with fields RegisterID, AccountID, Amount, TrDate, and a "Induced Transaction Queries" table that specifies the queries to provide the induced transactions with fields AutoNumberId, QueryThatDefinesAdditionalTransactions (and multiple rows, e.g. "Query That adds self", "Query That Adds Tax Liability", "Query That Computes Sales Tax Portion"),

how can a datamacro create rows in a "Transactions For Analysis" Table based on iterating over the "Induced Transaction Queries" table and inserting the results of the query in each row applied to the newly added row in the original "transactions table"

Thx

Answer 1

Well, while calling VBA from the macro after insert event can’t get the row just inserted, that VBA routine can read + update as many OTHER rows as you please. And if that new row or data you write out in VBA has to include the data from the row just inserted?

Well pass the columns to the VBA routine. So it now can go add up all the other rows, and THEN include the values just passed from the data macro.

So the data macro (after insert) can look like this:

And the VBA code thus can look like this:

Public Function AfterU(vid As Variant, Amount As Variant)

  Debug.Print vid, Amount

  Dim rst     As DAO.Recordset
  ' open table, sum data
  ' add Amount passed.

  ' write out data to some table.
  ' caution - don't add reocrds to this table that has the trigger
  ' else you wind up in a endless loop


End Function

The above of course has to be placed in a standard code module (not forms), and has to be defined as public as per above.

So while you find that the record JUST added is not yet committed to the table (it is about to be), you can still run + call some VBA code. You just not get your hands on the record just (about to be) inserted.

However, since you can pass the needed columns such as amount etc. to include in the final total? Then you are free to write as much VBA update and insert code as you wish. So you can pull some records, build a total, and now update some existing record, or even add a new record. Just include the new amount passed also.

As noted, you don’t want to “tangle” up writing to a table that has such a trigger with VBA inserts since you can easy get stuck a endless loop.

However, for adding up, or updating, or inserting to another table, you should be fine to do what you please in the VBA code.