Next number in a series based on another field value

[bluearrow]

Hi,

This all happens in the same Table and TO.

Field1 contains a number provided by the user (1 to 15).

- All records in the TO have a number in this field.

- Several records might share the same number (there might be several records with 1 in field1, and several records with 2 in field1, etc.)

Field2 must be automatically calculated based on field1. The calculation should be:

Field1 & "-" & Record Position according to the number of records of same type present when creating the record (a typical serial number).

For instance, 1-1, 1-2, 1-3, 1-4, 2-1, 2-2, 2-3.

When the user creates a record, he/she must select a number in Field1. Then the program should assign the value in Field2. If the user select 5 in Field1, and there are already 3 records present in the TO with a value of 5 in Field1, then Field2 should be automatically assigned the value of 5-4.

The user might choose any value in Field1 at any time. For instance, might add a new record and assign to Field1 the value 2 for that record, even in there are many records with values of 1, 2, 3, 4, etc. in Field1.

Ah! One last point. The user might change the value of Field1 at any given time. Field2 must be recalculated accordingly. For instance, if there are 4 records assigned with a value of 2 in Field1:

2-1, 2-2, 2-3, 2-4

and the user changes the value of Field2 for record with value 2-2 to 2-1, all other records must renumber accordingly, like:

2-1 is the new value of 2-2

2-1 changes to 2-2

2-3 remains unaffected

2-4 remains unaffected

Could you help me with (ideally) a custom function to handle this problem?

Thank you