Manual Auto Number

[Eric]

I have 3 fields
1. Staff_Id (Text, e.g. 'A123456(7)'
2. Start_Year(Integer, e.g. '2000'
3. End_Year(Integer, e.g. '2001'

I wish to create a record ID with combining the above 3 fields with format as below
A123456(7)-2000-2001/

How to make new recorde of same staff with same "Start_Year" and same "End_Year" to add 1 automatically,
i.e. A123456(7)-2000-2001/

If Same staff with different Start_Year or End_Year, the new record ID will count from 1 again as below
i.e. A123456(7)-2001-2002/

Thank you for all your help
eric

 

[Marshall Barton]

I have 3 fields:
1. Staff_Id (Text, e.g. 'A123456(7)')
2. Start_Year(Integer, e.g. '2000')
3. End_Year(Integer, e.g. '2001')

I wish to create a record ID with combining the above 3 fields with format as below:
A123456(7)-2000-2001/1

How to make new recorde of same staff with same "Start_Year" and same "End_Year" to add 1 automatically,
i.e. A123456(7)-2000-2001/2

If Same staff with different Start_Year or End_Year, the new record ID will count from 1 again as below:
i.e. A123456(7)-2001-2002/1

Add a fourth field for the sequential number.

Then, if your application is for a single user, you can use
code in the form (that's used to add new records) to
calculate the sequential number. The code in the form's
BeforeUpdate event would be something along these lines:

If Me.Newrecord Then
Me.SeqNum = Nz(DMax("SeqNum", "table", _
"Staff_Id = " & Me.Staff_Id _
& " And Start_Year = " & Me.Start_Year _
& " And End_Year = " & Me.End_Year), 0) +1
End If