CODEWITHSUNDEEP
vbams-accesscalculated-fieldms-access-data-macro
Daniel MV
Daniel MV

Reputation: 11

Event Driven Data Macro to Calculate Field in New Record - MS Access

In MS Access 2013 I have a table called [Serials] which consists of only 3 columns: [ID], [Hashed ID] and [Product Description].

[ID] is an Incremental Integer Autonumber, and the Primary Key. [Hashed ID] holds the result of a Hash Function applied to [ID]. Finally, [Product Description] holds user input (the Hash Function is stored in a VBA Module as a Public Function).

What I would like to do is to have the field [Hashed ID] calculated automatically after a row is inserted on the [Serials] table.

I guess that the trick lies in correctly using the Event Driven Data Macros for this table, but I've only managed to make this work using the "Before Change" event on rows that had already been inserted (Therefore, with a saved value on their [ID] field?) . I'm lost!!!

Upvotes: 1

Views: 1528

Answers (2)

Alex
Alex

Reputation: 11

This is tested on Access 2016. I found out the situation when you get NULL value while reading a field inside BeforeChange data macro. This happens if that field is a PRIMARY KEY. It is not an AutoNumber. So the problem appears when you have this combination:

  1. BeforeChange event
  2. INSERT action (only)
  3. PRIMARY KEY field

Simple solution is to change PRIMARY KEY to UNIQUE.

Upvotes: 1

Daniel MV
Daniel MV

Reputation: 11

Well, I managed to find an acceptable solution!

First of all it appears that in MS Access, when one uses Event Driven Data Macros for tables, it is NOT POSSIBLE to DIRECTLY reference the value of an Autonumber field until after it has been fully inserted and saved on the DB (this is not the case of the other field types). When passed as a parameter, it will always return a NULL VALUE.

This being said, we can still reference the CURRENT SEED NUMBER for any Autonumber field with the help of a custom VBA Function (Thanks to HansUp for This Post ):

Public Function NEXTAUTONUM(ByVal pTable As String, ByVal pColumn as String) As Long

Dim CAT As Object
Set CAT = CreateObject("ADOX.Catalog")
Set CAT.ActiveConnection = CurrentProject.Connection
NEXTAUTONUM = CAT.Tables(pTable).Columns(pColumn).Properties("Seed")
Set CAT = Nothing

End Function

NEXTAUTONUM will return the Autonumber Value that Access will set next in the [ID] field, should a new row be inserted, which is what the HASH FUNCTION needs as the input parameter. Now we can create our Data Macro in the Before Change Event, which should look like this:

If [IsInsert] = True Then
     Set Field
       Name  Hashed ID
       Value = HASHFUNCTION ( NEXTAUTONUM ( "Serials","ID" ) - 1 )
End If

Additionally, we substract 1 from the value returned by the NEXTAUTONUM function because we need the [ID] Autonumber value for the row we're currently working on, and not the one that will be created next.

Hope this will help someone!

Upvotes: 0

Related Questions