Am I correct that you are creating a new record and it needs a new and unique serial number value?
How will you use this serial number and why do you need it numbered in this fashion?
I do not recommend that you use such a serial number value as the primary key for linking your records in this table to other related tables. I do, however, understand that sometimes you need such a numbering system for other purposes--such as supporting a system already in use by your organization. That's OK, just keep such a value in a field in your main table and don't use it in relationships. That will allow you to put the value into any layouts/reports that you need and you can use it in finds and sorts, but not have it complicating the underlying "backbone" of relationships used in your database.
Another complication is that the methods available for generating such serial values are vulnerable to producing duplicate values if two users attempt to generate a serial number from the same series at the same time. If there is even the slightest chance that such might occur, you have to put in additional safe guards to catch and correct the duplicates.
The most commonly and most generally applicable technique is to set up a self join relationship where you link this table to another occurrence of the same table by the FORM field. Then you can set up the ID field to auto-enter: Max ( YourTable 2::ID ) + 1. To guard against duplicates, specify a unique values validation field option on this field so that an error appears if you get a duplicate.
Yes, you are totally correct. I need to design it in this fashion to support the existing system.
Could you please explain in detail for me and give me an example on how to do this? I will be so appreciate with ur help.
My time is limited.
Do you know how to define a self join that matches records by the FORM field?
Yes, i know that. I will try it first. Thanks.
Just trying not to spend time telling you parts that you may know already. Feel free to ask how if you can't get the self join set up. That's step one to implementing this process.