Customized autonumber ID.

[AccessBeetle]

I have to create a RecordID which should be unique and autoincremented . The specifications are something like,

1) The number must be 9 digits long
2)First two digits should represent current YY, which should increment every year
3)Next 7 digits should be like 0000001 and should be autoincremented (like 7 digits long autonumber concatenated to the current year)

so the final number should look like this: 090000001, 090000002, and so on.

What should be my approach to this?
Thanks in advance as always!!

[ChipR]

You may not be able to store preceding zeros if you use a number field, so probably a string. Here's how I think you would do it.

Get the max RecordID with DMax.
Convert the first 2 characters to an integer.
See if they are equal to the current 2 digit year.
If so,
-Convert the rest of the RecordID to an Integer.
-Add 1 (deal with overflow?)
-Create a string, set it equal to the number.
-Append "0" to the front of the string until it is the proper length of 7
-Combine it with the year string.
Else,
-Create a string with the first two digits of the current year and "0000001"
End if.
Done.

[ADezii]

You're probably better off leaving it as Text if you can, but that being said, this simple Function should do the trick. I'll leave it up to you to figure in the possible year change:

Public Function fIncrementAuto() As String
Dim varMax As Variant
 
varMax = DMax("[YourNumber]", "tblTest")
 
fIncrementAuto = Format$(Left(varMax, 2), "00") & Format(Mid$(varMax, 3) + 1, "0000000")
End Function

[AccessBeetle]

All these coding should be placed under Form's On open_click event. Am I right?

[AccessBeetle]

sorry I did not see Adezil's answer.

[missinglinq]

Come on, ADezii, that's the easy part! Doing the year change thing is the fun part! This will do it all.

Note that AI_Number is defined as Text.

Private Sub Form_BeforeUpdate(Cancel As Integer)
 
If Me.NewRecord Then
 
 If RecordsetClone.RecordCount = 0 Then
  Me.AI_Number = Format(Date, "yy") & "0000001"
 End If
 
 If RecordsetClone.RecordCount <> 0 Then
  If Left(DMax("AI_Number", "YourActualTableName"), 2) = Format(Date, "yy") Then
    Me.AI_Number = Format(DMax("val([AI_Number])", "YourActualTableName") + 1, "000000000")
  Else
    Me.AI_Number = Format(Date, "yy") & "0000001"
  End If
 End If
 
End If
End Sub

By placing the code in the Form_BeforeUpda te, the number is assigned at the last possible moment before the record is saved, decreasing the chances, in a multi-user environment, of two records being assigned the same number. Using this method for a number of years, I have never run into this problem.

Linq ;0)>

[ADezii]

We have to leave something for the OP, don't we? (LOL)!

[AccessBeetle]

I am getting an error saying Cannot insert duplicate value in the table. ODBC call failed. When I tried to enter the record, the RecordsID populated with "090000001" second time also. Then I try to move to enter another record and there the error is.
Also, it would be great if you can explain me this part

If Left(DMax("RecordID", "RadioLog_tblRadioCallActivity"), 2) = Format(Date, "yy") Then
    Me.RecordID = Format(DMax("val([RecordID])", "RadioLog_tblRadioCallActivity") + 1, "000000000")
  Else
    Me.RecordID = Format(Date, "yy") & "0000001"
  End If
 

My actual tables are in SQL 2005 and I am linking them in Access 2003. Also, the application contains many forms from where user can enter new data.
Shall I write this code under each and every form? (I think I have to but not sure)

Exact Error:
ODBC Call Failed
Violation of Primary key constraint PK_RadioLog_tbl _RadioCallActiv ity cannont insert duplicate key in object 'dbo.RadioLog_t blRadioCallActi vity. (#2627) and (#3621)

[AccessBeetle]

OK OK.
It is solved. It was the data creating problem. I removed all the data from all the adjoining tables and table itself is also clear.
There is still something wrong. I have customized Login interface. I logged in with User1 and entered the data. It was ok. Then I logged in using User2 and started to add data and it was again starting from 090000001. There was the same error there again. What am I doing wrong?
Thanks for help

[missinglinq]

Not really sure! It works fine on this end, but, of course, I'm not using a SQL 2005 back end, and have no real experience with it. Hopefully someone else here doesan d will be able to help..

The bit of code you asked about is checking to see if the year portion of the last record entered is the same as the current year. IF it is, it continues incrementing the number with the same year prefix. If it isn't it assigns the new year prefix and starts the "count" over again at 0000001.

Linq ;0)>