1

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!!!

Daniel MV
  • 11
  • 5
  • UPDATE: I continued to play around with the "Before Change" event and used it to copy all inserted values in the record to different empty columns in the same row. All of them were copied, EXCEPT [ID] COLUMN, which apparently just throws a NULL value. Could it be that AUTONUMBERS or PRIMARY KEYS are NOT REALLY "INSERTED" on the table until all events have been handled??? – Daniel MV Mar 05 '15 at 19:06
  • 1
    Yes, unfortunately when a Before Change data macro runs as a row is being inserted it "sees" the value of the AutoNumber field as `Null`. This despite the fact that Access has already retrieved the next AutoNumber value and may even be displaying it (e.g., in Datasheet view). Therefore an event-driven data macro may not be suitable for your intended purpose. – Gord Thompson Mar 05 '15 at 22:06
  • Thanks for the comment! The fact that it is shown in the Datasheet view makes it so anti-intuitive. Please review my answer below, it may not be the ideal solution but I think it's an effective workaround! – Daniel MV Mar 05 '15 at 22:45
  • You have to use the after insert. Before change on insert can be canceled and thus autonumber is NEVER used. Null autonumber is the case for most database systems/triggers. You cannot (and should not) use autonubmer until such time the record is committed. Any code requiring auotnumber REALLY NEEDS be moved to the after insert event. In fact such code really cannot by any “reasoned” sense belong in an event that can be canceled. You have ZERO need for a PK value in before change since you don't know if the record EVER is going to be committed at that point in time. – Albert D. Kallal Mar 06 '15 at 21:01

2 Answers2

1

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.

thor
  • 21,418
  • 31
  • 87
  • 173
Alex
  • 11
  • 1
0

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!

Community
  • 1
  • 1
Daniel MV
  • 11
  • 5
  • The solution is not to cook up a means to grab the auto number, but in fact move any code to the after insert event. You should not and can not really use the PK id in a before update event UNLESS you breaking basic database design and given that PK value some meaning. The ONLY possible reason to reference the PK value in such events is when a child table is involved, and thus again such code goes in after insert, since your code in the before change can CANCEL the insert and you never really know if the record going to be added. For what possible reason do you need PK id in before change? – Albert D. Kallal Mar 06 '15 at 21:07
  • I should add that rightfully so, the after insert will show/have the current record in context as READ only. You can get around this limitation by doing a lookup record to create a new reordset in context which you CAN edit. The main issue here is you are giving meaning to and using the PK ID column for something else in the same record – this breaks most database design rules since you are thus using and giving meaning to the PK id column. What you need to do is build a custom Hasfunction that returns it own numbering system – not one based on PK. – Albert D. Kallal Mar 06 '15 at 21:15
  • Wow! Big comment and many questions =), so i'll answer one by one: – Daniel MV Mar 06 '15 at 21:31
  • 1. I tried to use After Insert before switching to Before Change, but it yielded no results. Now it's clear why that happened because, as you say, the row was in read only mode. – Daniel MV Mar 06 '15 at 21:44
  • 2. I'm not adding any meaning to the primary key. In this case, hashing it gives a redundant value that adds no information. This might seem silly, but i will use this Hashed Value for better human readibility of the primary key and to reduce the chances of users mistyping the ID (Its easier to incorrectly type 120 when the value should have been 102 and still get an existing record instead of typing F34AAD51 when the value should have been F34ADA51 which should almost certainly just raise an error because its very unlikely that the typed value existed). – Daniel MV Mar 06 '15 at 21:45
  • 3. Number 2 explains why i need to do this: the primary key is the code that people will use, but in a "masked" version of it. – Daniel MV Mar 06 '15 at 21:45
  • 4. In my case, having a value in the Hashed ID column is mandatory for insert and i would like to keep it that way, so your solution wont work in my case but it's still a good workaround! – Daniel MV Mar 06 '15 at 21:45
  • Your points are all fair. As noted, you can use the after update (but you cannot use the record "in context" because it is read only, but if you use lookup record (with an alias) then you can edit the record again (you just check if your custom hashID is blank, and if yes, then update the value. This thus would allow the trigger to work with any ODBC connection and not require VBA. – Albert D. Kallal Mar 09 '15 at 04:42
  • Thank you very much for all your comments! I will keep them in mind when the official DB is built!! (I'm just doing a DEMO for now!). – Daniel MV Mar 09 '15 at 13:31