I strongly recommend that you not use this field as a primary key in relationships in your database. Use a serial number field for that purpose instead.
If you do use this field, put in in one table only and use it only for searches, reports etc.
Define a self join relationship:
YourTable::Year = YourTableByMonthYear::Year AND
YourTable::Month = YourTableByMonthYear::Month
Define Month as an auto-entered calculation: Month ( get ( CurrentDate ) ) and Year as Year ( get ( CurrentDate ) )
Set 3DigSerial as a number field with this auto-entered calculation:
Max ( YourTableByMonthYear::3DigSerial ) + 1
Be sure to clear the "do not replace existing values" check box and also clear the "Do not evaluate if all referenced fields are empty" check box.
Your calculation to produce this value:
Right ( Year ; 2 ) & Right ( "0" & Month ; 2 ) and Right ( "00" & 3DigSerial ; 3 )
This should be an auto-entered calculation on a text field with a UniqueValues validation rule to catch the possible occurrence of getting the same value for two different records should two users try to create a record in this table at the same time.
Thank you so much. I got it.