Autonumber each new record per ID

[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.

Even better if the numbers can just be 1, 2, 3, 4 etc for each account

Any help appreciated.

Thanks

[TheSmileyCoder]

You can use the beforeUpdate or BeforeInsert event of the form to control this. Assuming the personID is in field txPersonID of table tbl_Person, and the number is stored in lngNumber of table tblRecord, where txPersonID and lngNumber is a combined primary key (key consisting of 2 fields):

Private Sub BeforeUpdate(Cancel as Integer)
  If Me.NewRecord then
    Me.tb_LngNumber=nz(Dmax("lngNumber","tblRecord","txPersonID='" & Me.tb_PersonID & "'"),0)+1
  end if
End Sub

The basic principle is to find the maximum value used on a per-person basis, and simply add one. The NZ function is only there in case no records has been added yet, in which case the Dmax returns null. The Nz then safely converts that Null to a 0.

[Sharkiness]

Can you tell me what the tb_ relates to

[TheSmileyCoder]

tb_PersonID is the forms Textbox bound to the field tx_PersonID.

[Sharkiness]

Thank you.

I have another query which I have placed in a new thread.

[NeoPa]

I would only add a suggestion that such calculated index values should be created as late as possible before the record is actually created. Probably the forms BeforeInsert event would be most apropriate.

Sharkiness:
I have another query which I have placed in a new thread.

Good for you. That's the correct approach :-)

[Sharkiness]

I placed it in the id text box Before Update event as it didnt seem to work in the forms before insert event. do you think this will cause me problems?

[NeoPa]

Only if/when multiple operators are working on the same PersonID at the same time. There is a potential there for two records to use the same new ID with that logic. It's quite possible that will never happen for you - certainly if it's only ever used as a single instance.