Create an Autonumber for each ID in table

[Sharkiness]

Morning All,

I have having a little difficulty here. I have a user form where users will add a number of accounts for one person. Each person has a unique ID. What I want is when each account for this person is added to the form for some type of autonumber but for the autonumber to restart for each new Unique ID.

I.E

Mr J Bloggs Unique ID = AA124

Account 1 SubRef = AA124 &"1"
Account 2 SubRef = AA124 &"2"
Account 3 SubRef = AA124 &"3"
Account 4 SubRef = AA124 &"4"

Mrs J Bloggs Unique ID = AA125

Account 1 Subref = AA125 &"1"
Account 2 Subref = AA125 &"2"
Account 3 Subref = AA125 &"3"

When these accounts are added to the form the SubRef field will autopopulate with this.

Any help appreciated.

Thanks

 

[Galaxiom]

The AA124 part should not be included in the new field since it is already held in their unique ID. All you need is to store the sequence section. Concatenate them as required for display only. Mixing the numbers also greatly complicated the increment process.

The subref sequence number is easily generated by:

DMax("subref", "tablename", "ID=" & Me.IDcontrolname) + 1

This finds the largest number in the subref field where the ID matches the ID in a control on the current form.