Automatically generate Date + 4-digit sequence number for ID in Access 2010+

Question

I need to automatically generate a 12 character value for my Business Key. Without any user interaction.

8 character -> Today Date        (yyyymmdd or ddmmyyyy).
+
4 character -> Sequential Number (0001,0002,0003).

The Sequential Number must reset on each new day.

Is it possible to do this in Microsoft Access 2010+ without any coding involved?

Answer 1

Since you are using Access 2010+ the best way to accomplish your goal would be to use a Before Change data macro like this

To create the Before Change macro, click the "Before Change" button on the "Table" tab of the ribbon when the table is open in Datasheet View:

For more details on Data Macros see

Create a data macro